Hotstar: Commentary Language Split per Match

Medium
707.

Hotstar: Commentary Language Split per Match

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

ColumnType
view_idint
match_idint
viewer_idint
languagevarchar

Primary key: view_id.

One row per time a viewer tuned in to a commentary feed. Language names are spelled consistently (Hindi, English, Tamil, …).

Example

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

FeedView

view_idmatch_idviewer_idlanguage
111301Hindi
211301Hindi
311302English
411303Tamil
511303Telugu
611304Hindi
711304Kannada
812301English
912305Bengali

Output

match_idhindi_viewersenglish_viewersregional_viewerstotal_viewers
112124
120112
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 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

ColumnType
view_idint
match_idint
viewer_idint
languagevarchar

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_idmatch_idviewer_idlanguage
111301Hindi
211301Hindi
311302English
411303Tamil
511303Telugu
611304Hindi
711304Kannada
812301English
912305Bengali

Output

match_idhindi_viewersenglish_viewersregional_viewerstotal_viewers
112124
120112

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_id

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