Running 30-Day Active Users SQL | Dataford Interview Questions

General Motors wants to monitor rolling engagement with OnStar connected vehicle services. Write a PostgreSQL query that calculates the number of distinct users active during the 30-day period ending on each reporting date.

Requirements

Deduplicate repeated OnStar events for the same user on the same date using a SQL window function.
Count distinct non-null users whose activity date falls between report_date - INTERVAL '29 days' and report_date, inclusive.
Return every reporting date, including dates with no activity, ordered chronologically.

Schema

onstar_service_events

Column	Type	Description
event_idPK	INTEGER	Unique event identifier
user_id	VARCHAR(20)	Connected vehicle service user identifier
event_date	DATE	Date of the OnStar service event
service_name	VARCHAR(40)	Connected vehicle service used

reporting_dates

Column	Type	Description
report_datePK	DATE	Date for which active users are calculated

Your question is Running 30-Day Active Users 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