Rolling 7-Day Telemetry Average in SQL | Dataford Interview Questions

Datadog monitors telemetry ingestion for customer accounts. Write a PostgreSQL query that calculates each active account's daily telemetry ingestion rate and its rolling 7-day average from January 1 through January 7, 2024.

Requirements

Include every active account for every date in the reporting period, including accounts with no telemetry.
Aggregate multiple telemetry records for the same account and date into one daily ingestion rate. Treat missing or NULL ingestion rates as zero.
Use a window function partitioned by account and ordered by date to calculate the average across the current date and up to the six preceding calendar dates.
Return account name, date, daily ingestion rate, and the rolling average, rounded to two decimal places. Order by account name and date.

Schema

customer_accounts

Column	Type	Description
account_idPK	INT	Unique Datadog customer account identifier
account_name	VARCHAR(100)	Customer account name
status	VARCHAR(20)	Account lifecycle status

telemetry_ingestion

Column	Type	Description
ingestion_idPK	INT	Unique telemetry measurement identifier
account_id	INT	Customer account receiving telemetry
ingestion_date	DATE	Date of the telemetry measurement
ingestion_rate	NUMERIC(12,2)	Telemetry events ingested during the measurement interval

Your question is Rolling 7-Day Telemetry Average in SQL. Start with the requirements and the two tables 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