Hotstar: Advertisers Owed Make-Good Impressions

Medium
706.

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

ColumnType
booking_idint
advertiservarchar
match_idint
booked_impressionsint

Primary key: booking_id.

One row per impressions deal for one match. An advertiser can book several matches.

Table: DeliveryLog

ColumnType
log_idint
booking_idint
deliveredint

Primary key: log_id.

Impressions delivered for a booking in one ad break. A booking can have many rows or none.

Example

The rows must come back in the order shown.

AdBooking

booking_idadvertisermatch_idbooked_impressions
1Zing Cola5011000000
2Zing Cola502500000
3NovaPay501800000
4Spark Fantasy502400000
5Crunchy Bites501200000

DeliveryLog

log_idbooking_iddelivered
11500000
21400000
32470000
43760000
54300000
6450000

Output

advertiserbookeddeliveredshortfall
Crunchy Bites2000000200000
Zing Cola15000001370000130000
Spark Fantasy40000035000050000
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: 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

ColumnType
booking_idint
advertiservarchar
match_idint
booked_impressionsint

Primary key: booking_id.

One row per impressions deal for one match. An advertiser can book several matches.

Table: DeliveryLog

ColumnType
log_idint
booking_idint
deliveredint

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_idadvertisermatch_idbooked_impressions
1Zing Cola5011000000
2Zing Cola502500000
3NovaPay501800000
4Spark Fantasy502400000
5Crunchy Bites501200000

DeliveryLog

log_idbooking_iddelivered
11500000
21400000
32470000
43760000
54300000
6450000

Output

advertiserbookeddeliveredshortfall
Crunchy Bites2000000200000
Zing Cola15000001370000130000
Spark Fantasy40000035000050000

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, advertiser

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