Apple: Genius Bar Repeat Visits Within 14 Days — SQL Problem
A repair that brings the customer back soon for the same device suggests the first fix did not hold. The retail service team tracks these repeat visits.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #609
Problem statement
A repair that brings the customer back soon for the same device suggests the first fix did not hold. The retail service team tracks these repeat visits.
Consider only completed appointments. For each completed appointment, its previous visit is the same customer's latest earlier completed appointment for the same device_serial (earlier by visited_on, then by appointment_id). Return every completed appointment whose previous visit was at most 14 days before it, with the columns appointment_id, customer_id, device_serial and days_since_previous. 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: GeniusVisit
| Column | Type |
|---|---|
| appointment_id | int |
| customer_id | int |
| device_serial | varchar |
| visited_on | date |
| status | enum(completed, no_show) |
Primary key: appointment_id.
One row per Genius Bar appointment for one device.
Examples
Example 1
GeniusVisit
| appointment_id | customer_id | device_serial | visited_on | status |
|---|---|---|---|---|
| 1 | 7 | F4PRT8XH2NQC | 2025-05-01 | completed |
| 2 | 7 | F4PRT8XH2NQC | 2025-05-15 | completed |
| 3 | 7 | F4PRT8XH2NQC | 2025-06-10 | completed |
| 4 | 7 | DX7F2KQ91LMA | 2025-05-20 | completed |
| 5 | 9 | G9WZ3K7PL0RD | 2025-05-02 | completed |
| 6 | 9 | G9WZ3K7PL0RD | 2025-05-05 | no_show |
| 7 | 9 | G9WZ3K7PL0RD | 2025-05-17 | completed |
Output
| appointment_id | customer_id | device_serial | days_since_previous |
|---|---|---|---|
| 2 | 7 | F4PRT8XH2NQC | 14 |
How to solve Apple: Genius Bar Repeat Visits Within 14 Days
"The previous visit" is a question about row order within a group, which is what LAG answers. Filter to completed appointments first (a no-show neither counts as a visit nor as the previous one), then partition by customer_id, device_serial — the same customer's other devices are separate histories — and order by visited_on, appointment_id so two visits on one day still have a defined order.
LAG(visited_on) is NULL for the first visit of each history, and DATEDIFF of NULL is NULL, so those rows fall out of the <= 14 test; the explicit IS NOT NULL just says so. Fourteen days counts as a repeat, fifteen does not, and a same-day second visit is a repeat with 0 days.
Without window functions, a correlated subquery takes the MAX earlier date among completed visits of the same pair; the tie rule for same-day visits has to be written out by hand there. The window version sorts each history once.
Reference solution (MySQL)
SELECT appointment_id, customer_id, device_serial, DATEDIFF(visited_on, prev_on) AS days_since_previous
FROM (
SELECT appointment_id, customer_id, device_serial, visited_on,
LAG(visited_on) OVER (PARTITION BY customer_id, device_serial ORDER BY visited_on, appointment_id) AS prev_on
FROM GeniusVisit
WHERE status = 'completed'
) v
WHERE prev_on IS NOT NULL AND DATEDIFF(visited_on, prev_on) <= 14Another way
SELECT appointment_id, customer_id, device_serial, DATEDIFF(visited_on, prev_on) AS days_since_previous
FROM (
SELECT g.appointment_id, g.customer_id, g.device_serial, g.visited_on,
(SELECT MAX(p.visited_on) FROM GeniusVisit p
WHERE p.status = 'completed' AND p.customer_id = g.customer_id AND p.device_serial = g.device_serial
AND (p.visited_on < g.visited_on OR (p.visited_on = g.visited_on AND p.appointment_id < g.appointment_id))) AS prev_on
FROM GeniusVisit g
WHERE g.status = 'completed'
) v
WHERE DATEDIFF(visited_on, prev_on) <= 14← Apple: Developer Proceeds After Commission in March 2025 · Apple: Apple Pay Monthly Volume Change by Country →