Apple: AppleCare Serial Numbers That Fail the Format Check
Customers type their device's serial number when they register for AppleCare, and support wants to catch mistyped entries before a claim is filed.
- Difficulty: Medium
- Topics: Strings, Conditional Logic
- Dialect: MySQL
- Problem: #613
Problem statement
Customers type their device's serial number when they register for AppleCare, and support wants to catch mistyped entries before a claim is filed. In this exercise a valid serial, after trimming leading and trailing spaces, is exactly 12 characters, uses only capital letters and digits, and never contains the letters O or I (they are too easily confused with 0 and 1). The form already upper-cases what is typed.
Return every registration whose entry is invalid, with registration_id, serial_entered and reason, where reason is the first rule that fails, checked in this order: 'wrong length', 'invalid character', 'ambiguous letter'. 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: CareRegistration
| Column | Type |
|---|---|
| registration_id | int |
| customer_email | varchar |
| serial_entered | varchar |
Primary key: registration_id.
One row per AppleCare registration, with the serial number exactly as it was submitted (upper-case, possibly with stray spaces or dashes).
Examples
Example 1
CareRegistration
| registration_id | customer_email | serial_entered |
|---|---|---|
| 1 | aarav@icloud.com | F4PRT8XH2NQC |
| 2 | diya@icloud.com | DX7F2KQ91LMA |
| 3 | kabir@icloud.com | G9WZ3K7PL0R |
| 4 | meera@icloud.com | H2KM-8TQ4VX6 |
| 5 | rohan@icloud.com | C02XL0GHJGH5 |
| 6 | zara@icloud.com | FVFG7K3O2M1Q |
| 7 | neha@icloud.com | MXK3 9TQ4VX6 |
Output
| registration_id | serial_entered | reason |
|---|---|---|
| 3 | G9WZ3K7PL0R | wrong length |
| 4 | H2KM-8TQ4VX6 | invalid character |
| 6 | FVFG7K3O2M1Q | ambiguous letter |
| 7 | MXK3 9TQ4VX6 | invalid character |
How to solve Apple: AppleCare Serial Numbers That Fail the Format Check
Every rule applies to the trimmed entry, so wrap the column in TRIM everywhere: ' DX7F2KQ91LMA ' is a perfectly good serial once its padding is gone. Then the three rules become three tests, and because the statement asks for the first failing rule, they belong in one CASE in the stated order — a CASE returns the first branch that is true.
Length is CHAR_LENGTH(TRIM(…)) <> 12. The character rule is a regular expression with a negated class, [^A-Z0-9], which matches a dash or an inner space. The ambiguity rule looks for an O or an I. A valid entry passes all three, the CASE yields NULL, and the outer WHERE reason IS NOT NULL drops it. An entry that is both too short and contains a dash reports wrong length, because that rule comes first.
The alternative nests IFs, checks the character set with an anchored pattern, and finds the ambiguous letters with INSTR. Both are one scan; regular expressions cost more per row than plain comparisons, but registrations are checked a handful at a time.
Reference solution (MySQL)
SELECT registration_id, serial_entered, reason
FROM (
SELECT registration_id, serial_entered,
CASE
WHEN CHAR_LENGTH(TRIM(serial_entered)) <> 12 THEN 'wrong length'
WHEN TRIM(serial_entered) REGEXP '[^A-Z0-9]' THEN 'invalid character'
WHEN TRIM(serial_entered) REGEXP '[OI]' THEN 'ambiguous letter'
END AS reason
FROM CareRegistration
) r
WHERE reason IS NOT NULLAnother way
SELECT registration_id, serial_entered,
IF(LENGTH(TRIM(serial_entered)) <> 12, 'wrong length',
IF(NOT TRIM(serial_entered) REGEXP '^[0-9A-Z]+$', 'invalid character', 'ambiguous letter')) AS reason
FROM CareRegistration
WHERE LENGTH(TRIM(serial_entered)) <> 12
OR NOT TRIM(serial_entered) REGEXP '^[0-9A-Z]{12}$'
OR INSTR(serial_entered, 'O') > 0 OR INSTR(serial_entered, 'I') > 0← Apple: Each Product Line's Share of Store Revenue · Apple: Running iCloud+ Subscriber Count by Day →