Identify Transactions During 'Golden' Membership Period
Company: Robinhood
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
transaction
+-----------+---------+------------+---------+
| trans_id | user_id | trans_date | amount |
+-----------+---------+------------+---------+
| 1 | 101 | 2023-01-05 | 200.00 |
| 2 | 102 | 2023-01-07 | 150.50 |
| 3 | 101 | 2023-02-03 | 75.00 |
| 4 | 103 | 2023-02-10 | 50.00 |
+-----------+---------+------------+---------+
membership
+---------+---------+------------+------------+
| user_id | tier | start_date | end_date |
+---------+---------+------------+------------+
| 101 | golden | 2022-12-15 | NULL |
| 102 | silver | 2023-01-01 | 2023-02-01 |
| 103 | golden | 2023-02-01 | 2023-03-01 |
| 104 | golden | 2023-01-20 | NULL |
+---------+---------+------------+------------+
##### Scenario
Robinhood interview for an Analytics-Engineer/DS role: product database has transaction and membership tables plus a log of P2P messages.
##### Question
SQL: Return every transaction that took place while the user’s membership tier was ‘golden’. end_date is NULL for still-active subscriptions. SQL follow-up: Using a window function (ROW_NUMBER or RANK), pick the first transaction made by each user during any ‘golden’ subscription period. Python: Given a list/DF of messages with sender_id and receiver_id, output the five users who sent the most messages and the five who received the most.
##### Hints
Think joins on date ranges, COALESCE(end_date, CURRENT_DATE), window partition by user, Counter/collections for frequency tally.
Overview: This question evaluates proficiency in temporal data manipulation and aggregation, covering SQL date-range joins, window functions for per-user ranking, and Python frequency counting of senders and receivers, and it falls under the Data Manipulation (SQL/Python) domain for data scientist roles.
You are given two tables: transaction and membership. A user can have a membership tier over a date range, where end_date is NULL for still-active memberships. Assume the current date is 2025-06-01 when treating open-ended memberships.
1) First, find all transactions that occurred while the user’s membership tier was 'golden' (i.e., trans_date is between start_date and end_date inclusive, with NULL end_date treated as '2025-06-01').
2) Then, using a window function (e.g., ROW_NUMBER), return only the first transaction made by each user during any 'golden' subscription period.
Tables
transaction(trans_id INTEGER, user_id INTEGER, trans_date DATE, amount DECIMAL(10,2))
membership(user_id INTEGER, tier VARCHAR(20), start_date DATE, end_date DATE)
Hints
- Join transaction to membership on user_id and filter to membership.tier = 'golden'.
- Filter by date range: trans_date BETWEEN start_date AND COALESCE(end_date, DATE '2025-06-01').