Top Assets Over Rolling 30 Days | Dataford Interview Questions

Write a query to identify top-performing assets based on user transaction volume over a rolling 30-day window at Robinhood.

Use completed transactions and define the rolling window as the 30-day period ending at the latest transaction timestamp. Return the top three transaction-volume ranks, including ties.

Output

One row per included asset with asset_id, symbol, transaction_count, active_users, total_notional, and volume_rank.
Include only completed transactions in the rolling window, and include all assets tied within the top three ranks.
Order by volume_rank, transaction_count descending, total_notional descending, and asset_id.

Schema

assets

Column	Type	Description
asset_idPK	VARCHAR(20)	Unique Robinhood asset identifier
symbol	VARCHAR(12)	Tradable asset symbol
asset_name	VARCHAR(100)	Display name of the asset
asset_class	VARCHAR(30)	Asset classification

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	Robinhood user who placed the transaction
asset_id	VARCHAR(20)	Asset involved in the transaction
executed_at	TIMESTAMP	Timestamp when the transaction was executed or recorded
status	VARCHAR(20)	Transaction processing status
notional_value	NUMERIC(14,2)	Transaction value in US dollars

Your question is Top Assets Over Rolling 30 Days. 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