SQL Rolling Average With Spikes | Dataford Interview Questions

Your question is SQL Rolling Average With Spikes. Start with the requirements and the two tables on the right.

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

Problem

Point72's trading analytics team wants to monitor unusual daily activity by ticker. Write a PostgreSQL query that aggregates trade volume by ticker and trading date, then compares each day with the prior seven completed trading observations.

Requirements

Join trades to active securities only and aggregate total volume per ticker and date.
Calculate a seven-observation rolling average and sample standard deviation using window functions. Exclude the current day from the baseline.
Flag a day as a volume spike when its volume exceeds the rolling average plus two rolling standard deviations. Return false when the baseline is not yet available.
Return results ordered by ticker and trade date.

Schema

trades

Column	Type	Description
trade_idPK	INT	Unique trade identifier
ticker	VARCHAR(10)	Ticker symbol referenced by securities
trade_date	DATE	Date on which the trade occurred
volume	INT	Number of shares traded

securities

Column	Type	Description
tickerPK	VARCHAR(10)	Security ticker symbol
security_name	VARCHAR(100)	Security display name
is_active	BOOLEAN	Whether the security is actively monitored