Hotstar: Death-Over Ad Breaks Running Long

Easy
698.

Hotstar: Death-Over Ad Breaks Running Long

EasyBasics

MySQL

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

ColumnType
break_idint
match_idint
inningsint
over_noint
duration_secsint

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.

Example

The rows must come back in the order shown.

AdBreak

break_idmatch_idinningsover_noduration_secs
1101115120
210111695
310111890
4101220NULL
5102217130
610211960
710211691

Output

break_idmatch_idinningsover_no
2101116
7102116
5102217
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: 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

ColumnType
break_idint
match_idint
inningsint
over_noint
duration_secsint

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_idmatch_idinningsover_noduration_secs
1101115120
210111695
310111890
4101220NULL
5102217130
610211960
710211691

Output

break_idmatch_idinningsover_no
2101116
7102116
5102217

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_id

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