Apple: AppleCare Serial Numbers That Fail the Format Check

Medium
613.

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. 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

ColumnType
registration_idint
customer_emailvarchar
serial_enteredvarchar

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).

Example

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

CareRegistration

registration_idcustomer_emailserial_entered
1aarav@icloud.comF4PRT8XH2NQC
2diya@icloud.com DX7F2KQ91LMA
3kabir@icloud.comG9WZ3K7PL0R
4meera@icloud.comH2KM-8TQ4VX6
5rohan@icloud.comC02XL0GHJGH5
6zara@icloud.comFVFG7K3O2M1Q
7neha@icloud.comMXK3 9TQ4VX6

Output

registration_idserial_enteredreason
3G9WZ3K7PL0Rwrong length
4H2KM-8TQ4VX6invalid character
6FVFG7K3O2M1Qambiguous letter
7MXK3 9TQ4VX6invalid character
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 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

ColumnType
registration_idint
customer_emailvarchar
serial_enteredvarchar

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_idcustomer_emailserial_entered
1aarav@icloud.comF4PRT8XH2NQC
2diya@icloud.com DX7F2KQ91LMA
3kabir@icloud.comG9WZ3K7PL0R
4meera@icloud.comH2KM-8TQ4VX6
5rohan@icloud.comC02XL0GHJGH5
6zara@icloud.comFVFG7K3O2M1Q
7neha@icloud.comMXK3 9TQ4VX6

Output

registration_idserial_enteredreason
3G9WZ3K7PL0Rwrong length
4H2KM-8TQ4VX6invalid character
6FVFG7K3O2M1Qambiguous letter
7MXK3 9TQ4VX6invalid 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 NULL

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