Katkat
Back

List Transactions During Active 'Gold' Membership Period

RobinhoodData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency in SQL-based data manipulation and temporal reasoning, specifically the ability to correlate membership periods with transaction records using relational operations.

customer_profile

+-------------+----------------+--------------------+-------------------+ | customer_id | membership_type| membership_start_date| membership_end_date| +-------------+----------------+--------------------+-------------------+ | 101 | Gold | 2023-01-01 | 2023-12-31 | | 102 | Silver | 2023-02-15 | 2023-08-15 | | 103 | Gold | 2023-03-01 | 2023-09-01 | +-------------+----------------+--------------------+-------------------+

​

transactions

+---------------+-------------+-----------------+--------+ | transaction_id| customer_id | transaction_date| amount | +---------------+-------------+-----------------+--------+ | 1001 | 101 | 2023-03-10 | 120.00 | | 1002 | 102 | 2023-09-01 | 50.00 | | 1003 | 103 | 2023-05-05 | 300.00 | +---------------+-------------+-----------------+--------+

Scenario

Fintech platform wants to list every transaction that occurred while a customer’s specific membership tier was active.

Question

Given tables customer_profile (customer_id, membership_type, membership_start_date, membership_end_date) and transactions (transaction_id, customer_id, transaction_date, amount), write a SQL query that returns all transactions that happened during the customer’s active 'Gold' membership period.

Hints

Join on customer_id, filter membership_type, date between start and end (inclusive).