Hotstar: Advertisers Owed Make-Good Impressions
Advertisers buy a number of impressions in a match's ad breaks; the ad server logs what it actually delivered, in several partial rows per booking (one per…
- Difficulty: Medium
- Topics: Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #706
Problem statement
Advertisers buy a number of impressions in a match's ad breaks; the ad server logs what it actually delivered, in several partial rows per booking (one per break the spot ran in). When an advertiser's total delivery across all its bookings falls below 95% of what it booked, Hotstar's ad-sales team owes it make-good impressions in a later match.
Return advertiser, booked (total booked impressions), delivered (total delivered; a booking with no log rows delivered 0) and shortfall = booked − delivered, for every advertiser whose delivered is strictly less than 95% of booked. Order by shortfall descending, then advertiser ascending.
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: AdBooking
| Column | Type |
|---|---|
| booking_id | int |
| advertiser | varchar |
| match_id | int |
| booked_impressions | int |
Primary key: booking_id.
One row per impressions deal for one match. An advertiser can book several matches.
Table: DeliveryLog
| Column | Type |
|---|---|
| log_id | int |
| booking_id | int |
| delivered | int |
Primary key: log_id.
Impressions delivered for a booking in one ad break. A booking can have many rows or none.
Examples
Example 1
AdBooking
| booking_id | advertiser | match_id | booked_impressions |
|---|---|---|---|
| 1 | Zing Cola | 501 | 1000000 |
| 2 | Zing Cola | 502 | 500000 |
| 3 | NovaPay | 501 | 800000 |
| 4 | Spark Fantasy | 502 | 400000 |
| 5 | Crunchy Bites | 501 | 200000 |
DeliveryLog
| log_id | booking_id | delivered |
|---|---|---|
| 1 | 1 | 500000 |
| 2 | 1 | 400000 |
| 3 | 2 | 470000 |
| 4 | 3 | 760000 |
| 5 | 4 | 300000 |
| 6 | 4 | 50000 |
Output
| advertiser | booked | delivered | shortfall |
|---|---|---|---|
| Crunchy Bites | 200000 | 0 | 200000 |
| Zing Cola | 1500000 | 1370000 | 130000 |
| Spark Fantasy | 400000 | 350000 | 50000 |
How to solve Hotstar: Advertisers Owed Make-Good Impressions
Two tables at different grains meet here: a booking is one row, its deliveries are many. Joining them directly and summing would fan out the booking — Zing Cola's one-million booking has two log rows, so its booked impressions would be counted twice. The fix is to bring both sides to the same grain before joining: collapse DeliveryLog to one row per booking (per_booking), then join that to AdBooking with a LEFT JOIN so bookings without any log row survive with a NULL that COALESCE turns into 0 (Crunchy Bites delivered nothing and owes the most).
Grouping by advertiser then gives booked and delivered totals, and the make-good test is delivered * 100 < booked * 95, kept in integers so nothing is lost to rounding. It is strict: NovaPay at exactly 95% is not owed. Over-delivery simply never qualifies.
The alternative avoids the fan-out differently: it sums bookings per advertiser on their own and fetches the advertiser's total delivery with a correlated subquery over the log joined to that advertiser's bookings. Both read each table once per advertiser at most; the CTE version is the one that scales.
Reference solution (MySQL)
WITH per_booking AS (
SELECT booking_id, SUM(delivered) AS delivered
FROM DeliveryLog
GROUP BY booking_id
),
per_advertiser AS (
SELECT b.advertiser,
SUM(b.booked_impressions) AS booked,
SUM(COALESCE(d.delivered, 0)) AS delivered
FROM AdBooking b
LEFT JOIN per_booking d ON d.booking_id = b.booking_id
GROUP BY b.advertiser
)
SELECT advertiser, booked, delivered, booked - delivered AS shortfall
FROM per_advertiser
WHERE delivered * 100 < booked * 95
ORDER BY shortfall DESC, advertiserAnother way
SELECT advertiser, booked, delivered, booked - delivered AS shortfall
FROM (
SELECT b.advertiser,
SUM(b.booked_impressions) AS booked,
COALESCE((SELECT SUM(l.delivered) FROM DeliveryLog l JOIN AdBooking x ON x.booking_id = l.booking_id WHERE x.advertiser = b.advertiser), 0) AS delivered
FROM AdBooking b
GROUP BY b.advertiser
) t
WHERE delivered < 0.95 * booked
ORDER BY shortfall DESC, advertiser← Hotstar: Playback Errors by App Platform · Hotstar: Commentary Language Split per Match →