Apple: Genius Bar No-Shows at Every Store

Easy
598.

Apple: Genius Bar No-Shows at Every Store

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

ColumnType
store_idint
store_namevarchar
cityvarchar

Primary key: store_id.

One row per retail store.

Table: GeniusAppointment

ColumnTypeValues
appointment_idint
store_idint
customer_idint
slot_atdatetime
statusenumcompleted, no_show, cancelled

Primary key: appointment_id.

One row per booked Genius Bar slot; store_id always names a store.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Store

store_idstore_namecity
1Apple BKCMumbai
2Apple SaketDelhi
3Apple Regent StreetLondon
4Apple GinzaTokyo

GeniusAppointment

appointment_idstore_idcustomer_idslot_atstatus
10115012025-03-03 10:00:00no_show
10215022025-03-03 10:30:00completed
10315032025-03-04 11:00:00no_show
10425042025-03-03 12:00:00cancelled
10525052025-03-05 16:00:00completed
10635062025-03-06 09:30:00no_show

Output

store_nameno_shows
Apple BKC2
Apple Saket0
Apple Regent Street1
Apple Ginza0
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
store_idint
store_namevarchar
cityvarchar

Primary key: store_id.

One row per retail store.

Table: GeniusAppointment

ColumnType
appointment_idint
store_idint
customer_idint
slot_atdatetime
statusenum(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_idstore_namecity
1Apple BKCMumbai
2Apple SaketDelhi
3Apple Regent StreetLondon
4Apple GinzaTokyo

GeniusAppointment

appointment_idstore_idcustomer_idslot_atstatus
10115012025-03-03 10:00:00no_show
10215022025-03-03 10:30:00completed
10315032025-03-04 11:00:00no_show
10425042025-03-03 12:00:00cancelled
10525052025-03-05 16:00:00completed
10635062025-03-06 09:30:00no_show

Output

store_nameno_shows
Apple BKC2
Apple Saket0
Apple Regent Street1
Apple Ginza0

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_name

Another 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 s

Another 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 →