Daily Rolling Average Balance | Dataford Interview Questions

Given a table of account balances, write a query to calculate the daily rolling average balance for each user.

For this exercise, calculate each user's average balance for the current date and the preceding two calendar days. If multiple balance records exist on one date, first determine that user's daily average. Ignore NULL balances.

Output

One row per user and balance date, with user_id, balance_date, daily_average_balance, and rolling_average_balance.
Include every user-date with a balance record, ordered by user_id and balance_date.

Schema

account_balances

Column	Type	Description
balance_idPK	INT	Unique identifier for the balance record
user_id	INT	Identifier of the account owner
balance_date	DATE	Date on which the balance was recorded
balance	NUMERIC(12,2)	Account balance recorded for the user

Your question is Daily Rolling Average Balance. 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.

Problem

Given a table of account balances, write a query to calculate the daily rolling average balance for each user.

Output

One row per user and balance date, with user_id, balance_date, daily_average_balance, and rolling_average_balance.
Include every user-date with a balance record, ordered by user_id and balance_date.

Schema

account_balances

Column	Type	Description
balance_idPK	INT	Unique identifier for the balance record
user_id	INT	Identifier of the account owner
balance_date	DATE	Date on which the balance was recorded
balance	NUMERIC(12,2)	Account balance recorded for the user

Your question is Daily Rolling Average Balance. 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.