Concert Sell-Through and Sales Status

Medium
85.

Concert Sell-Through and Sales Status

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

ColumnType
venue_idint
venue_namevarchar
capacityint

Primary key: venue_id.

One row per venue; capacity is the number of sellable seats, always positive.

Table: concerts

ColumnType
concert_idint
titlevarchar
venue_idint
concert_datedate

Primary key: concert_id.

Every venue_id is in venues.

Table: ticket_sales

ColumnType
sale_idint
concert_idint
qtyint
sold_atdatetime

Primary key: sale_id.

One row per order; the platform never sells more seats than the venue holds.

Example

The rows must come back in the order shown.

venues

venue_idvenue_namecapacity
1Phoenix Arena200
2Blue Frog Hall87
3Shanmukhananda Hall1003

concerts

concert_idtitlevenue_idconcert_date
1Prateek Kuhad Tour12025-05-02
2Jazz by the Bay22025-05-03
3Indian Ocean Unplugged22025-05-10
4Zakir Hussain Tribute32025-05-17
5Coke Studio Nights12025-05-24

ticket_sales

sale_idconcert_idqtysold_at
111202025-04-01 10:00:00
21802025-04-03 18:30:00
32402025-04-02 12:00:00
42262025-04-05 20:15:00
53302025-04-04 09:45:00
651502025-04-06 11:00:00

Output

concert_idtitletickets_soldsell_through_pctsales_status
1Prateek Kuhad Tour200100Sold Out
2Jazz by the Bay6675.9Strong
5Coke Studio Nights15075Strong
3Indian Ocean Unplugged3034.5Needs Push
4Zakir Hussain Tribute00Needs Push
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.

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

ColumnType
venue_idint
venue_namevarchar
capacityint

Primary key: venue_id.

One row per venue; capacity is the number of sellable seats, always positive.

Table: concerts

ColumnType
concert_idint
titlevarchar
venue_idint
concert_datedate

Primary key: concert_id.

Every venue_id is in venues.

Table: ticket_sales

ColumnType
sale_idint
concert_idint
qtyint
sold_atdatetime

Primary key: sale_id.

One row per order; the platform never sells more seats than the venue holds.

Examples

Example 1

venues

venue_idvenue_namecapacity
1Phoenix Arena200
2Blue Frog Hall87
3Shanmukhananda Hall1003

concerts

concert_idtitlevenue_idconcert_date
1Prateek Kuhad Tour12025-05-02
2Jazz by the Bay22025-05-03
3Indian Ocean Unplugged22025-05-10
4Zakir Hussain Tribute32025-05-17
5Coke Studio Nights12025-05-24

ticket_sales

sale_idconcert_idqtysold_at
111202025-04-01 10:00:00
21802025-04-03 18:30:00
32402025-04-02 12:00:00
42262025-04-05 20:15:00
53302025-04-04 09:45:00
651502025-04-06 11:00:00

Output

concert_idtitletickets_soldsell_through_pctsales_status
1Prateek Kuhad Tour200100Sold Out
2Jazz by the Bay6675.9Strong
5Coke Studio Nights15075Strong
3Indian Ocean Unplugged3034.5Needs Push
4Zakir Hussain Tribute00Needs 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_id

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