Use COUNT(DISTINCT) across joins and understand distinct counting in aggregation
I want to see regional rep coverage — for each region (include all of them), how many distinct sales reps have closed at least one deal there? Also show the total number of transactions and total revenue. I want all regions listed even if the count is zero.
| Column | Type | Key |
|---|---|---|
| region_id | INT | PK |
| region_name | TEXT | |
| country | TEXT | |
| timezone | TEXT |
| Column | Type | Key |
|---|---|---|
| sale_id | INT | PK |
| employee_id | INT | FK → dim_employees |
| product_id | INT | FK → dim_products |
| region_id | INT | FK → dim_regions |
| sale_date | TEXT | |
| quantity | INT | |
| revenue | REAL | |
| discount | REAL |
Each hint you reveal reduces the XP you can earn. Try the query first.
The brief asks for ALL regions — including any that haven't had sales activity. Think about which join type preserves rows from the left table even with no matching rows on the right. Check whether every region in the dimension table actually appears in the fact table.
COUNT(DISTINCT s.employee_id) gives you unique reps per region
Use COALESCE for the zero-sale region to avoid NULLs