Hotstar: Death-Over Ad Breaks Running Long — SQL Problem
The live production desk at Hotstar worries that ad breaks late in a T20 innings run into the next delivery.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #698
Problem statement
The live production desk at Hotstar worries that ad breaks late in a T20 innings run into the next delivery. Every break between overs is logged with its length in seconds; a break the director cut short by hand has duration_secs NULL.
Return every break taken after an over from 16 to 20 inclusive (the death overs) whose duration_secs is more than 90. Return break_id, match_id, innings and over_no, ordered by match_id, then innings, then over_no, then break_id.
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: AdBreak
| Column | Type |
|---|---|
| break_id | int |
| match_id | int |
| innings | int |
| over_no | int |
| duration_secs | int |
Primary key: break_id.
One row per ad break, taken at the end of over over_no (1–20) of innings 1 or 2. duration_secs is NULL when the break was cut by hand and not timed.
Examples
Example 1
AdBreak
| break_id | match_id | innings | over_no | duration_secs |
|---|---|---|---|---|
| 1 | 101 | 1 | 15 | 120 |
| 2 | 101 | 1 | 16 | 95 |
| 3 | 101 | 1 | 18 | 90 |
| 4 | 101 | 2 | 20 | NULL |
| 5 | 102 | 2 | 17 | 130 |
| 6 | 102 | 1 | 19 | 60 |
| 7 | 102 | 1 | 16 | 91 |
Output
| break_id | match_id | innings | over_no |
|---|---|---|---|
| 2 | 101 | 1 | 16 |
| 7 | 102 | 1 | 16 |
| 5 | 102 | 2 | 17 |
How to solve Hotstar: Death-Over Ad Breaks Running Long
This is a filter on one table: a break either belongs on the desk's list or it does not, and nothing is counted. Two conditions must hold at once — the over is one of the death overs, and the break ran longer than 90 seconds — so they are joined with AND.
over_no BETWEEN 16 AND 20 keeps both end overs, which is what "inclusive" asks for; a break after the 15th over is not a death-over break. The length test is strict: a break of exactly 90 seconds fits the slot and stays off the list.
Breaks the director cut by hand have duration_secs NULL. NULL > 90 is neither true nor false, and WHERE keeps only rows where the condition is true, so they drop out without an explicit IS NOT NULL (the alternative spells it out anyway, and writes the bounds with >=/<=). The statement fixes the order, so the query ends with an ORDER BY over all four keys. It is a single scan of the table.
Reference solution (MySQL)
SELECT break_id, match_id, innings, over_no
FROM AdBreak
WHERE over_no BETWEEN 16 AND 20
AND duration_secs > 90
ORDER BY match_id, innings, over_no, break_idAnother way
SELECT break_id, match_id, innings, over_no FROM AdBreak WHERE over_no >= 16 AND over_no <= 20 AND duration_secs >= 91 AND duration_secs IS NOT NULL ORDER BY match_id, innings, over_no, break_id← Google: Search Experiment Lift Without Contaminated Users · Hotstar: Commentary Languages Live on Each Match →