Share of Seats Booked Two Weeks Early for Each Match

Medium
462.

Share of Seats Booked Two Weeks Early for Each Match

The ticketing team wants to know how far ahead fans buy. A booking is early when it was made at least 14 days before the match date (a booking exactly 14 days before counts as early).

For every match with at least one booking, return the columns match_id, match_date, total_seats (all seats booked), early_seats and early_share_pct = 100 * early_seats / total_seats, rounded to 2 decimal places. Matches with no bookings are left out. Order the rows by match_date, then match_id.

Tables

Table: T20Match

ColumnType
match_idint
match_datedate
home_teamvarchar
away_teamvarchar

Primary key: match_id.

One row per T20 league match.

Table: TicketBooking

ColumnType
booking_idint
match_idint
booked_ondate
seatsint

Primary key: booking_id.

One row per booking; booked_on is never after the match date. match_id is always in T20Match.

Example

The rows must come back in the order shown.

T20Match

match_idmatch_datehome_teamaway_team
12024-04-12Mumbai MonarchsDelhi Defenders
22024-04-14Chennai CheetahsKolkata Krakens
32024-04-20Jaipur JaguarsPunjab Pioneers

TicketBooking

booking_idmatch_idbooked_onseats
112024-03-204
212024-03-292
312024-04-106
422024-03-313
522024-04-015
622024-04-132
712024-04-128

Output

match_idmatch_datetotal_seatsearly_seatsearly_share_pct
12024-04-1220630
22024-04-1410330
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.

Share of Seats Booked Two Weeks Early for Each Match

The ticketing team wants to know how far ahead fans buy. A booking is early when it was made at least 14 days before the match date (a booking exactly 14…

  • Difficulty: Medium
  • Topics: Dates, Joins, Conditional Logic
  • Dialect: MySQL
  • Problem: #462

Problem statement

The ticketing team wants to know how far ahead fans buy. A booking is early when it was made at least 14 days before the match date (a booking exactly 14 days before counts as early).

For every match with at least one booking, return the columns match_id, match_date, total_seats (all seats booked), early_seats and early_share_pct = 100 * early_seats / total_seats, rounded to 2 decimal places. Matches with no bookings are left out. Order the rows by match_date, then match_id.

Tables

Table: T20Match

ColumnType
match_idint
match_datedate
home_teamvarchar
away_teamvarchar

Primary key: match_id.

One row per T20 league match.

Table: TicketBooking

ColumnType
booking_idint
match_idint
booked_ondate
seatsint

Primary key: booking_id.

One row per booking; booked_on is never after the match date. match_id is always in T20Match.

Examples

Example 1

T20Match

match_idmatch_datehome_teamaway_team
12024-04-12Mumbai MonarchsDelhi Defenders
22024-04-14Chennai CheetahsKolkata Krakens
32024-04-20Jaipur JaguarsPunjab Pioneers

TicketBooking

booking_idmatch_idbooked_onseats
112024-03-204
212024-03-292
312024-04-106
422024-03-313
522024-04-015
622024-04-132
712024-04-128

Output

match_idmatch_datetotal_seatsearly_seatsearly_share_pct
12024-04-1220630
22024-04-1410330

How to solve Share of Seats Booked Two Weeks Early for Each Match

The early/late decision needs a date from each table, so the bookings are joined to their match. An inner join also implements "matches with no bookings are left out": a match with no booking has no rows to group.

Per match, the total is SUM(seats) and the early part is a conditional sum: SUM(CASE WHEN DATEDIFF(match_date, booked_on) >= 14 THEN seats ELSE 0 END). DATEDIFF(a, b) is a - b in days, so a booking on 29 March for a 12 April match is 14 days ahead and counts, while one on 1 April for a 14 April match is 13 days ahead and does not. Comparing booked_on <= DATE_SUB(match_date, INTERVAL 14 DAY) is the same boundary written as a date.

The share is the ratio of the two sums times 100, rounded at the end; the total is at least one seat for every match that has a row, so there is no division by zero. One join and one grouping.

Reference solution (MySQL)

SELECT m.match_id, m.match_date,
       SUM(b.seats) AS total_seats,
       SUM(CASE WHEN DATEDIFF(m.match_date, b.booked_on) >= 14 THEN b.seats ELSE 0 END) AS early_seats,
       ROUND(100 * SUM(CASE WHEN DATEDIFF(m.match_date, b.booked_on) >= 14 THEN b.seats ELSE 0 END) / SUM(b.seats), 2) AS early_share_pct
FROM T20Match m
JOIN TicketBooking b ON b.match_id = m.match_id
GROUP BY m.match_id, m.match_date
ORDER BY m.match_date, m.match_id

Another way

SELECT m.match_id, m.match_date, SUM(b.seats) AS total_seats, SUM(IF(b.booked_on <= DATE_SUB(m.match_date, INTERVAL 14 DAY), b.seats, 0)) AS early_seats, ROUND(100 * SUM(IF(b.booked_on <= DATE_SUB(m.match_date, INTERVAL 14 DAY), b.seats, 0)) / SUM(b.seats), 2) AS early_share_pct FROM TicketBooking b JOIN T20Match m ON m.match_id = b.match_id GROUP BY m.match_id, m.match_date ORDER BY m.match_date, m.match_id

← Auction Buys Above Their Franchise's Average Price · Monthly Fantasy Contest Entries and the Change From Last Month →