Hotstar: Top Two Shows per Language by Unique Viewers

Medium
714.

Hotstar: Top Two Shows per Language by Unique Viewers

Each language home page on Hotstar leads with its most-watched shows. The editorial team measures a show by unique viewers (distinct profiles that played it), and wants the top two values per language — with ties, so a third show that ties the second also makes the row.

Return language, show_name and unique_viewers for every show whose unique-viewer count is among the two highest distinct counts of its language. Shows nobody played are never returned. 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: TvShow

ColumnType
show_idint
show_namevarchar
languagevarchar

Primary key: show_id.

One row per show in the catalogue, in its original language.

Table: ShowView

ColumnType
view_idint
show_idint
profile_idint

Primary key: view_id.

One row per play of a show; a profile can play a show many times.

Example

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

TvShow

show_idshow_namelanguage
1Pyaar Ki PatangHindi
2Sindoor Ki SaugandhHindi
3Second InningsHindi
4Kitchen KingsHindi
5Neer KolamTamil
6Kadal KaatruTamil

ShowView

view_idshow_idprofile_id
11801
21802
31803
41801
52801
62804
73805
83806
94807
105808
1 more row

Output

languageshow_nameunique_viewers
HindiPyaar Ki Patang3
HindiSindoor Ki Saugandh2
HindiSecond Innings2
TamilNeer Kolam1
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: Top Two Shows per Language by Unique Viewers

Each language home page on Hotstar leads with its most-watched shows.

  • Difficulty: Medium
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #714

Problem statement

Each language home page on Hotstar leads with its most-watched shows. The editorial team measures a show by unique viewers (distinct profiles that played it), and wants the top two values per language — with ties, so a third show that ties the second also makes the row.

Return language, show_name and unique_viewers for every show whose unique-viewer count is among the two highest distinct counts of its language. Shows nobody played are never returned. 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: TvShow

ColumnType
show_idint
show_namevarchar
languagevarchar

Primary key: show_id.

One row per show in the catalogue, in its original language.

Table: ShowView

ColumnType
view_idint
show_idint
profile_idint

Primary key: view_id.

One row per play of a show; a profile can play a show many times.

Examples

Example 1

TvShow

show_idshow_namelanguage
1Pyaar Ki PatangHindi
2Sindoor Ki SaugandhHindi
3Second InningsHindi
4Kitchen KingsHindi
5Neer KolamTamil
6Kadal KaatruTamil

ShowView

view_idshow_idprofile_id
11801
21802
31803
41801
52801
62804
73805
83806
94807
105808
115808

Output

languageshow_nameunique_viewers
HindiPyaar Ki Patang3
HindiSindoor Ki Saugandh2
HindiSecond Innings2
TamilNeer Kolam1

How to solve Hotstar: Top Two Shows per Language by Unique Viewers

Two steps. First the measure: COUNT(DISTINCT profile_id) per show, so profile 801's two plays of the first show count once (and the Tamil show watched twice by one profile has one unique viewer). The inner join leaves out shows nobody played, as required.

Then the cut. The statement asks for the top two values, with every show holding them — that is DENSE_RANK(), which numbers distinct values 1, 2, 3 with no gaps, so all shows at the highest count get 1 and all shows at the second-highest get 2. RANK() would skip to 3 after a tie at the top and drop the second value; ROW_NUMBER() would cut ties arbitrarily. In the example, two Hindi shows tie at 2 viewers and both are returned beside the 3-viewer leader.

The alternative states the same rule as a correlated count: a show qualifies when fewer than two distinct higher counts exist in its language. It is quadratic in shows per language; the window version sorts once.

Reference solution (MySQL)

WITH reach AS (
  SELECT s.language, s.show_name, COUNT(DISTINCT v.profile_id) AS unique_viewers
  FROM TvShow s
  JOIN ShowView v ON v.show_id = s.show_id
  GROUP BY s.show_id, s.language, s.show_name
),
ranked AS (
  SELECT language, show_name, unique_viewers,
         DENSE_RANK() OVER (PARTITION BY language ORDER BY unique_viewers DESC) AS dr
  FROM reach
)
SELECT language, show_name, unique_viewers
FROM ranked
WHERE dr <= 2

Another way

WITH reach AS (
  SELECT s.language, s.show_name, COUNT(DISTINCT v.profile_id) AS unique_viewers
  FROM TvShow s JOIN ShowView v ON v.show_id = s.show_id
  GROUP BY s.show_id, s.language, s.show_name
)
SELECT r.language, r.show_name, r.unique_viewers
FROM reach r
WHERE (SELECT COUNT(DISTINCT o.unique_viewers) FROM reach o
       WHERE o.language = r.language AND o.unique_viewers > r.unique_viewers) < 2

← Hotstar: Unsold Ad Slots by Innings Phase · Hotstar: Final-Day Paid Sign-Ups by Hour With a Running Total →