Hotstar: Playback Errors by App Platform

Easy
705.

Hotstar: Playback Errors by App Platform

Every playback error the Hotstar apps report carries a client string of the form platform/app_version/device_model, for example android/14.2.1/SM-S918B. The player team wants errors grouped by platform — the part before the first /. Errors whose client string is NULL (the report was truncated) are ignored.

Return platform, errors (the number of errors) and error_codes (the number of distinct error_code values) for every platform. Order by errors descending, then platform ascending.

A practice scenario set at Hotstar: the tables and data are invented, and this is not a question from, or endorsed by, Hotstar.

Tables

Table: PlaybackError

ColumnType
error_idint
clientvarchar
error_codevarchar
logged_atdatetime

Primary key: error_id.

One row per error report. Platform names are always lower case; client is NULL when the report was cut off.

Example

The rows must come back in the order shown.

PlaybackError

error_idclienterror_codelogged_at
1android/14.2.1/SM-S918BPL-10022025-04-12 19:31:05
2android/14.1.0/RMX3741PL-10022025-04-12 19:32:40
3ios/14.2.0/iPhone15,2DRM-3012025-04-12 19:35:12
4tizen/7.0.3/UA55CU8000PL-22102025-04-12 19:40:00
5NULLPL-10022025-04-12 19:41:18
6android/14.2.1/CPH2581NET-5042025-04-12 19:44:51
7ios/14.1.9/iPad13,4DRM-3012025-04-12 19:50:02
8tizen/7.0.1/UA43T5400PL-10022025-04-12 19:52:30

Output

platformerrorserror_codes
android32
ios21
tizen22
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.

Hotstar: Playback Errors by App Platform — SQL Problem

Every playback error the Hotstar apps report carries a client string of the form platform/app_version/device_model, for example android/14.2.1/SM-S918B.

  • Difficulty: Easy
  • Topics: Strings, Aggregation
  • Dialect: MySQL
  • Problem: #705

Problem statement

Every playback error the Hotstar apps report carries a client string of the form platform/app_version/device_model, for example android/14.2.1/SM-S918B. The player team wants errors grouped by platform — the part before the first /. Errors whose client string is NULL (the report was truncated) are ignored.

Return platform, errors (the number of errors) and error_codes (the number of distinct error_code values) for every platform. Order by errors descending, then platform ascending.

A practice scenario set at Hotstar: the tables and data are invented, and this is not a question from, or endorsed by, Hotstar.

Tables

Table: PlaybackError

ColumnType
error_idint
clientvarchar
error_codevarchar
logged_atdatetime

Primary key: error_id.

One row per error report. Platform names are always lower case; client is NULL when the report was cut off.

Examples

Example 1

PlaybackError

error_idclienterror_codelogged_at
1android/14.2.1/SM-S918BPL-10022025-04-12 19:31:05
2android/14.1.0/RMX3741PL-10022025-04-12 19:32:40
3ios/14.2.0/iPhone15,2DRM-3012025-04-12 19:35:12
4tizen/7.0.3/UA55CU8000PL-22102025-04-12 19:40:00
5NULLPL-10022025-04-12 19:41:18
6android/14.2.1/CPH2581NET-5042025-04-12 19:44:51
7ios/14.1.9/iPad13,4DRM-3012025-04-12 19:50:02
8tizen/7.0.1/UA43T5400PL-10022025-04-12 19:52:30

Output

platformerrorserror_codes
android32
ios21
tizen22

How to solve Hotstar: Playback Errors by App Platform

The grouping key is not a column but a piece of one: the platform is everything before the first slash. SUBSTRING_INDEX(client, '/', 1) returns the text before the first occurrence of the delimiter, so android/14.2.1/SM-S918B becomes android. Grouping by that expression (through its alias) puts every Android build and device together.

Two counts per group: COUNT(*) for the errors and COUNT(DISTINCT error_code) for how many different failure types the platform showed. Rows with a NULL client would produce a NULL platform and a group of their own, so they are filtered out in WHERE first.

The alternative finds the slash with LOCATE and keeps the characters to its left with LEFT(client, position - 1), computing it in a derived table so the outer query groups by a plain column. The statement fixes the order — most errors first, ties by platform name. One scan of the table plus a small sort.

Reference solution (MySQL)

SELECT SUBSTRING_INDEX(client, '/', 1) AS platform,
       COUNT(*) AS errors,
       COUNT(DISTINCT error_code) AS error_codes
FROM PlaybackError
WHERE client IS NOT NULL
GROUP BY platform
ORDER BY errors DESC, platform

Another way

SELECT platform, COUNT(*) AS errors, COUNT(DISTINCT error_code) AS error_codes
FROM (SELECT LEFT(client, LOCATE('/', client) - 1) AS platform, error_code FROM PlaybackError WHERE client IS NOT NULL) e
GROUP BY platform
ORDER BY errors DESC, platform

← Hotstar: Matches That Crossed Two Crore Concurrent Viewers · Hotstar: Advertisers Owed Make-Good Impressions →