Bandwidth Usage Moving Averages | Dataford Interview Questions

Your question is Bandwidth Usage Moving Averages. 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

Netflix Streaming Quality needs a subscriber-level view of bandwidth consumption. Write a PostgreSQL query that summarizes daily bandwidth usage for the requested date range and applies window functions per subscriber.

Requirements

Join subscriber metadata to usage_events and include usage from April 1 through April 7, 2024.
Aggregate multiple usage events on the same subscriber and date into daily_bandwidth_mb. Treat a day containing only NULL bandwidth values as zero.
Calculate a three-row moving average, including the current day and up to the two preceding usage days for that subscriber.
Calculate the cumulative distribution of each subscriber's daily bandwidth using CUME_DIST(), ordered from lowest to highest daily usage. Return rows ordered by subscriber and usage date.

Schema

subscribers

Column	Type	Description
subscriber_idPK	INT	Unique subscriber identifier
profile_name	VARCHAR(50)	Netflix profile name
plan_tier	VARCHAR(20)	Subscription plan tier

usage_events

Column	Type	Description
usage_idPK	INT	Unique bandwidth event identifier
subscriber_id	INT	Subscriber associated with the event
usage_date	DATE	Date of bandwidth usage
bandwidth_mb	NUMERIC(10,2)	Bandwidth consumed in megabytes
playback_surface	VARCHAR(30)	Netflix playback device or surface