SQL Window Functions for Rolling DAU | Dataford Interview Questions

Your question is SQL Window Functions for Rolling DAU. Start with the requirements and the three 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

The PlayStation Plus analytics team wants to monitor subscriber engagement over a ten-day period. Write a PostgreSQL query that calculates daily active users, or DAU, for PlayStation Plus subscribers and the 7-day rolling average of that DAU.

Requirements

Count each PlayStation Plus subscriber at most once per calendar day, even if they generate multiple activity events.
Include calendar dates with zero qualifying activity so the rolling window represents seven consecutive days.
Treat a user as a subscriber only when the activity date falls between their subscription start and end dates, inclusively.
Return the date, daily DAU, and rolling average rounded to two decimal places. For the first six dates, calculate the average over the available dates.

Schema

calendar_dates

Column	Type	Description
calendar_datePK	DATE	Calendar date in the reporting period

playstation_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	PlayStation user who generated the event
activity_date	DATE	Date on which the activity occurred

plus_subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription record identifier
user_id	INT	PlayStation user with a subscription
plus_start_date	DATE	Inclusive subscription start date
plus_end_date	DATE	Inclusive subscription end date