Apple: Genius Bar Repeat Visits Within 14 Days

Medium
609.

Apple: Genius Bar Repeat Visits Within 14 Days

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

ColumnTypeValues
appointment_idint
customer_idint
device_serialvarchar
visited_ondate
statusenumcompleted, no_show

Primary key: appointment_id.

One row per Genius Bar appointment for one device.

Example

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

GeniusVisit

appointment_idcustomer_iddevice_serialvisited_onstatus
17F4PRT8XH2NQC2025-05-01completed
27F4PRT8XH2NQC2025-05-15completed
37F4PRT8XH2NQC2025-06-10completed
47DX7F2KQ91LMA2025-05-20completed
59G9WZ3K7PL0RD2025-05-02completed
69G9WZ3K7PL0RD2025-05-05no_show
79G9WZ3K7PL0RD2025-05-17completed

Output

appointment_idcustomer_iddevice_serialdays_since_previous
27F4PRT8XH2NQC14
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 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

ColumnType
appointment_idint
customer_idint
device_serialvarchar
visited_ondate
statusenum(completed, no_show)

Primary key: appointment_id.

One row per Genius Bar appointment for one device.

Examples

Example 1

GeniusVisit

appointment_idcustomer_iddevice_serialvisited_onstatus
17F4PRT8XH2NQC2025-05-01completed
27F4PRT8XH2NQC2025-05-15completed
37F4PRT8XH2NQC2025-06-10completed
47DX7F2KQ91LMA2025-05-20completed
59G9WZ3K7PL0RD2025-05-02completed
69G9WZ3K7PL0RD2025-05-05no_show
79G9WZ3K7PL0RD2025-05-17completed

Output

appointment_idcustomer_iddevice_serialdays_since_previous
27F4PRT8XH2NQC14

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) <= 14

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