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
| Column | Type |
|---|---|
| error_id | int |
| client | varchar |
| error_code | varchar |
| logged_at | datetime |
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_id | client | error_code | logged_at |
|---|---|---|---|
| 1 | android/14.2.1/SM-S918B | PL-1002 | 2025-04-12 19:31:05 |
| 2 | android/14.1.0/RMX3741 | PL-1002 | 2025-04-12 19:32:40 |
| 3 | ios/14.2.0/iPhone15,2 | DRM-301 | 2025-04-12 19:35:12 |
| 4 | tizen/7.0.3/UA55CU8000 | PL-2210 | 2025-04-12 19:40:00 |
| 5 | NULL | PL-1002 | 2025-04-12 19:41:18 |
| 6 | android/14.2.1/CPH2581 | NET-504 | 2025-04-12 19:44:51 |
| 7 | ios/14.1.9/iPad13,4 | DRM-301 | 2025-04-12 19:50:02 |
| 8 | tizen/7.0.1/UA43T5400 | PL-1002 | 2025-04-12 19:52:30 |
Output
| platform | errors | error_codes |
|---|---|---|
| android | 3 | 2 |
| ios | 2 | 1 |
| tizen | 2 | 2 |
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, platformAnother 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 →