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
| Column | Type |
|---|---|
| match_id | int |
| innings | int |
| over_no | int |
| viewers | bigint |
| is_glitch | bool |
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_id | innings | over_no | viewers | is_glitch |
|---|---|---|---|---|
| 1 | 1 | 2 | 8000000 | 0 |
| 1 | 1 | 6 | 9000000 | 0 |
| 1 | 1 | 10 | 10000000 | 0 |
| 1 | 1 | 15 | 60000000 | 1 |
| 1 | 1 | 20 | 11000000 | 0 |
| 1 | 2 | 4 | 12000000 | 0 |
| 1 | 2 | 10 | 9400000 | 0 |
| 2 | 1 | 5 | 5000000 | 0 |
| 2 | 1 | 12 | 6000000 | 0 |
| 2 | 2 | 3 | 7000000 | 0 |
| 2 | 2 | 9 | 6500000 | 0 |
| 2 | 2 | 15 | 6000000 | 0 |
| 3 | 1 | 8 | 40000000 | 1 |
Output
| match_id | innings | over_no | smoothed_viewers |
|---|---|---|---|
| 1 | 2 | 4 | 11000000 |
| 2 | 2 | 9 | 6500000 |
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 = 1Another 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 →