SQL Window Functions Challenge | Dataford Interview Questions

Tell me about the most difficult questions you faced in the SQL round, especially those involving window functions.

For this exercise, write a PostgreSQL query that demonstrates this type of analysis using the provided account transaction data. Include only posted transactions from the first quarter of 2025.

Output

One row per included transaction, with account_id, transaction_id, transaction_date, amount, prior_amount, running_balance, and three_transaction_average
prior_amount is the previous posted transaction amount for that account; cumulative and average values are calculated chronologically
Order by account_id, transaction_date, and transaction_id

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(100)	Account display name
opened_date	DATE	Date the account was opened
status	VARCHAR(20)	Current account status

transaction_types

Column	Type	Description
type_idPK	INT	Unique transaction type identifier
type_name	VARCHAR(50)	Transaction type name
category	VARCHAR(30)	Transaction category

account_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Related account identifier
type_id	INT	Related transaction type
transaction_date	DATE	Date of the transaction
amount	NUMERIC(12,2)	Signed transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is SQL Window Functions Challenge. Start with the requirements and the three 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