Apple-style Company ChallengeEasyVerified answerSQLite live

Genius Bar Appointments Today

Which Genius Bar appointments are scheduled for January 11, 2024 — who are the customers, which stores, and what are their issues?

  • Joins
  • Filtering
  • Sorting

Challenge brief

Understand the request

Genius Bar Operations is preparing the day's service roster and needs all scheduled appointments for January 11, 2024.

List scheduled Genius Bar appointments on 2024-01-11 with customer name, store name, appointment time, and issue description.

Return

  • customer_name (full name)
  • store_name
  • appointment_date
  • issue_description

Constraints

  • Include scheduled appointments from 2024-01-11 00:00 up to but not including 2024-01-12 00:00
  • Show appointments chronologically and resolve ties by appointment ID

Data you will use

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

genius_bar_appointments

  • appointment_idINTEGER
  • customer_idINTEGER
  • store_idINTEGER
  • appointment_dateDATETIME
  • issue_descriptionTEXT
  • statusVARCHAR(20)

customers

  • customer_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)

stores

  • store_idINTEGER
  • store_nameVARCHAR(100)

Hints, when you need them

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

Hint 1

Appointment data is in genius_bar_appointments. Customer names are in customers, store names in stores. You need two JOINs: one to each reference table.

Hint 2

INNER JOIN genius_bar_appointments to customers on customer_id, and to stores on store_id. WHERE DATE(g.appointment_date) = '2024-01-11' AND g.status = 'scheduled'.

Hint 3

Build question 6 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 c.first_name || ' ' || c.last_name AS customer_name, s.store_name, g.appointment_date, g.issue_description FROM genius_bar_appointments g INNER JOIN customers c ON g.customer_id = c.customer_id INNER JOIN stores s ON g.store_id = s.store_id WHERE g.appointment_date >= '2024-01-11 00:00:00' AND g.appointment_date < '2024-01-12 00:00:00' AND g.status = 'scheduled' ORDER BY g.appointment_date, g.appointment_id;

Why this works

Use a half-open timestamp range so an index can support the appointment filter and midnight on January 12 cannot leak into the day. Join customers and stores only after establishing the scheduled appointment population.

Success check

4 scheduled appointments on Jan 11 across Apple Fifth Avenue and Regent Street

Expected result

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

customer_namestore_nameappointment_dateissue_description
Alice JohnsonApple Fifth Avenue2024-01-11 10:00:00iPhone battery draining quickly
Bob SmithApple Union Square2024-01-11 11:30:00MacBook screen flickering
Carol WhiteApple Fifth Avenue2024-01-11 14:00:00iPad not charging
David BrownApple Regent Street2024-01-11 15:30:00Apple Watch not syncing

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.