Concert Sell-Through and Sales Status — SQL Joins Problem
The promoter's weekly report shows how full every concert is.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #85
Problem statement
The promoter's weekly report shows how full every concert is. tickets_sold is the total qty over a concert's sales (0 when it has none), and sell-through is tickets_sold * 100 / capacity of its venue.
Return every concert with concert_id, title, tickets_sold, sell_through_pct (rounded to one decimal) and sales_status: 'Sold Out' when tickets sold reach the capacity, 'Strong' when sell-through is at least 75%, otherwise 'Needs Push'. Order by sell_through_pct descending, then concert_id ascending.
Tables
Table: venues
| Column | Type |
|---|---|
| venue_id | int |
| venue_name | varchar |
| capacity | int |
Primary key: venue_id.
One row per venue; capacity is the number of sellable seats, always positive.
Table: concerts
| Column | Type |
|---|---|
| concert_id | int |
| title | varchar |
| venue_id | int |
| concert_date | date |
Primary key: concert_id.
Every venue_id is in venues.
Table: ticket_sales
| Column | Type |
|---|---|
| sale_id | int |
| concert_id | int |
| qty | int |
| sold_at | datetime |
Primary key: sale_id.
One row per order; the platform never sells more seats than the venue holds.
Examples
Example 1
venues
| venue_id | venue_name | capacity |
|---|---|---|
| 1 | Phoenix Arena | 200 |
| 2 | Blue Frog Hall | 87 |
| 3 | Shanmukhananda Hall | 1003 |
concerts
| concert_id | title | venue_id | concert_date |
|---|---|---|---|
| 1 | Prateek Kuhad Tour | 1 | 2025-05-02 |
| 2 | Jazz by the Bay | 2 | 2025-05-03 |
| 3 | Indian Ocean Unplugged | 2 | 2025-05-10 |
| 4 | Zakir Hussain Tribute | 3 | 2025-05-17 |
| 5 | Coke Studio Nights | 1 | 2025-05-24 |
ticket_sales
| sale_id | concert_id | qty | sold_at |
|---|---|---|---|
| 1 | 1 | 120 | 2025-04-01 10:00:00 |
| 2 | 1 | 80 | 2025-04-03 18:30:00 |
| 3 | 2 | 40 | 2025-04-02 12:00:00 |
| 4 | 2 | 26 | 2025-04-05 20:15:00 |
| 5 | 3 | 30 | 2025-04-04 09:45:00 |
| 6 | 5 | 150 | 2025-04-06 11:00:00 |
Output
| concert_id | title | tickets_sold | sell_through_pct | sales_status |
|---|---|---|---|---|
| 1 | Prateek Kuhad Tour | 200 | 100 | Sold Out |
| 2 | Jazz by the Bay | 66 | 75.9 | Strong |
| 5 | Coke Studio Nights | 150 | 75 | Strong |
| 3 | Indian Ocean Unplugged | 30 | 34.5 | Needs Push |
| 4 | Zakir Hussain Tribute | 0 | 0 | Needs Push |
How to solve Concert Sell-Through and Sales Status
Three tables, one row per concert. The venue is an inner join (every concert has one), but sales must be a LEFT JOIN, or a concert with nothing sold would vanish from the report. After grouping per concert, SUM(s.qty) is NULL for such a concert, so it is wrapped in COALESCE(…, 0) everywhere it is used.
Capacity is grouped along with the concert so it can be used outside an aggregate; it is determined by the concert anyway. The percentage divides by capacity and rounds to one decimal. The status is a CASE tested strictest first: a sold-out concert is also above 75%, so 'Sold Out' must come before 'Strong'. Comparing sold * 100 >= 75 * capacity keeps the threshold in whole numbers.
The alternative computes tickets_sold with a correlated scalar subquery per concert, which avoids grouping across the join entirely; it reads the sales of each concert once, the same work as the join with an index on ticket_sales(concert_id).
Reference solution (MySQL)
SELECT c.concert_id, c.title,
COALESCE(SUM(s.qty), 0) AS tickets_sold,
ROUND(COALESCE(SUM(s.qty), 0) * 100 / v.capacity, 1) AS sell_through_pct,
CASE
WHEN COALESCE(SUM(s.qty), 0) >= v.capacity THEN 'Sold Out'
WHEN COALESCE(SUM(s.qty), 0) * 100 >= 75 * v.capacity THEN 'Strong'
ELSE 'Needs Push'
END AS sales_status
FROM concerts c
JOIN venues v ON v.venue_id = c.venue_id
LEFT JOIN ticket_sales s ON s.concert_id = c.concert_id
GROUP BY c.concert_id, c.title, v.capacity
ORDER BY sell_through_pct DESC, c.concert_idAnother way
SELECT concert_id, title, tickets_sold, ROUND(tickets_sold * 100 / capacity, 1) AS sell_through_pct,
IF(tickets_sold >= capacity, 'Sold Out', IF(tickets_sold / capacity >= 0.75, 'Strong', 'Needs Push')) AS sales_status
FROM (
SELECT c.concert_id, c.title, v.capacity,
(SELECT COALESCE(SUM(qty), 0) FROM ticket_sales s WHERE s.concert_id = c.concert_id) AS tickets_sold
FROM concerts c JOIN venues v ON v.venue_id = c.venue_id
) t
ORDER BY sell_through_pct DESC, concert_id← Recurring Donors Across Three Months of 2024 · Resale Listings Marked Up Above Their Event's Average →