Window Functions for Rolling Averages | Dataford Interview Questions

Dell Technologies uses Dell APEX usage analytics to evaluate engagement across customer signup cohorts. Write a PostgreSQL query that creates a daily user activity series for a fixed reporting period, calculates each user's 7-day rolling average, and ranks users within their cohort for each day.

Requirements

Use 2025-02-01 through 2025-02-05 as the reporting period.
Aggregate multiple usage events for the same user and day. Treat missing or NULL usage as zero.
Include users with no usage events during the reporting period.
Calculate a calendar-based rolling average using the current day and up to the six preceding days.
Rank users within each signup-month cohort and activity date by rolling average descending. Ties must receive the same rank.
Return cohort_month, activity_date, user_id, rolling_avg_units, and cohort_rank, ordered by cohort, date, and rank.

Schema

apex_users

Column	Type	Description
user_idPK	INTEGER	Unique APEX user identifier
signup_date	DATE	Date the user joined Dell APEX
region	VARCHAR(30)	Customer operating region

apex_usage_events

Column	Type	Description
event_idPK	INTEGER	Unique usage event identifier
user_id	INTEGER	References apex_users.user_id
event_date	DATE	Date of the usage event
units_used	INTEGER	APEX capacity units consumed in the event

Your question is Window Functions for Rolling Averages. 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