Apple: Genius Bar No-Shows at Every Store — SQL Problem
The retail operations team wants to know which stores lose the most Genius Bar slots to customers who book an appointment and never turn up.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #598
Problem statement
The retail operations team wants to know which stores lose the most Genius Bar slots to customers who book an appointment and never turn up.
For every store, return store_name and no_shows, the number of its appointments whose status is no_show. A store with no no-shows — including a store with no appointments at all — appears with 0. Return the rows in any order.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: Store
| Column | Type |
|---|---|
| store_id | int |
| store_name | varchar |
| city | varchar |
Primary key: store_id.
One row per retail store.
Table: GeniusAppointment
| Column | Type |
|---|---|
| appointment_id | int |
| store_id | int |
| customer_id | int |
| slot_at | datetime |
| status | enum(completed, no_show, cancelled) |
Primary key: appointment_id.
One row per booked Genius Bar slot; store_id always names a store.
Examples
Example 1
Store
| store_id | store_name | city |
|---|---|---|
| 1 | Apple BKC | Mumbai |
| 2 | Apple Saket | Delhi |
| 3 | Apple Regent Street | London |
| 4 | Apple Ginza | Tokyo |
GeniusAppointment
| appointment_id | store_id | customer_id | slot_at | status |
|---|---|---|---|---|
| 101 | 1 | 501 | 2025-03-03 10:00:00 | no_show |
| 102 | 1 | 502 | 2025-03-03 10:30:00 | completed |
| 103 | 1 | 503 | 2025-03-04 11:00:00 | no_show |
| 104 | 2 | 504 | 2025-03-03 12:00:00 | cancelled |
| 105 | 2 | 505 | 2025-03-05 16:00:00 | completed |
| 106 | 3 | 506 | 2025-03-06 09:30:00 | no_show |
Output
| store_name | no_shows |
|---|---|
| Apple BKC | 2 |
| Apple Saket | 0 |
| Apple Regent Street | 1 |
| Apple Ginza | 0 |
How to solve Apple: Genius Bar No-Shows at Every Store
The answer has one row per store, including stores with nothing to count, so the query starts from Store and LEFT JOINs the appointments. The subtle part is where the status = 'no_show' test goes. In the ON clause it decides which appointments attach to a store; a store with none still survives with NULLs on the appointment side. In WHERE it would run after the join and drop exactly those NULL rows, so stores with zero no-shows would vanish.
After grouping by store, COUNT(a.appointment_id) counts only the attached appointments — COUNT(*) would count the single NULL row of an unmatched store as 1. Group by store_id as well as the name, so two stores that happened to share a name would stay apart.
A correlated subquery per store gives the same numbers and reads naturally; conditional SUM(CASE …) over a plain LEFT JOIN works too, with COALESCE turning the NULL sum of an empty store into 0. All three are one pass over the appointments with an index on store_id.
Reference solution (MySQL)
SELECT s.store_name, COUNT(a.appointment_id) AS no_shows
FROM Store s
LEFT JOIN GeniusAppointment a ON a.store_id = s.store_id AND a.status = 'no_show'
GROUP BY s.store_id, s.store_nameAnother way
SELECT s.store_name, (SELECT COUNT(*) FROM GeniusAppointment a WHERE a.store_id = s.store_id AND a.status = 'no_show') AS no_shows FROM Store sAnother way
SELECT s.store_name, COALESCE(SUM(CASE WHEN a.status = 'no_show' THEN 1 ELSE 0 END), 0) AS no_shows FROM Store s LEFT JOIN GeniusAppointment a ON a.store_id = s.store_id GROUP BY s.store_id, s.store_name← Amazon: Packing Station Backlog Carried Hour to Hour · Apple: iCloud Accounts Using More Than Their Plan →