Apple: AppleCare Claims Filed in a Device's First 30 Days
The AppleCare quality team treats a claim filed soon after purchase as a possible out-of-box failure and reviews those claims every week.
- Difficulty: Easy
- Topics: Dates, Joins
- Dialect: MySQL
- Problem: #601
Problem statement
The AppleCare quality team treats a claim filed soon after purchase as a possible out-of-box failure and reviews those claims every week.
Return every claim filed within 30 days of the device's purchase — that is, 0 to 30 days after the purchase date, both ends included — with the columns claim_id, model and days_after_purchase. 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: DeviceSale
| Column | Type |
|---|---|
| serial_number | varchar |
| model | varchar |
| purchase_date | date |
Primary key: serial_number.
One row per device sold.
Table: AppleCareClaim
| Column | Type |
|---|---|
| claim_id | int |
| serial_number | varchar |
| claim_date | date |
| issue | enum(display, battery, accidental_damage, audio, other) |
Primary key: claim_id.
serial_number always names a sold device, and a claim is never dated before its purchase.
Examples
Example 1
DeviceSale
| serial_number | model | purchase_date |
|---|---|---|
| DX7F2KQ91LMA | iPhone 16 | 2025-01-05 |
| F4PRT8XH2NQC | MacBook Air | 2025-01-10 |
| G9WZ3K7PL0RD | iPad Air | 2024-12-20 |
| H2KM8TQ4VX6E | iPhone 16 Pro | 2025-02-01 |
AppleCareClaim
| claim_id | serial_number | claim_date | issue |
|---|---|---|---|
| 1 | DX7F2KQ91LMA | 2025-02-04 | display |
| 2 | DX7F2KQ91LMA | 2025-02-05 | battery |
| 3 | F4PRT8XH2NQC | 2025-01-10 | audio |
| 4 | G9WZ3K7PL0RD | 2025-03-01 | accidental_damage |
| 5 | H2KM8TQ4VX6E | 2025-02-14 | other |
Output
| claim_id | model | days_after_purchase |
|---|---|---|
| 1 | iPhone 16 | 30 |
| 3 | MacBook Air | 0 |
| 5 | iPhone 16 Pro | 13 |
How to solve Apple: AppleCare Claims Filed in a Device's First 30 Days
A claim only knows its serial number, so the first step is to join it to DeviceSale to fetch the model and the purchase date. Then DATEDIFF(claim_date, purchase_date) is the number of days the device had been owned when the claim came in: 0 for a claim on the day of purchase, 30 for a claim exactly thirty days later.
The window is inclusive at both ends, so the filter is <= 30 (claims are never before purchase, so the lower bound is automatic). A device can appear more than once — every qualifying claim is its own row. The boundary is the usual trap: a claim 31 days after purchase is outside, and a < 30 filter would wrongly drop the claim on day 30.
Equivalent forms compare the claim date with DATE_ADD(purchase_date, INTERVAL 30 DAY), which can use an index on claim_date, or compute the difference with TIMESTAMPDIFF(DAY, …). Each is a single join on the serial number's primary key.
Reference solution (MySQL)
SELECT c.claim_id, d.model, DATEDIFF(c.claim_date, d.purchase_date) AS days_after_purchase
FROM AppleCareClaim c
JOIN DeviceSale d ON d.serial_number = c.serial_number
WHERE DATEDIFF(c.claim_date, d.purchase_date) <= 30Another way
SELECT c.claim_id, d.model, TIMESTAMPDIFF(DAY, d.purchase_date, c.claim_date) AS days_after_purchase FROM AppleCareClaim c JOIN DeviceSale d ON d.serial_number = c.serial_number WHERE c.claim_date <= DATE_ADD(d.purchase_date, INTERVAL 30 DAY)Another way
SELECT claim_id, model, days_after_purchase FROM (SELECT c.claim_id, d.model, DATEDIFF(c.claim_date, d.purchase_date) AS days_after_purchase FROM AppleCareClaim c, DeviceSale d WHERE d.serial_number = c.serial_number) t WHERE days_after_purchase BETWEEN 0 AND 30← Apple: Developer Teams With No Approved App Yet · Apple: Trade-In Credit Paid by Device Family →