SQL Quest › SQL Interview Questions › Joins
LEFT JOIN NULL Semantics: Inactive Customers
Build a customer activity report that correctly handles NULLs from LEFT JOINs. For each customer, show name, membership, order_count (0 for customers with no orders, not NULL), total_spent (0.00 for no orders), and status ('active' if any orders exist, 'inactive' otherwise). Sort by order_count DESC, name. Understanding NULL semantics in LEFT JOINs is tested at every FAANG company because most candidates incorrectly assume COUNT and SUM handle NULLs automatically.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
customers
| customer_id | name | signup_date | membership | total_orders | |
|---|---|---|---|---|---|
| 1 | John Smith | john.smith@email.com | 2023-01-15 | Gold | 15 |
| 2 | Emma Wilson | emma.wilson@email.com | 2023-03-20 | Silver | 8 |
| 3 | Michael Brown | michael.brown@email.com | 2023-02-10 | Gold | 12 |
orders
| order_id | customer_id | product | category | quantity | price | total | order_date | country | status |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Laptop Pro | Electronics | 1 | 1299.99 | 1299.99 | 2024-01-15 | USA | completed |
| 2 | 2 | Wireless Mouse | Electronics | 2 | 49.99 | 99.98 | 2024-01-16 | Canada | completed |
| 3 | 3 | Office Chair | Furniture | 1 | 349.99 | 349.99 | 2024-01-17 | USA | completed |
Expected output: order_count: 0, total_spent: 0.00, status: inactive
Hint
Concepts
SELECT LEFT JOIN COALESCE Aggregation GROUP BY CASE LEFT JOIN + NULL
Practise the topic: SQL practice questions · JOIN practice · NULL handling practice · GROUP BY exercises · CASE WHEN practice
In these company practice sets
Airbnb · Plaid · Shopify · Snowflake
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test