Hotstar: Commentary Language Split per Match — SQL Problem
Hotstar decides how many commentary teams to staff for a language by how many people chose that feed.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #707
Problem statement
Hotstar decides how many commentary teams to staff for a language by how many people chose that feed. Each row of FeedView is a viewer tuning in to a match in a language; viewers reconnect, so the same viewer, match and language can appear many times, and a viewer can try several languages.
For every match, return match_id, hindi_viewers and english_viewers (distinct viewers who tuned in to that language), regional_viewers (distinct viewers who tuned in to any other language), and total_viewers (distinct viewers of the match in any language). A viewer counts once in every bucket they touched, and once in the total. 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: FeedView
| Column | Type |
|---|---|
| view_id | int |
| match_id | int |
| viewer_id | int |
| language | varchar |
Primary key: view_id.
One row per time a viewer tuned in to a commentary feed. Language names are spelled consistently (Hindi, English, Tamil, …).
Examples
Example 1
FeedView
| view_id | match_id | viewer_id | language |
|---|---|---|---|
| 1 | 11 | 301 | Hindi |
| 2 | 11 | 301 | Hindi |
| 3 | 11 | 302 | English |
| 4 | 11 | 303 | Tamil |
| 5 | 11 | 303 | Telugu |
| 6 | 11 | 304 | Hindi |
| 7 | 11 | 304 | Kannada |
| 8 | 12 | 301 | English |
| 9 | 12 | 305 | Bengali |
Output
| match_id | hindi_viewers | english_viewers | regional_viewers | total_viewers |
|---|---|---|---|---|
| 11 | 2 | 1 | 2 | 4 |
| 12 | 0 | 1 | 1 | 2 |
How to solve Hotstar: Commentary Language Split per Match
This is a pivot: one row per match with a column per language bucket. Conditional aggregation builds it in one pass — CASE WHEN language = 'Hindi' THEN viewer_id END is the viewer id on Hindi rows and NULL elsewhere, and COUNT(DISTINCT …) over it counts distinct Hindi viewers, ignoring the NULLs. The regional bucket uses NOT IN ('Hindi', 'English'), so a viewer who tried both Tamil and Telugu still produces one distinct id there.
DISTINCT is doing the real work: reconnections repeat (match, viewer, language), and without it viewer 301's two Hindi rows would count twice. The total is a separate COUNT(DISTINCT viewer_id), not the sum of the buckets — viewer 304 is in both the Hindi and the regional bucket but is one person.
The alternative first reduces the data to distinct (match, viewer, bucket) triples; after that each bucket is a plain SUM of a boolean, and the total comes from a correlated subquery because the triples can list a viewer twice. The single-pass version reads the table once.
Reference solution (MySQL)
SELECT match_id,
COUNT(DISTINCT CASE WHEN language = 'Hindi' THEN viewer_id END) AS hindi_viewers,
COUNT(DISTINCT CASE WHEN language = 'English' THEN viewer_id END) AS english_viewers,
COUNT(DISTINCT CASE WHEN language NOT IN ('Hindi', 'English') THEN viewer_id END) AS regional_viewers,
COUNT(DISTINCT viewer_id) AS total_viewers
FROM FeedView
GROUP BY match_idAnother way
SELECT b.match_id,
SUM(b.bucket = 'hindi') AS hindi_viewers,
SUM(b.bucket = 'english') AS english_viewers,
SUM(b.bucket = 'regional') AS regional_viewers,
(SELECT COUNT(DISTINCT viewer_id) FROM FeedView f WHERE f.match_id = b.match_id) AS total_viewers
FROM (
SELECT DISTINCT match_id, viewer_id,
CASE WHEN language = 'Hindi' THEN 'hindi' WHEN language = 'English' THEN 'english' ELSE 'regional' END AS bucket
FROM FeedView
) b
GROUP BY b.match_id← Hotstar: Advertisers Owed Make-Good Impressions · Hotstar: Biggest Over-on-Over Jump in Viewers →