Hotstar: Peak of Three-Sample Smoothed Concurrency per Match

Hard
716.

Hotstar: Peak of Three-Sample Smoothed Concurrency per Match

Raw concurrency samples jump around — a wicket, an app refresh, a telemetry glitch — so Hotstar's capacity planners size the next match from a smoothed curve. Samples are taken at the end of some overs and ordered by innings, then over_no. Samples flagged is_glitch = 1 are bad readings and are removed before smoothing, so they never enter an average.

The smoothed value at a clean sample is the average of that sample and the two clean samples before it in the same match (fewer at the start of a match: the first sample averages itself, the second averages two), rounded to a whole number of viewers. For every match with at least one clean sample, return the sample where the rounded smoothed value is highest, as match_id, innings, over_no and smoothed_viewers. If several samples share the highest rounded value, return the earliest (lowest innings, then lowest over). 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: OverSnapshot

ColumnType
match_idint
inningsint
over_noint
viewersbigint
is_glitchbool

Primary key (together): match_id, innings, over_no.

Concurrent viewers sampled at the end of over over_no (1–20) of innings 1 or 2. is_glitch is 1 when monitoring marked the reading as bad.

Example

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

OverSnapshot

match_idinningsover_noviewersis_glitch
11280000000
11690000000
1110100000000
1115600000001
1120110000000
124120000000
121094000000
21550000000
211260000000
22370000000
3 more rows

Output

match_idinningsover_nosmoothed_viewers
12411000000
2296500000
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: Peak of Three-Sample Smoothed Concurrency per Match

Raw concurrency samples jump around — a wicket, an app refresh, a telemetry glitch — so Hotstar's capacity planners size the next match from a smoothed curve.

  • Difficulty: Hard
  • Topics: Window Functions
  • Dialect: MySQL
  • Problem: #716

Problem statement

Raw concurrency samples jump around — a wicket, an app refresh, a telemetry glitch — so Hotstar's capacity planners size the next match from a smoothed curve. Samples are taken at the end of some overs and ordered by innings, then over_no. Samples flagged is_glitch = 1 are bad readings and are removed before smoothing, so they never enter an average.

The smoothed value at a clean sample is the average of that sample and the two clean samples before it in the same match (fewer at the start of a match: the first sample averages itself, the second averages two), rounded to a whole number of viewers. For every match with at least one clean sample, return the sample where the rounded smoothed value is highest, as match_id, innings, over_no and smoothed_viewers. If several samples share the highest rounded value, return the earliest (lowest innings, then lowest over). 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: OverSnapshot

ColumnType
match_idint
inningsint
over_noint
viewersbigint
is_glitchbool

Primary key: match_id, innings, over_no.

Concurrent viewers sampled at the end of over over_no (1–20) of innings 1 or 2. is_glitch is 1 when monitoring marked the reading as bad.

Examples

Example 1

OverSnapshot

match_idinningsover_noviewersis_glitch
11280000000
11690000000
1110100000000
1115600000001
1120110000000
124120000000
121094000000
21550000000
211260000000
22370000000
22965000000
221560000000
318400000001

Output

match_idinningsover_nosmoothed_viewers
12411000000
2296500000

How to solve Hotstar: Peak of Three-Sample Smoothed Concurrency per Match

A moving average is a window aggregate with a sliding frame. Over the clean samples of each match, ordered by innings then over, AVG(viewers) OVER (PARTITION BY match_id ORDER BY innings, over_no ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) averages the current sample with up to two before it. At the start of a match the frame simply holds fewer rows, so the first sample averages itself and the second averages two — exactly the statement's rule. The frame must be ROWS: samples are irregular, and "the two before" means the previous two samples, not the previous two overs.

Order of operations is the trap. The glitch reading of 6 crore in match 1 must be removed before the window runs; filtering it afterwards would still have let it inflate the averages of the next two samples. A CTE that keeps is_glitch = 0 does that, and a match with only glitches (match 3) has no clean sample and is not listed.

Finally the rounded smoothed values are ranked per match with ROW_NUMBER() ordered by value descending, then innings and over, which returns match 2's earlier sample when two samples both smooth to 6,500,000. The alternative builds each frame by hand — a self join keeping the samples with fewer than three clean samples between them and the current one — and picks the peak with NOT EXISTS; it is quadratic, while the window version is one sort per match.

Reference solution (MySQL)

WITH clean AS (
  SELECT match_id, innings, over_no, viewers
  FROM OverSnapshot
  WHERE is_glitch = 0
),
smoothed AS (
  SELECT match_id, innings, over_no,
         ROUND(AVG(viewers) OVER (PARTITION BY match_id ORDER BY innings, over_no
                                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS smoothed_viewers
  FROM clean
),
ranked AS (
  SELECT match_id, innings, over_no, smoothed_viewers,
         ROW_NUMBER() OVER (PARTITION BY match_id ORDER BY smoothed_viewers DESC, innings, over_no) AS rn
  FROM smoothed
)
SELECT match_id, innings, over_no, smoothed_viewers
FROM ranked
WHERE rn = 1

Another way

WITH k AS (
  SELECT match_id, innings, over_no, viewers, innings * 100 + over_no AS pos
  FROM OverSnapshot WHERE is_glitch = 0
),
s AS (
  SELECT p.match_id, p.innings, p.over_no, p.pos, ROUND(AVG(q.viewers), 0) AS smoothed_viewers
  FROM k p
  JOIN k q ON q.match_id = p.match_id AND q.pos <= p.pos
          AND (SELECT COUNT(*) FROM k r WHERE r.match_id = p.match_id AND r.pos > q.pos AND r.pos <= p.pos) < 3
  GROUP BY p.match_id, p.innings, p.over_no, p.pos
)
SELECT match_id, innings, over_no, smoothed_viewers
FROM s
WHERE NOT EXISTS (
  SELECT 1 FROM s b
  WHERE b.match_id = s.match_id
    AND (b.smoothed_viewers > s.smoothed_viewers OR (b.smoothed_viewers = s.smoothed_viewers AND b.pos < s.pos))
)

← Hotstar: Final-Day Paid Sign-Ups by Hour With a Running Total · Hotstar: Viewers Lost During Each Rain Delay →