Apple: AppleCare Claims Filed in a Device's First 30 Days

Easy
601.

Apple: AppleCare Claims Filed in a Device's First 30 Days

EasyDatesJoins

MySQL

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

ColumnType
serial_numbervarchar
modelvarchar
purchase_datedate

Primary key: serial_number.

One row per device sold.

Table: AppleCareClaim

ColumnTypeValues
claim_idint
serial_numbervarchar
claim_datedate
issueenumdisplay, 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.

Example

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

DeviceSale

serial_numbermodelpurchase_date
DX7F2KQ91LMAiPhone 162025-01-05
F4PRT8XH2NQCMacBook Air2025-01-10
G9WZ3K7PL0RDiPad Air2024-12-20
H2KM8TQ4VX6EiPhone 16 Pro2025-02-01

AppleCareClaim

claim_idserial_numberclaim_dateissue
1DX7F2KQ91LMA2025-02-04display
2DX7F2KQ91LMA2025-02-05battery
3F4PRT8XH2NQC2025-01-10audio
4G9WZ3K7PL0RD2025-03-01accidental_damage
5H2KM8TQ4VX6E2025-02-14other

Output

claim_idmodeldays_after_purchase
1iPhone 1630
3MacBook Air0
5iPhone 16 Pro13
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: 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

ColumnType
serial_numbervarchar
modelvarchar
purchase_datedate

Primary key: serial_number.

One row per device sold.

Table: AppleCareClaim

ColumnType
claim_idint
serial_numbervarchar
claim_datedate
issueenum(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_numbermodelpurchase_date
DX7F2KQ91LMAiPhone 162025-01-05
F4PRT8XH2NQCMacBook Air2025-01-10
G9WZ3K7PL0RDiPad Air2024-12-20
H2KM8TQ4VX6EiPhone 16 Pro2025-02-01

AppleCareClaim

claim_idserial_numberclaim_dateissue
1DX7F2KQ91LMA2025-02-04display
2DX7F2KQ91LMA2025-02-05battery
3F4PRT8XH2NQC2025-01-10audio
4G9WZ3K7PL0RD2025-03-01accidental_damage
5H2KM8TQ4VX6E2025-02-14other

Output

claim_idmodeldays_after_purchase
1iPhone 1630
3MacBook Air0
5iPhone 16 Pro13

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

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