Hotstar: Unsold Ad Slots by Innings Phase — SQL Problem
Ad inventory in a T20 is priced by phase: overs 1–6 are the Powerplay, overs 7–15 the Middle overs and overs 16–20 the Death overs.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #713
Problem statement
Ad inventory in a T20 is priced by phase: overs 1–6 are the Powerplay, overs 7–15 the Middle overs and overs 16–20 the Death overs. Hotstar's ad-sales team wants to see where inventory went unsold and what it would have earned at its rate card.
Return phase, total_slots, unsold_slots and unsold_value (the sum of rate over unsold slots; 0 when every slot sold) for every phase that has at least one slot. Order the rows by phase in match order: Powerplay, then Middle, then Death.
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: AdSlot
| Column | Type |
|---|---|
| slot_id | int |
| match_id | int |
| over_no | int |
| sold | bool |
| rate | int |
Primary key: slot_id.
One 10-second ad slot in the break after over over_no (1–20). rate is the rate-card price in rupees; sold is 1 if a buyer took it.
Examples
Example 1
AdSlot
| slot_id | match_id | over_no | sold | rate |
|---|---|---|---|---|
| 1 | 9 | 3 | 1 | 180000 |
| 2 | 9 | 6 | 0 | 180000 |
| 3 | 9 | 7 | 0 | 120000 |
| 4 | 9 | 12 | 0 | 120000 |
| 5 | 9 | 15 | 1 | 120000 |
| 6 | 9 | 16 | 1 | 250000 |
| 7 | 9 | 20 | 1 | 250000 |
| 8 | 10 | 1 | 1 | 180000 |
Output
| phase | total_slots | unsold_slots | unsold_value |
|---|---|---|---|
| Powerplay | 3 | 1 | 180000 |
| Middle | 3 | 2 | 240000 |
| Death | 2 | 0 | 0 |
How to solve Hotstar: Unsold Ad Slots by Innings Phase
The grouping key is derived: a CASE on over_no turns overs 1–6 into Powerplay, 7–15 into Middle and 16–20 into Death. Because a CASE takes the first branch that matches, over_no <= 15 in the second branch only sees overs above 6, so the boundaries 6/7 and 15/16 fall exactly where the rate card puts them.
Inside each phase, COUNT(*) counts the slots, and two conditional aggregates count and price the unsold ones. Writing ELSE 0 matters for unsold_value: without it a phase that sold out would add only NULLs and report NULL instead of 0. Phases with no slots produce no group and are not listed.
The order is by the match, not the alphabet. The solution carries a phase_order number beside the label and sorts by it; the alternative sorts by MIN(over_no) of the group, which is just as monotonic, and uses nested IF and COUNT(*) - SUM(sold) for the same counts. One scan and a three-row sort.
Reference solution (MySQL)
SELECT phase, COUNT(*) AS total_slots,
SUM(CASE WHEN sold = 0 THEN 1 ELSE 0 END) AS unsold_slots,
SUM(CASE WHEN sold = 0 THEN rate ELSE 0 END) AS unsold_value
FROM (
SELECT sold, rate,
CASE WHEN over_no <= 6 THEN 'Powerplay' WHEN over_no <= 15 THEN 'Middle' ELSE 'Death' END AS phase,
CASE WHEN over_no <= 6 THEN 1 WHEN over_no <= 15 THEN 2 ELSE 3 END AS phase_order
FROM AdSlot
) s
GROUP BY phase, phase_order
ORDER BY phase_orderAnother way
SELECT IF(over_no <= 6, 'Powerplay', IF(over_no <= 15, 'Middle', 'Death')) AS phase,
COUNT(*) AS total_slots,
COUNT(*) - SUM(sold) AS unsold_slots,
SUM(IF(sold = 1, 0, rate)) AS unsold_value
FROM AdSlot
GROUP BY phase
ORDER BY MIN(over_no)← Hotstar: Subscriptions With No Play in Their First Week · Hotstar: Top Two Shows per Language by Unique Viewers →