Your question is Join and Aggregate by Day. 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.
Yahoo Mail analytics needs a daily report for active US users during January 1 through January 3, 2025. Write a PostgreSQL query that joins the user, message-event, and ad-click tables without double-counting metrics.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Yahoo Mail user identifier |
| email_address | VARCHAR(255) | Yahoo Mail address |
| account_status | VARCHAR(20) | Current account state |
| country_code | VARCHAR(2) | ISO country code |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Mail event identifier |
| user_id | INT | Referenced Yahoo Mail user |
| event_type | VARCHAR(20) | Type of mail event |
| occurred_at | TIMESTAMPTZ | UTC event timestamp |
| Column | Type | Description |
|---|---|---|
| click_idPK | INT | Ad-click identifier |
| user_id | INT | Referenced Yahoo Mail user |
| clicked_at | TIMESTAMPTZ | UTC click timestamp |
| revenue | NUMERIC(8,2) | Revenue attributed to the click |
| placement | VARCHAR(30) | Yahoo Mail ad placement |