Rolling 30-Day Active Users SQL | Dataford Interview Questions

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

Publicis Groupe analysts are evaluating regional engagement across a digital audience platform. Write a PostgreSQL query that measures distinct active users during the trailing 30-day window ending on 2024-02-01, assigns each region an engagement tier, and ranks regions within their tier.

Requirements

Join users to user_activity while retaining regions whose users had no qualifying activity.
Count distinct users with activity from 2024-01-03 through 2024-02-01, inclusive.
Assign tiers using High for at least 3 active users, Medium for 1 or 2, and Low for 0.
Use a window function to rank regions within each tier by active-user count descending, with region name as a deterministic tie-breaker in the final output.
Exclude users whose region is NULL.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
region	VARCHAR(50)	Geographic region assigned to the user

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity event
activity_date	DATE	Calendar date on which the activity occurred