Rolling 30-Day Active Users

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Rolling 30-Day Active Users. Start with the requirements and the one table 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.

You need to log in / sign up to run or submit.

Problem

BCG wants to monitor engagement across its internal digital products and analytics surfaces. Using a raw event log, calculate the number of unique users active during the trailing 30 calendar days for each date in the event range.

Write a PostgreSQL query that produces one row per calendar date from the minimum through maximum event date.

Requirements

Count each non-null user_id only once within each 30-day window.
Include the current date and the preceding 29 calendar days in each window.
Include dates with no events between the minimum and maximum event dates.
Sort the output chronologically by date.

Schema

raw_event_log

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_timestamp	TIMESTAMP	Timestamp when the event occurred
event_type	VARCHAR(30)	Type of user event

Tablesraw_event_log

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results