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
| Column | Type |
|---|---|
| match_id | int |
| match_date | date |
| home_team | varchar |
| away_team | varchar |
Primary key: match_id.
One row per T20 league match.
Table: TicketBooking
| Column | Type |
|---|---|
| booking_id | int |
| match_id | int |
| booked_on | date |
| seats | int |
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_id | match_date | home_team | away_team |
|---|---|---|---|
| 1 | 2024-04-12 | Mumbai Monarchs | Delhi Defenders |
| 2 | 2024-04-14 | Chennai Cheetahs | Kolkata Krakens |
| 3 | 2024-04-20 | Jaipur Jaguars | Punjab Pioneers |
TicketBooking
| booking_id | match_id | booked_on | seats |
|---|---|---|---|
| 1 | 1 | 2024-03-20 | 4 |
| 2 | 1 | 2024-03-29 | 2 |
| 3 | 1 | 2024-04-10 | 6 |
| 4 | 2 | 2024-03-31 | 3 |
| 5 | 2 | 2024-04-01 | 5 |
| 6 | 2 | 2024-04-13 | 2 |
| 7 | 1 | 2024-04-12 | 8 |
Output
| match_id | match_date | total_seats | early_seats | early_share_pct |
|---|---|---|---|---|
| 1 | 2024-04-12 | 20 | 6 | 30 |
| 2 | 2024-04-14 | 10 | 3 | 30 |
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_idAnother 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 →