SQL Rolling Average by Asset Class | Dataford Interview Questions

Your question is SQL Rolling Average by Asset Class. Start with the requirements and the one table 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 short-term changes in transaction volume across its market data. Write a PostgreSQL query that calculates a seven-day moving average for each ticker, while keeping asset classes separate.

Requirements

Aggregate transaction volume by asset_class, ticker, and trade_date before applying the window function.
Calculate the moving average using the current date and the preceding six calendar days. Include only dates present in the daily aggregate; do not create missing calendar dates.
Partition the window by both asset_class and ticker, and order each partition chronologically by trade_date.
Return the asset class, ticker, trade date, daily volume, and moving average, ordered by asset class, ticker, and trade date.

A NULL volume should be ignored by SUM and AVG, consistent with PostgreSQL aggregate behavior.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
ticker	VARCHAR(10)	Ticker or traded instrument symbol
asset_class	VARCHAR(20)	Asset class of the traded instrument
trade_date	DATE	Date on which the transaction occurred
volume	DECIMAL(14,2)	Transaction volume

Your question is SQL Rolling Average by Asset Class. Start with the requirements and the one table 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.

Requirements

Aggregate transaction volume by asset_class, ticker, and trade_date before applying the window function.
Calculate the moving average using the current date and the preceding six calendar days. Include only dates present in the daily aggregate; do not create missing calendar dates.
Partition the window by both asset_class and ticker, and order each partition chronologically by trade_date.
Return the asset class, ticker, trade date, daily volume, and moving average, ordered by asset class, ticker, and trade date.

A NULL volume should be ignored by SUM and AVG, consistent with PostgreSQL aggregate behavior.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
ticker	VARCHAR(10)	Ticker or traded instrument symbol
asset_class	VARCHAR(20)	Asset class of the traded instrument
trade_date	DATE	Date on which the transaction occurred
volume	DECIMAL(14,2)	Transaction volume