Apple: Daily Share of Checked-In Devices on iOS 18
The iOS release team tracks adoption of the newest major version from the daily device check-ins.
- Difficulty: Medium
- Topics: Strings, Aggregation, Dates
- Dialect: MySQL
- Problem: #606
Problem statement
The iOS release team tracks adoption of the newest major version from the daily device check-ins.
For each checkin_date, return devices (devices that checked in that day) and pct_on_ios18, the percentage of them whose ios_version has major version 18 — the part before the first dot — rounded to 1 decimal place. Note that 1.8 style strings never occur, but 18.0, 18.1.1 and 17.6.1 do. Order the rows by checkin_date.
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: DeviceCheckin
| Column | Type |
|---|---|
| device_id | int |
| checkin_date | date |
| ios_version | varchar |
Primary key: device_id, checkin_date.
At most one check-in per device per day; ios_version is 'major.minor' or 'major.minor.patch'.
Examples
Example 1
DeviceCheckin
| device_id | checkin_date | ios_version |
|---|---|---|
| 1 | 2024-09-20 | 17.6.1 |
| 2 | 2024-09-20 | 18.0 |
| 3 | 2024-09-20 | 17.5 |
| 1 | 2024-09-21 | 18.0 |
| 2 | 2024-09-21 | 18.0 |
| 3 | 2024-09-21 | 17.5 |
| 4 | 2024-09-21 | 16.7.10 |
| 4 | 2024-09-23 | 18.0.1 |
Output
| checkin_date | devices | pct_on_ios18 |
|---|---|---|
| 2024-09-20 | 3 | 33.3 |
| 2024-09-21 | 4 | 50 |
| 2024-09-23 | 1 | 100 |
How to solve Apple: Daily Share of Checked-In Devices on iOS 18
Each day is one group, so the query is a GROUP BY checkin_date with two counts: all check-ins, and the check-ins on iOS 18. The second is a conditional aggregate — SUM(CASE WHEN … THEN 1 ELSE 0 END) — and the condition is a string test on the version.
The major version is the text before the first dot, which SUBSTRING_INDEX(ios_version, '.', 1) extracts. Comparing it to '18' is exact; a looser test such as LIKE '%18%' would count 17.1.8. LIKE '18.%' is also correct because every version has a dot after its major number.
The percentage is 100 * on_18 / devices rounded to one decimal. Because the table allows one check-in per device per day, COUNT(*) is the number of devices. The query reads the table once; the alternatives only swap the string function (LEFT + LOCATE, or LIKE) and the counting form (COUNT(CASE …), IF).
Reference solution (MySQL)
SELECT checkin_date,
COUNT(*) AS devices,
ROUND(100 * SUM(CASE WHEN SUBSTRING_INDEX(ios_version, '.', 1) = '18' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_on_ios18
FROM DeviceCheckin
GROUP BY checkin_date
ORDER BY checkin_dateAnother way
SELECT checkin_date, COUNT(device_id) AS devices, ROUND(100 * COUNT(CASE WHEN ios_version LIKE '18.%' THEN 1 END) / COUNT(device_id), 1) AS pct_on_ios18 FROM DeviceCheckin GROUP BY checkin_date ORDER BY checkin_dateAnother way
SELECT checkin_date, COUNT(*) AS devices, ROUND(100 * SUM(IF(LEFT(ios_version, LOCATE('.', ios_version) - 1) = '18', 1, 0)) / COUNT(*), 1) AS pct_on_ios18 FROM DeviceCheckin GROUP BY checkin_date ORDER BY checkin_date← Apple: Online Orders With Both an iPhone and AirPods · Apple: Category Leaders by Q4 Net App Revenue →