Rolling 30-Day Subscriber Usage Totals | Dataford Interview Questions

Your question is Rolling 30-Day Subscriber Usage Totals. 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.

Business Context

Bell Mobility analytics needs a daily view of subscriber data consumption to support network capacity monitoring and usage-based reporting.

Write a PostgreSQL query that calculates each active Bell Mobility subscriber's rolling 30-day data usage from the recorded usage events.

Join usage events to active subscribers only.
Aggregate multiple usage events for the same subscriber and date into daily_usage_mb.
Use a window function partitioned by subscriber to calculate rolling_30_day_mb. The window must include the current date and the preceding 29 calendar days.
Treat NULL usage values as zero and order the final output by subscriber and usage date.

bell_subscribers

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

bell_data_usage

Column	Type	Description
usage_event_idPK	INT	Unique data usage event identifier
subscriber_id	INT	Subscriber associated with the usage event
usage_date	DATE	Date on which usage was recorded
usage_mb	NUMERIC(10,2)	Data consumed in megabytes