Rolling Averages with SQL Window Functions | Dataford Interview Questions

What are window functions in SQL, and how would you use them to calculate a rolling average of account balances for Hsbc?

Use the account_balances table and calculate the average balance for each account across the current record and its two preceding records.

Output

One row per account balance record with account_id, balance_date, balance, and rolling_avg_balance.
Include all records, round the rolling average to two decimal places, and order by account_id, then balance_date.

account_balances

Column	Type	Description
balance_idPK	INT	Unique balance observation identifier
account_id	VARCHAR(20)	Hsbc customer account identifier
balance_date	DATE	Date of the balance observation
balance	DECIMAL(12,2)	Account balance on the observation date

Your question is Rolling Averages with SQL Window Functions. Start with the requirements and the one table 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.

What are window functions in SQL, and how would you use them to calculate a rolling average of account balances for Hsbc?

Use the account_balances table and calculate the average balance for each account across the current record and its two preceding records.

One row per account balance record with account_id, balance_date, balance, and rolling_avg_balance.
Include all records, round the rolling average to two decimal places, and order by account_id, then balance_date.

account_balances

Column	Type	Description
balance_idPK	INT	Unique balance observation identifier
account_id	VARCHAR(20)	Hsbc customer account identifier
balance_date	DATE	Date of the balance observation
balance	DECIMAL(12,2)	Account balance on the observation date

Your question is Rolling Averages with SQL Window Functions. Start with the requirements and the one table 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.