Detect Pattern Day Traders | Dataford Interview Questions

Write a query to identify users who have executed more than four day trades within a rolling five-day window to flag potential pattern day traders on Robinhood.

Use the provided users and day_trades tables. Treat each row in day_trades as one completed day trade, and evaluate five calendar-day windows ending on a user's trade date.

Output

Return one row per qualifying user.
Include user_id, username, window_start, window_end, and day_trade_count.
Return the user's qualifying window with the highest trade count, breaking ties by the earliest window end date.
Order results by user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Robinhood user identifier
username	VARCHAR(100)	Robinhood username
account_status	VARCHAR(20)	Current account status

day_trades

Column	Type	Description
trade_idPK	INT	Unique day trade identifier
user_id	INT	User who executed the trade
symbol	VARCHAR(10)	Traded security symbol
executed_at	TIMESTAMP	Timestamp when the day trade was executed

Your question is Detect Pattern Day Traders. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem