Write SQL to localize trading drop contributors
Company: Robinhood
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Use the schema and samples below. Unless stated, treat a user as active on a day if they attempted any order (any status). Dates are inclusive.
Schema (ANSI SQL-friendly):
- users(user_id, signup_date DATE, geo STRING, acquisition_channel STRING)
- accounts(account_id, user_id, kyc_status STRING, funding_method STRING, first_fund_date DATE)
- orders(order_id, account_id, ts TIMESTAMP, asset_class STRING, order_type STRING, side STRING, quantity DECIMAL, status STRING, reject_code STRING, platform STRING)
- releases(release_id, ts TIMESTAMP, feature_name STRING, platform STRING)
- market_index(date DATE, sp500_return DECIMAL, vix_close DECIMAL, is_holiday BOOLEAN)
Small ASCII samples:
users
user_id | signup_date | geo | acquisition_channel
1 | 2025-05-20 | US-CA | ads
2 | 2025-06-03 | US-NY | referral
3 | 2025-06-15 | US-TX | organic
4 | 2025-07-05 | US-CA | ads
5 | 2025-07-12 | US-FL | organic
accounts
account_id | user_id | kyc_status | funding_method | first_fund_date
10 | 1 | approved | ach | 2025-05-22
11 | 2 | approved | wire | 2025-06-04
12 | 3 | pending | ach | null
13 | 4 | approved | ach | 2025-07-07
14 | 5 | approved | card | 2025-07-13
orders
order_id | account_id | ts | asset_class | order_type | side | quantity | status | reject_code | platform
100 | 10 | 2025-06-10 15:20:00 | equity | market | buy | 10 | executed | null | ios
101 | 11 | 2025-06-12 10:05:00 | equity | limit | sell | 5 | executed | null | web
102 | 11 | 2025-07-11 09:33:00 | equity | market | buy | 8 | rejected | R201 | web
103 | 13 | 2025-07-12 09:35:00 | crypto | market | buy | 0.2 | executed | null | android
104 | 13 | 2025-07-24 13:02:00 | equity | limit | buy | 3 | canceled | null | android
105 | 14 | 2025-07-25 10:01:00 | equity | market | buy | 2 | rejected | R305 | ios
106 | 10 | 2025-07-26 11:20:00 | option | market | buy | 1 | executed | null | ios
107 | 11 | 2025-08-01 14:07:00 | equity | limit | buy | 4 | executed | null | web
108 | 11 | 2025-08-05 09:31:00 | equity | market | sell | 2 | rejected | R201 | web
109 | 10 | 2025-08-10 15:45:00 | equity | market | buy | 6 | executed | null | ios
110 | 14 | 2025-08-15 12:12:00 | equity | limit | buy | 1 | executed | null | ios
111 | 13 | 2025-08-21 10:00:00 | crypto | market | sell | 0.1 | executed | null | android
releases
release_id | ts | feature_name | platform
1 | 2025-07-10 08:00:00 | onboarding_v2 | ios
2 | 2025-07-10 08:00:00 | onboarding_v2 | android
3 | 2025-07-10 08:00:00 | onboarding_v2 | web
market_index
date | sp500_return | vix_close | is_holiday
2025-06-10 | 0.3 | 15.2 | 0
2025-06-12 | -0.4 | 18.1 | 0
2025-07-11 | -1.2 | 22.5 | 0
2025-07-24 | 0.1 | 17.0 | 0
2025-07-25 | 0.0 | 16.8 | 0
2025-08-01 | -0.2 | 19.3 | 0
2025-08-05 | -0.6 | 21.0 | 0
2025-08-10 | 0.5 | 15.0 | 0
2025-08-15 | 0.2 | 14.7 | 0
2025-08-21 | -0.1 | 14.9 | 0
Tasks (write ANSI SQL; use CTEs if helpful):
A) Daily executed_trades_per_active_user by platform and signup cohort (signup_month) for 2025-06-01–2025-08-21. Definitions: executed_trades = count of orders.status='executed'; active users = distinct users with any order that day (any status). Exclude market_index.is_holiday = true by left-joining on date.
B) Attribution: Using baseline window 2025-06-01–2025-06-28 and impact window 2025-07-24–2025-08-21, compute per-segment change by [platform × asset_class × order_type]. For each segment, report: baseline mean executed_trades/day, impact mean, absolute delta, and share of total drop. Return top 5 segments by absolute contribution.
C) Quality: Post 2025-07-10, list the top 10 reject_code values whose rejection rate increased the most, controlling for asset_class and platform (i.e., compare within each asset_class×platform cell). Return code, cell, baseline rate (2025-06-01–2025-07-09), post rate (2025-07-10–2025-08-21), and delta.
D) Optional: Produce a user-day dataset for ML uplift modeling with columns [date, user_id, platform, acquisition_channel, tenure_days, funded_flag, executed_trades, any_order_attempt, rejection_rate_day, vix_close, sp500_return, is_holiday] and target executed_trades>0.
Overview: This question evaluates proficiency in SQL-based data manipulation and analytics, including cohorting by signup month, daily aggregation of executed trades per active user, segmentation by platform and geolocation, and competency with joins, timestamps, and operational flags.
Daily Executed Trades per Active User by Platform and Signup Cohort
Using the tables below, compute a daily metric for the period 2025-06-01 to 2025-08-21 (inclusive). For each calendar day, platform, and signup cohort (signup_month), return:
- date
- platform
- signup_month (cohort based on users.signup_date, truncated to the first day of that month)
- executed_trades (count of orders where status = 'executed')
- active_users (distinct users who attempted any order that day on that platform, regardless of status)
- executed_trades_per_active_user = executed_trades / active_users
A user is considered active on a given day if they attempted at least one order (any status) on that day. Derive the date from orders.ts. Exclude days that are holidays by left-joining to market_index on date and filtering out rows where market_index.is_holiday = TRUE. Return one row per (date, platform, signup_month), ordered by date, platform, signup_month.
Tables
users(user_id INT, signup_date DATE, geo VARCHAR(10), acquisition_channel VARCHAR(20))
accounts(account_id INT, user_id INT, kyc_status VARCHAR(20), funding_method VARCHAR(20), first_fund_date DATE)
orders(order_id INT, account_id INT, ts TIMESTAMP, asset_class VARCHAR(20), order_type VARCHAR(20), side VARCHAR(10), quantity DECIMAL(18,4), status VARCHAR(20), reject_code VARCHAR(10), platform VARCHAR(20))
releases(release_id INT, ts TIMESTAMP, feature_name VARCHAR(50), platform VARCHAR(20))
market_index(date DATE, sp500_return DECIMAL(5,2), vix_close DECIMAL(5,2), is_holiday BOOLEAN)
Hints
- Join orders to accounts and users to get user_id and signup_date, and derive the calendar date from orders.ts.
- Aggregate by date, platform, and signup_month; use SUM over a CASE expression for executed_trades and COUNT(DISTINCT user_id) for active_users.
Attribution of Trading Drop by Platform × Asset Class × Order Type
Using the tables below, attribute the change in executed trading activity between a baseline and an impact window to segments defined by [platform × asset_class × order_type].
1. Define the baseline window as 2025-06-01 to 2025-06-28 (inclusive).
2. Define the impact window as 2025-07-24 to 2025-08-21 (inclusive).
3. For each segment (platform, asset_class, order_type) and each window:
- For every calendar day in that window where the segment has at least one order (any status), compute executed_trades_day = count of orders with status = 'executed' in that segment on that day.
- Compute baseline_mean_executed_trades_per_day as the average of executed_trades_day over the baseline window for that segment.
- Compute impact_mean_executed_trades_per_day similarly over the impact window.
- If a segment has no orders in a window, treat its mean as 0 for that window.
4. For each segment, compute:
- absolute_delta = ABS(impact_mean_executed_trades_per_day - baseline_mean_executed_trades_per_day)
- contribution_to_drop = (baseline_mean_executed_trades_per_day - impact_mean_executed_trades_per_day) for segments where the impact mean is lower than the baseline (i.e., true drops; otherwise 0).
- share_of_total_drop = contribution_to_drop / SUM(contribution_to_drop over all segments with a drop).
Return the top 5 segments ordered by absolute_delta descending, including for each:
- platform
- asset_class
- order_type
- baseline_mean_executed_trades_per_day
- impact_mean_executed_trades_per_day
- absolute_delta
- share_of_total_drop
If fewer than 5 segments have a drop, return only the segments that dropped.
Tables
users(user_id INT, signup_date DATE, geo VARCHAR(10), acquisition_channel VARCHAR(20))
accounts(account_id INT, user_id INT, kyc_status VARCHAR(20), funding_method VARCHAR(20), first_fund_date DATE)
orders(order_id INT, account_id INT, ts TIMESTAMP, asset_class VARCHAR(20), order_type VARCHAR(20), side VARCHAR(10), quantity DECIMAL(18,4), status VARCHAR(20), reject_code VARCHAR(10), platform VARCHAR(20))
releases(release_id INT, ts TIMESTAMP, feature_name VARCHAR(50), platform VARCHAR(20))
market_index(date DATE, sp500_return DECIMAL(5,2), vix_close DECIMAL(5,2), is_holiday BOOLEAN)
Hints
- First compute daily executed_trades per (platform, asset_class, order_type) separately for the baseline and impact windows, then take an average per window.
- After you have baseline and impact means per segment, compute deltas, then a second pass (or window/aggregate) to compute the total drop and each segment's share of that drop.
Rejection Codes with the Largest Increase in Rejection Rate by Asset Class and Platform
Post 2025-07-10, identify reject_code values whose rejection rate increased the most, controlling for asset_class and platform.
1. Define a baseline window: 2025-06-01 to 2025-07-09 (inclusive).
2. Define a post window: 2025-07-10 to 2025-08-21 (inclusive).
3. For each cell (asset_class, platform) and reject_code, compute:
- baseline_rejection_rate = (number of rejected orders with that reject_code in that cell during the baseline window) / (total number of orders in that cell during the baseline window).
- post_rejection_rate = (number of rejected orders with that reject_code in that cell during the post window) / (total number of orders in that cell during the post window).
- Treat missing counts as 0 (e.g., if a code never appears in the baseline window, its baseline_rejection_rate is 0).
4. Compute delta = post_rejection_rate - baseline_rejection_rate.
Return the top 10 (reject_code, asset_class, platform) combinations where delta > 0, ordered by delta descending, with columns:
- reject_code
- asset_class
- platform
- baseline_rejection_rate
- post_rejection_rate
- delta
Tables
users(user_id INT, signup_date DATE, geo VARCHAR(10), acquisition_channel VARCHAR(20))
accounts(account_id INT, user_id INT, kyc_status VARCHAR(20), funding_method VARCHAR(20), first_fund_date DATE)
orders(order_id INT, account_id INT, ts TIMESTAMP, asset_class VARCHAR(20), order_type VARCHAR(20), side VARCHAR(10), quantity DECIMAL(18,4), status VARCHAR(20), reject_code VARCHAR(10), platform VARCHAR(20))
releases(release_id INT, ts TIMESTAMP, feature_name VARCHAR(50), platform VARCHAR(20))
market_index(date DATE, sp500_return DECIMAL(5,2), vix_close DECIMAL(5,2), is_holiday BOOLEAN)
Hints
- Compute total orders per (asset_class, platform) and rejected orders per (asset_class, platform, reject_code) separately for the baseline and post windows.
- Build a list of all (asset_class, platform, reject_code) combinations, join in baseline and post counts, turn missing values into zeros, and compare the resulting rejection rates.
User-Day Dataset for Trading Uplift Modeling
Produce a user-day dataset suitable for ML uplift modeling using the tables below. For each user-day where the user attempted at least one order, output one row with the following columns and a binary target:
- date: calendar date of activity (from orders.ts)
- user_id
- platform: the platform of the orders on that day (one row per user_id × date × platform)
- acquisition_channel: from users.acquisition_channel
- tenure_days: number of days between users.signup_date and date (date - signup_date)
- funded_flag: 1 if the user has any account whose first_fund_date is not NULL and first_fund_date <= date, otherwise 0
- executed_trades: count of orders with status = 'executed' for that user on that date and platform
- any_order_attempt: 1 if the user placed at least one order that date on that platform (should always be 1 for included rows)
- rejection_rate_day: number of rejected orders / total orders for that user on that date and platform
- vix_close: from market_index for that date
- sp500_return: from market_index for that date
- is_holiday: from market_index for that date
- target_executed_trades_gt_0: 1 if executed_trades > 0 on that date and platform, else 0
Base the dataset on the sample data as-is (no specific date window required). Return the complete user-day dataset for the sample data, ordered by date, user_id, and platform.
Tables
users(user_id INT, signup_date DATE, geo VARCHAR(10), acquisition_channel VARCHAR(20))
accounts(account_id INT, user_id INT, kyc_status VARCHAR(20), funding_method VARCHAR(20), first_fund_date DATE)
orders(order_id INT, account_id INT, ts TIMESTAMP, asset_class VARCHAR(20), order_type VARCHAR(20), side VARCHAR(10), quantity DECIMAL(18,4), status VARCHAR(20), reject_code VARCHAR(10), platform VARCHAR(20))
releases(release_id INT, ts TIMESTAMP, feature_name VARCHAR(50), platform VARCHAR(20))
market_index(date DATE, sp500_return DECIMAL(5,2), vix_close DECIMAL(5,2), is_holiday BOOLEAN)
Hints
- Aggregate orders to the user_id × date × platform level to compute total_orders, executed_trades, and rejected_trades.
- Join in user signup_date and first_fund_date to compute tenure_days and funded_flag, and join market_index on date for the market covariates.