Hotstar: Commentary Languages Live on Each Match

Easy
699.

Hotstar: Commentary Languages Live on Each Match

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

ColumnType
match_idint
titlevarchar

Primary key: match_id.

One row per streamed match.

Table: CommentaryFeed

ColumnType
feed_idint
match_idint
languagevarchar
went_livebool

Primary key: feed_id.

One row per commentary feed started for a match. A restarted feed is a new row with the same language.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

LiveMatch

match_idtitle
1Mumbai Mariners vs Chennai Chargers
2Delhi Dynamos vs Kolkata Knights
3Bengaluru Blasters vs Hyderabad Hawks

CommentaryFeed

feed_idmatch_idlanguagewent_live
11Hindi1
21English1
31Tamil1
41Hindi1
52English1
62Bengali0
72Kannada1
83Telugu0

Output

titlelanguageslanguage_count
Mumbai Mariners vs Chennai ChargersEnglish, Hindi, Tamil3
Delhi Dynamos vs Kolkata KnightsEnglish, Kannada2
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: 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

ColumnType
match_idint
titlevarchar

Primary key: match_id.

One row per streamed match.

Table: CommentaryFeed

ColumnType
feed_idint
match_idint
languagevarchar
went_livebool

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_idtitle
1Mumbai Mariners vs Chennai Chargers
2Delhi Dynamos vs Kolkata Knights
3Bengaluru Blasters vs Hyderabad Hawks

CommentaryFeed

feed_idmatch_idlanguagewent_live
11Hindi1
21English1
31Tamil1
41Hindi1
52English1
62Bengali0
72Kannada1
83Telugu0

Output

titlelanguageslanguage_count
Mumbai Mariners vs Chennai ChargersEnglish, Hindi, Tamil3
Delhi Dynamos vs Kolkata KnightsEnglish, Kannada2

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.title

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