Apple: Daily Share of Checked-In Devices on iOS 18

Medium
606.

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.

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

ColumnType
device_idint
checkin_datedate
ios_versionvarchar

Primary key (together): device_id, checkin_date.

At most one check-in per device per day; ios_version is 'major.minor' or 'major.minor.patch'.

Example

The rows must come back in the order shown.

DeviceCheckin

device_idcheckin_dateios_version
12024-09-2017.6.1
22024-09-2018.0
32024-09-2017.5
12024-09-2118.0
22024-09-2118.0
32024-09-2117.5
42024-09-2116.7.10
42024-09-2318.0.1

Output

checkin_datedevicespct_on_ios18
2024-09-20333.3
2024-09-21450
2024-09-231100
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: 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

ColumnType
device_idint
checkin_datedate
ios_versionvarchar

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_idcheckin_dateios_version
12024-09-2017.6.1
22024-09-2018.0
32024-09-2017.5
12024-09-2118.0
22024-09-2118.0
32024-09-2117.5
42024-09-2116.7.10
42024-09-2318.0.1

Output

checkin_datedevicespct_on_ios18
2024-09-20333.3
2024-09-21450
2024-09-231100

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_date

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

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