Meta: Messenger Call Drops by Major App Version
After a release, the Messenger calling team compares dropped calls across app versions.
- Difficulty: Medium
- Topics: Strings, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #763
Problem statement
After a release, the Messenger calling team compares dropped calls across app versions. Versions look like 452.1.0; the major version is the number before the first dot.
Ignoring declined calls (they never connected), return one row per major version with the columns major_version (as a number), calls (connected calls on that major version) and dropped (those that ended as dropped). Order the rows by major_version numerically, smallest first.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: CallLog
| Column | Type |
|---|---|
| call_id | int |
| user_id | int |
| app_version | varchar |
| platform | enum(android, ios) |
| end_reason | enum(hangup, dropped, declined) |
Primary key: call_id.
One row per call attempt; app_version is the caller's app version, always three dot-separated numbers.
Examples
Example 1
CallLog
| call_id | user_id | app_version | platform | end_reason |
|---|---|---|---|---|
| 1 | 10 | 452.0.1 | android | hangup |
| 2 | 11 | 452.1.0 | ios | dropped |
| 3 | 12 | 98.3.2 | android | dropped |
| 4 | 13 | 98.3.2 | android | declined |
| 5 | 14 | 451.0.0 | ios | hangup |
| 6 | 15 | 45.2.0 | android | hangup |
| 7 | 10 | 452.0.1 | android | hangup |
| 8 | 16 | 451.2.1 | android | declined |
Output
| major_version | calls | dropped |
|---|---|---|
| 45 | 1 | 0 |
| 98 | 1 | 1 |
| 451 | 1 | 0 |
| 452 | 3 | 1 |
How to solve Meta: Messenger Call Drops by Major App Version
The version string has to be cut and converted before it can be grouped and sorted. SUBSTRING_INDEX(app_version, '.', 1) keeps the part before the first dot; CAST(… AS SIGNED) turns it into a number. The conversion is not cosmetic: as text, '45', '451', '452', '98' is the sorted order, while numerically 98 comes before 451. Grouping by the text would also work, but ordering must be numeric, so it is simplest to group by the number too.
Declined calls never connected, so a WHERE removes them before grouping; within each major version COUNT(*) gives the connected calls and a conditional SUM(CASE …) the dropped ones. A prefix test such as LIKE '45%' would wrongly lump 45, 450, 451 and 452 together — which is why the string is cut at the dot rather than matched.
The alternative finds the dot with LOCATE and takes the LEFT part, in a derived table, and counts drops with COUNT(CASE …). Both are one scan plus a sort of the groups.
Reference solution (MySQL)
SELECT CAST(SUBSTRING_INDEX(app_version, '.', 1) AS SIGNED) AS major_version,
COUNT(*) AS calls,
SUM(CASE WHEN end_reason = 'dropped' THEN 1 ELSE 0 END) AS dropped
FROM CallLog
WHERE end_reason <> 'declined'
GROUP BY CAST(SUBSTRING_INDEX(app_version, '.', 1) AS SIGNED)
ORDER BY major_versionAnother way
SELECT major_version, COUNT(*) AS calls, COUNT(CASE WHEN end_reason = 'dropped' THEN 1 END) AS dropped
FROM (SELECT CAST(LEFT(app_version, LOCATE('.', app_version) - 1) AS SIGNED) AS major_version, end_reason
FROM CallLog WHERE end_reason IN ('hangup', 'dropped')) c
GROUP BY major_version
ORDER BY major_version← Meta: Instagram Follow-Backs Within a Week · Meta: Running Quest Store Revenue per Developer →