Identify Transactions During 'Golden' Membership Period

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.

Transactions During Golden Membership and First Golden Transaction Per User

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.

Table transaction

column	type	constraints
trans_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
trans_date	DATE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	trans_date	trans_id	user_id
200	2023-01-05	1	101
150.5	2023-01-07	2	102
75	2023-02-03	3	101
50	2023-02-10	4	103

Table membership

column	type	constraints
user_id	INTEGER	NOT NULL
tier	VARCHAR(20)	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE

Sample data:

end_date	start_date	tier	user_id
NULL	2022-12-15	golden	101
2023-02-01	2023-01-01	silver	102
2023-03-01	2023-02-01	golden	103
NULL	2023-01-20	golden	104

Expected output

For each user, return the earliest transaction that occurred during a 'golden' membership period. A transaction is considered within a 'golden' period if its trans_date is between start_date and end_date inclusive, where NULL end_date is treated as '2025-06-01'. Based on the sample data, user 101 has two golden transactions and we keep the earlier one; user 103 has one golden transaction; users 102 and 104 have no qualifying transactions.

amount	trans_date	trans_id	user_id
200	2023-01-05	1	101
50	2023-02-10	4	103

Hints

1. Join transaction to membership on user_id and filter to membership.tier = 'golden'.
2. Filter by date range: trans_date BETWEEN start_date AND COALESCE(end_date, DATE '2025-06-01').
3. After filtering to golden periods, use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY trans_date, trans_id) and keep only rows with row_number = 1.