SQL Quest › SQL Interview Questions › Joins
Counting Across a JOIN
Retention wants how many orders each customer has placed, most active first.
Join orders to customers, and show name and order_count. Order by order_count descending, then name.
The new idea: GROUP BY works across a join exactly like it works on one table. Join first, group second — SQL builds the combined rows, then collapses them.
Now count your output rows. You get 10, not 16. This is the inner join from the previous challenge dropping the six customers with no orders, and it matters: a "customers by order count" report that silently omits your least engaged customers is the report you'd most want them in. Fixing that is what challenge 106 does with a LEFT JOIN.
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
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 |
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 |
Expected output: 10 rows — John Smith 6, Emma Wilson 5, Michael Brown 5, ...
Hint
SELECT, JOIN, GROUP BY, and open the hint there if you stall.Concepts
SELECT JOIN GROUP BY COUNT
Practise the topic: SQL practice questions · JOIN practice · GROUP BY exercises
In these company practice sets
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