Robinhood·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

Robinhood data scientist interview with a SQL and Python focus, all centered on a payments transaction schema. Nothing too wild but the window function follow-up was a step up from what I expected in terms of complexity.

Questions Asked (3)

Using a users table and a transactions table, write a SQL query that joins them and returns each user's name alongside their total amount sent and total amount received.

Product Analytics & MetricsData Modeling

Author's notes

Pretty standard aggregation question.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Clarify the schema and whether 'sent' and 'received' refer to the same transaction table with sender_id and receiver_id columns. Then write a query that aggregates sent and received amounts separately and joins them to the users table, using LEFT JOINs to include users with no transactions.

Pro tip: Mention that you would validate the query by checking for users with zero transactions and ensuring the totals match a manual spot-check, showing attention to data quality.

1. Clarify schema and assumptions

Ask about the columns in users and transactions tables, and confirm that transactions have sender_id and receiver_id fields. State any assumptions clearly.

2. Aggregate sent and received amounts

Write subqueries or CTEs to sum amounts grouped by sender_id and receiver_id separately, aliasing them as total_sent and total_received.

3. Join aggregates to users

Use LEFT JOINs from the users table to the aggregated subqueries on user_id to ensure all users are included, even those with no transactions.

4. Handle NULLs and format output

Use COALESCE to replace NULL totals with 0, and select user name along with the totals. Optionally order by user name or total amount.

5. Validate and discuss edge cases

Mention checking for users with no transactions, self-transactions, and ensuring the query performs well with indexes on sender_id and receiver_id.

Key Points to Mention

Use of LEFT JOIN to include users with no transactions
Separate aggregation for sent and received amounts
Handling NULLs with COALESCE or IFNULL
Potential need for indexes on sender_id and receiver_id for performance
Consideration of self-transactions (same sender and receiver)
Validation of results against sample data or business logic

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Extend your previous SQL query to include a running cumulative sum of amount_sent per sender, ordered by transaction_id, using a window function.

Data ModelingProduct Analytics & Metrics

Author's notes

This is where I stumbled a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by restating the previous query to ensure alignment, then add a window function that computes the cumulative sum of amount_sent partitioned by sender and ordered by transaction_id. Explain the purpose of each clause (PARTITION BY, ORDER BY, frame specification) and verify the result with a small example.

Pro tip: Mention that the default frame for a window with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes peers; if transaction_id is unique, ROWS is more efficient and predictable. Also note that for large datasets, partitioning by sender and ordering by transaction_id can leverage indexes or sort-merge joins to avoid full shuffles.

1. Restate the base query

Briefly summarize the previous SQL query to confirm the context and ensure the interviewer knows you're building on it.

2. Identify the window function

Choose SUM(amount_sent) as the window function and specify PARTITION BY sender to compute per-sender cumulative sums.

3. Define the ordering and frame

Add ORDER BY transaction_id and explicitly set the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a precise running total.

4. Write the full query

Combine the base query with the window function, ensuring correct syntax and aliasing the new column (e.g., running_total).

5. Validate and discuss performance

Walk through a small example to verify correctness, and mention potential performance considerations like indexing or partitioning strategies.

Key Points to Mention

PARTITION BY sender ensures the cumulative sum resets for each sender.
ORDER BY transaction_id defines the sequence for the running total.
Explicit frame specification (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avoids ambiguity with duplicate transaction_ids.
The default frame (RANGE) includes peers, which may lead to unexpected results if transaction_id is not unique.
Window functions are computed after WHERE, GROUP BY, and HAVING, so the base query's filters apply before the cumulative sum.
Performance can be improved with an index on (sender, transaction_id) to avoid sorting.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Given a transactions dataframe in Python, produce two Series showing the top 5 user IDs by number of transactions sent and the top 5 by number of transactions received, accounting for the fact that negative amounts reverse the direction of money flow.

Algorithms & Data StructuresProduct Analytics & Metrics

Author's notes

The negative amount wrinkle is the whole point of this question and I almost glossed over it.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, normalize the transaction direction by flipping the sign of the amount when it is negative, so that positive amounts always represent money sent and negative amounts represent money received. Then, group by sender and receiver separately, count transactions, and extract the top 5 for each using nlargest. Finally, return two Series with user IDs as index and counts as values.

Pro tip: Explicitly state your assumption about the sign convention (e.g., positive = sent, negative = received) and mention that you would verify it with the interviewer before coding. This shows attention to detail and avoids silent misinterpretation.

1. Clarify the data schema and sign convention

Confirm the column names (e.g., sender_id, receiver_id, amount) and the meaning of positive vs. negative amounts. State that you will treat positive amounts as sent and negative amounts as received, flipping the sign to normalize direction.

2. Normalize transaction direction

Create a new column or use a conditional to ensure that positive amounts always indicate money sent. For example, multiply the amount by -1 when it is negative, or swap sender and receiver when the amount is negative.

3. Compute counts for sent and received transactions

Group the normalized data by sender_id and count the number of transactions to get sent counts. Similarly, group by receiver_id and count to get received counts.

4. Extract top 5 for each direction

Use nlargest(5) on each count Series to get the top 5 user IDs by number of transactions sent and received. Ensure the result is a Series with user IDs as index and counts as values.

5. Validate and present results

Check that the total number of transactions is consistent and that no user appears in both top lists incorrectly. Present the two Series clearly, perhaps with a brief interpretation.

Key Points to Mention

Handling negative amounts by flipping the sign to correctly attribute direction of money flow.
Using groupby and count (or size) to aggregate transaction counts per user.
Using nlargest(5) for efficient top-k selection instead of sorting the entire dataset.
Ensuring the output is a Series with user IDs as index and counts as values, as requested.
Considering edge cases such as ties in counts, missing values, or self-transactions.
Validating the total number of transactions before and after normalization to catch errors.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.