Your question is DAU by Content Category. 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.
Disney+ Hotstar wants to monitor daily active users for its Sports content category. Write a PostgreSQL query that counts unique users who watched at least one Sports title during January 1 through January 3, 2025.
content_id.category = 'Sports' and timestamps fall within the specified date range.Repeated viewing events by the same user on the same day must count once. Events for other categories, unknown content IDs, null users, and timestamps outside the date range must not contribute to DAU.
| Column | Type | Description |
|---|---|---|
| content_idPK | INT | Unique identifier for a Disney+ Hotstar content title |
| content_title | VARCHAR(150) | Display title of the content |
| category | VARCHAR(50) | Content category, such as Sports or Drama |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique identifier for a viewing event |
| user_id | VARCHAR(20) | Identifier of the viewer |
| content_id | INT | Identifier of the watched catalog title |
| event_ts | TIMESTAMPTZ | Timestamp at which the viewing event occurred |