Meta: Messenger Call Drops by Major App Version

Medium
763.

Meta: Messenger Call Drops by Major App Version

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

ColumnTypeValues
call_idint
user_idint
app_versionvarchar
platformenumandroid, ios
end_reasonenumhangup, dropped, declined

Primary key: call_id.

One row per call attempt; app_version is the caller's app version, always three dot-separated numbers.

Example

The rows must come back in the order shown.

CallLog

call_iduser_idapp_versionplatformend_reason
110452.0.1androidhangup
211452.1.0iosdropped
31298.3.2androiddropped
41398.3.2androiddeclined
514451.0.0ioshangup
61545.2.0androidhangup
710452.0.1androidhangup
816451.2.1androiddeclined

Output

major_versioncallsdropped
4510
9811
45110
45231
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.

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

ColumnType
call_idint
user_idint
app_versionvarchar
platformenum(android, ios)
end_reasonenum(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_iduser_idapp_versionplatformend_reason
110452.0.1androidhangup
211452.1.0iosdropped
31298.3.2androiddropped
41398.3.2androiddeclined
514451.0.0ioshangup
61545.2.0androidhangup
710452.0.1androidhangup
816451.2.1androiddeclined

Output

major_versioncallsdropped
4510
9811
45110
45231

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_version

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