Use LEFT JOIN to find non-matching rows
How many customers in our database have never made a purchase? Need a count for the re-engagement push — planning meeting's tomorrow morning, so before EOD if you can. Just the number.
| Column | Type | Key |
|---|---|---|
| customer_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| TEXT | ||
| signup_date | TEXT | |
| city | TEXT | |
| state | TEXT | |
| age | INT | |
| gender | TEXT |
| Column | Type | Key |
|---|---|---|
| purchase_id | INT | PK |
| customer_id | INT | FK → dim_customers |
| purchase_date | TEXT | |
| amount | REAL | |
| product_category | TEXT | |
| campaign_id | INT | FK → dim_campaigns |
| coupon_code | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Use a `LEFT JOIN` from `dim_customers` to `fact_purchases` on `customer_id` — this keeps all customers, even those with no purchases
After the LEFT JOIN, customers with no purchases will have NULL in the fact_purchases columns. Filter with `WHERE p.purchase_id IS NULL`
One row out, one number — the count of unmatched customer rows. Alias it so Sarah's slide reads cleanly.