Hotstar: Unsold Ad Slots by Innings Phase

Medium
713.

Hotstar: Unsold Ad Slots by Innings Phase

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

ColumnType
slot_idint
match_idint
over_noint
soldbool
rateint

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.

Example

The rows must come back in the order shown.

AdSlot

slot_idmatch_idover_nosoldrate
1931180000
2960180000
3970120000
49120120000
59151120000
69161250000
79201250000
81011180000

Output

phasetotal_slotsunsold_slotsunsold_value
Powerplay31180000
Middle32240000
Death200
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: 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

ColumnType
slot_idint
match_idint
over_noint
soldbool
rateint

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_idmatch_idover_nosoldrate
1931180000
2960180000
3970120000
49120120000
59151120000
69161250000
79201250000
81011180000

Output

phasetotal_slotsunsold_slotsunsold_value
Powerplay31180000
Middle32240000
Death200

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_order

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