Apple-style Company ChallengeHardVerified answerSQLite live

Genius Bar Appointment Summary by Store

For each Apple store, how many Genius Bar appointments have been held in total, and how many were completed vs scheduled?

  • Joins
  • Aggregation
  • CASE expressions
  • Sorting

Challenge brief

Understand the request

Genius Bar Operations is reviewing service workload distribution across stores to plan staffing adjustments.

Show appointment counts per store using LEFT JOIN and conditional aggregation to split by status.

Return

  • store_name
  • total_appointments
  • completed
  • scheduled

Constraints

  • Include every store, including stores with no appointments
  • Count completed and scheduled appointments independently
  • Show the largest appointment totals first and resolve ties by store name

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

stores

  • store_idINTEGER
  • store_nameVARCHAR(100)

genius_bar_appointments

  • appointment_idINTEGER
  • store_idINTEGER
  • statusVARCHAR(20)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Start FROM stores (left side) and LEFT JOIN genius_bar_appointments. This keeps all stores even those with zero appointments. Use SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) to count by status.

Hint 2

LEFT JOIN stores to genius_bar_appointments on store_id. GROUP BY store. COUNT(g.appointment_id) — this counts NULL as 0 for stores with no appointments. SUM(CASE WHEN ...) for each status.

Hint 3

Build question 18 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT s.store_name, COUNT(g.appointment_id) AS total_appointments, SUM(CASE WHEN g.status = 'completed' THEN 1 ELSE 0 END) AS completed, SUM(CASE WHEN g.status = 'scheduled' THEN 1 ELSE 0 END) AS scheduled FROM stores s LEFT JOIN genius_bar_appointments g ON s.store_id = g.store_id GROUP BY s.store_id, s.store_name ORDER BY total_appointments DESC, s.store_name

Why this works

COUNT(g.appointment_id) counts non-NULL appointment IDs — for stores with no appointments the LEFT JOIN produces NULL, which COUNT correctly treats as 0. SUM(CASE WHEN ...) is the conditional pivot pattern: it counts only rows matching each status.

Success check

6 stores — Apple Fifth Avenue leads (4 total: 2 completed, 2 scheduled). Champs, Ginza, Beverly Hills show zero appointments.

Expected result

Use this output to verify values, aliases, ordering, and row count.

store_nametotal_appointmentscompletedscheduled
Apple Fifth Avenue422
Apple Union Square312
Apple Regent Street101
Apple Beverly Hills000
Apple Champs-Élysées000
Apple Ginza000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.