Rolling 7-Day Usage SQL | Dataford Interview Questions

Your question is Rolling 7-Day Usage SQL. 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

Verizon's network analytics team wants to monitor short-term data consumption trends for active subscribers. Write a PostgreSQL query that calculates a rolling 7-day average of daily data usage for each active subscriber.

Requirements

Aggregate multiple usage records from the same subscriber and date into one daily total.
Return one row per active subscriber and usage date, including the daily total in megabytes.
Calculate the average over the current date and the six preceding calendar days using a window function. Missing dates should not be treated as zero usage.
Sort the output by subscriber ID and usage date.

Schema

subscriber_accounts

Column	Type	Description
subscriber_idPK	INT	Unique Verizon subscriber identifier
subscriber_name	VARCHAR(100)	Subscriber display name
status	VARCHAR(20)	Current account status

subscriber_usage

Column	Type	Description
usage_idPK	INT	Unique usage event identifier
subscriber_id	INT	Subscriber associated with the usage event
usage_date	DATE	Date of mobile data usage
data_usage_mb	NUMERIC(12,2)	Data consumed in megabytes