SQL Window Totals and Averages | Dataford Interview Questions

Your question is SQL Window Totals and Averages. 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

Business Context

Balyasny Asset Management needs a daily view of execution activity across instruments to support portfolio and trading analysis.

Task

Write a PostgreSQL query that joins trade executions to instrument metadata, aggregates filled executions by ticker and trading day, and calculates running and sliding-window metrics.

Requirements

Include only executions with execution_status = 'FILLED'.
Aggregate daily executed quantity and daily notional value by ticker and trade date.
Calculate a cumulative executed quantity and cumulative notional value for each ticker.
Calculate the average daily executed quantity over the current day and preceding 29 calendar days. Use a date-based RANGE window, not a row-count window.
Return results ordered by ticker and trade date.

Schema

trade_executions

Column	Type	Description
execution_idPK	INT	Unique execution identifier
instrument_id	INT	References instruments.instrument_id
executed_at	TIMESTAMPTZ	Execution timestamp
quantity	NUMERIC(18,2)	Executed quantity
execution_price	NUMERIC(18,4)	Execution price
execution_status	VARCHAR(20)	Execution lifecycle status
trader_id	VARCHAR(30)	BAM trader identifier

instruments

Column	Type	Description
instrument_idPK	INT	Unique instrument identifier
ticker	VARCHAR(20)	Instrument ticker
asset_class	VARCHAR(30)	Instrument asset classification