Window Functions for Trends | Dataford Interview Questions

Your question is Window Functions for Trends. 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

Write a query to calculate a running total or a moving average using window functions.

Use the provided account and transaction data. Calculate both metrics for each transaction, ordered chronologically within each account. Include transactions with no associated account.

Output

One row per transaction, with account_id, account_name, transaction_date, amount, running_total, and moving_average
The moving average must use the current transaction and up to the two preceding transactions for the same account
Order by account_id ascending with NULL account IDs last, then transaction_date and transaction_id ascending

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(100)	Account display name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Associated account identifier
transaction_date	DATE	Transaction posting date
amount	NUMERIC(10,2)	Transaction amount