JOIN two tables and compute a percentage
Can you break down our send performance by channel? I need the channel name, total sends, total opens, and the open rate as a percentage. We're deciding where to invest next quarter's budget.
| Column | Type | Key |
|---|---|---|
| send_id | INT | PK |
| campaign_id | INT | FK → dim_campaigns |
| customer_id | INT | FK → dim_customers |
| sent_at | TEXT | |
| opened | INT | |
| clicked | INT |
| Column | Type | Key |
|---|---|---|
| campaign_id | INT | PK |
| campaign_name | TEXT | |
| channel | TEXT | |
| start_date | TEXT | |
| end_date | TEXT | |
| budget | REAL | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
JOIN `fact_sends` to `dim_campaigns` on `campaign_id` to get the `channel` for each send
Open rate = opens ÷ sends × 100. Use `ROUND(100.0 * SUM(s.opened) / COUNT(*), 2) AS open_rate`
GROUP BY `channel` and ORDER BY open_rate DESC to see the best-performing channel first