Hotstar: Commentary Languages Live on Each Match
Hotstar streams a match with several commentary feeds, one per language.
- Difficulty: Easy
- Topics: Aggregation, Strings, Joins
- Dialect: MySQL
- Problem: #699
Problem statement
Hotstar streams a match with several commentary feeds, one per language. The programming team wants a one-line summary per match of the languages that actually went on air; a feed that was scheduled but never went live has went_live = 0, and a feed that crashed and was restarted appears twice.
For every match with at least one feed that went live, return title, languages — the distinct live languages in alphabetical order, separated by a comma and a space — and language_count, the number of distinct live languages. Matches with no live feed are left out. Return the rows in any order.
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: LiveMatch
| Column | Type |
|---|---|
| match_id | int |
| title | varchar |
Primary key: match_id.
One row per streamed match.
Table: CommentaryFeed
| Column | Type |
|---|---|
| feed_id | int |
| match_id | int |
| language | varchar |
| went_live | bool |
Primary key: feed_id.
One row per commentary feed started for a match. A restarted feed is a new row with the same language.
Examples
Example 1
LiveMatch
| match_id | title |
|---|---|
| 1 | Mumbai Mariners vs Chennai Chargers |
| 2 | Delhi Dynamos vs Kolkata Knights |
| 3 | Bengaluru Blasters vs Hyderabad Hawks |
CommentaryFeed
| feed_id | match_id | language | went_live |
|---|---|---|---|
| 1 | 1 | Hindi | 1 |
| 2 | 1 | English | 1 |
| 3 | 1 | Tamil | 1 |
| 4 | 1 | Hindi | 1 |
| 5 | 2 | English | 1 |
| 6 | 2 | Bengali | 0 |
| 7 | 2 | Kannada | 1 |
| 8 | 3 | Telugu | 0 |
Output
| title | languages | language_count |
|---|---|---|
| Mumbai Mariners vs Chennai Chargers | English, Hindi, Tamil | 3 |
| Delhi Dynamos vs Kolkata Knights | English, Kannada | 2 |
How to solve Hotstar: Commentary Languages Live on Each Match
Each output row summarises one match, so the query groups the live feeds by match. The filter went_live = 1 runs in WHERE, before grouping, so a scheduled-but-dead feed never reaches a group; and because the join is an inner join, a match whose feeds all stayed dark forms no group at all and is left out as asked.
The trap is the restarted feed: Hindi appears twice for the first match. GROUP_CONCAT(DISTINCT f.language ORDER BY f.language SEPARATOR ', ') lists each language once, alphabetically, with the separator the statement asks for, and COUNT(DISTINCT f.language) counts each once too — a plain COUNT(*) would say 4.
The alternative removes the duplicates first, in a derived table of distinct (match_id, language) pairs, after which a plain GROUP_CONCAT and COUNT(*) are already right. Group by match_id as well as the title so two matches with the same teams would stay apart. The cost is one pass over the feeds plus a small sort per match.
Reference solution (MySQL)
SELECT m.title,
GROUP_CONCAT(DISTINCT f.language ORDER BY f.language SEPARATOR ', ') AS languages,
COUNT(DISTINCT f.language) AS language_count
FROM LiveMatch m
JOIN CommentaryFeed f ON f.match_id = m.match_id
WHERE f.went_live = 1
GROUP BY m.match_id, m.titleAnother way
SELECT m.title, GROUP_CONCAT(f.language ORDER BY f.language SEPARATOR ', ') AS languages, COUNT(*) AS language_count
FROM LiveMatch m
JOIN (SELECT DISTINCT match_id, language FROM CommentaryFeed WHERE went_live = 1) f ON f.match_id = m.match_id
GROUP BY m.match_id, m.title← Hotstar: Death-Over Ad Breaks Running Long · Hotstar: Plans With No Active Subscriber →