D8LooPFocus modeCODE WORKSPACE
Bucket missing countries
For active (not soft-deleted) customers, return customer_id and country_bucket, where country_bucket is the country or the text 'unknown' when the country is missing. Order by customer_id.
Filter deleted_at IS NULL, then wrap country in COALESCE(country, 'unknown') and alias it country_bucket.
Sample input
customersAll 6 customers. Customer 2 has a missing country; customer 5 is soft-deleted (deleted_at is set).
| customer_id | country | deleted_at |
|---|---|---|
| 1 | US | null |
| 2 | null | null |
| 3 | GB | null |
| 4 | IN | null |
| 5 | US | 2026-01-20 |
| 6 | GB | null |
Expected output
Expected outputCustomer 2's NULL country becomes 'unknown'; customer 5 is excluded because deleted_at is not NULL.
| customer_id | country_bucket |
|---|---|
| 1 | US |
| 2 | unknown |
| 3 | GB |
| 4 | IN |
| 6 | GB |
Constraints
Active means deleted_at IS NULL. Replace a missing country with COALESCE(country, 'unknown'). Do not use country = NULL.
Expected skills
Testing for missing values with IS NULL and substituting defaults with COALESCE.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.