Zomato: District Event Sell-Through by Category

Medium
1036.

Zomato: District Event Sell-Through by Category

District lists live events — comedy, music, workshops, sports — and the events team compares categories by how much of their capacity they sell.

For each category with at least one event, return category, events (number of events), tickets_sold (tickets of confirmed orders; 0 if none), capacity (the total capacity of all its events, including events that sold nothing) and sell_through_pct = 100 × tickets_sold / capacity rounded to 2 decimals. Order by sell_through_pct descending, then category ascending.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: LiveEvent

ColumnTypeValues
event_idint
titlevarchar
categoryenumcomedy, music, workshop, sports
capacityint

Primary key: event_id.

One row per event listed on District; capacity is the tickets on sale.

Table: TicketOrder

ColumnTypeValues
order_idint
event_idint
ticketsint
statusenumconfirmed, cancelled

Primary key: order_id.

One row per order of one or more tickets.

Example

The rows must come back in the order shown.

LiveEvent

event_idtitlecategorycapacity
1Laugh Riot Livecomedy200
2Open Mic Nightcomedy100
3Indie Nightsmusic500
4Pottery Basicsworkshop100
5Bass Drop Festmusic300
6Sketching 101workshop100

TicketOrder

order_idevent_idticketsstatus
11100confirmed
2150confirmed
3130cancelled
4250confirmed
53400confirmed
6460confirmed
7440confirmed

Output

categoryeventstickets_soldcapacitysell_through_pct
comedy220030066.67
music240080050
workshop210020050
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.

Zomato: District Event Sell-Through by Category

District lists live events — comedy, music, workshops, sports — and the events team compares categories by how much of their capacity they sell.

  • Difficulty: Medium
  • Topics: Aggregation, Joins, Subqueries
  • Dialect: MySQL
  • Problem: #1036

Problem statement

District lists live events — comedy, music, workshops, sports — and the events team compares categories by how much of their capacity they sell.

For each category with at least one event, return category, events (number of events), tickets_sold (tickets of confirmed orders; 0 if none), capacity (the total capacity of all its events, including events that sold nothing) and sell_through_pct = 100 × tickets_sold / capacity rounded to 2 decimals. Order by sell_through_pct descending, then category ascending.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: LiveEvent

ColumnType
event_idint
titlevarchar
categoryenum(comedy, music, workshop, sports)
capacityint

Primary key: event_id.

One row per event listed on District; capacity is the tickets on sale.

Table: TicketOrder

ColumnType
order_idint
event_idint
ticketsint
statusenum(confirmed, cancelled)

Primary key: order_id.

One row per order of one or more tickets.

Examples

Example 1

LiveEvent

event_idtitlecategorycapacity
1Laugh Riot Livecomedy200
2Open Mic Nightcomedy100
3Indie Nightsmusic500
4Pottery Basicsworkshop100
5Bass Drop Festmusic300
6Sketching 101workshop100

TicketOrder

order_idevent_idticketsstatus
11100confirmed
2150confirmed
3130cancelled
4250confirmed
53400confirmed
6460confirmed
7440confirmed

Output

categoryeventstickets_soldcapacitysell_through_pct
comedy220030066.67
music240080050
workshop210020050

How to solve Zomato: District Event Sell-Through by Category

The trap is the fan-out. If events are joined straight to their orders and then grouped by category, an event with three orders appears on three rows and SUM(capacity) adds its capacity three times; an event with no orders disappears under an inner join, and its capacity is lost. Both make the sell-through wrong.

The fix is to aggregate at the right grain before going up a level. The per_event step gives one row per event with its confirmed tickets (COALESCE turns "no confirmed orders" into 0). Now each event is exactly one row, so grouping by category can safely count events, add tickets and add capacities, and the ratio is 100 * SUM(sold) / SUM(capacity).

Note that this is the category's overall rate (total tickets over total capacity), not the average of per-event percentages — a large event weighs more, which is the useful reading. Categories with no events never appear, and ties on the rate fall back to the category name.

The alternative aggregates the two sides independently — events and capacity per category, confirmed tickets per category — and joins the two summaries with a LEFT JOIN, which is the same idea in a different shape. Both read each table once.

Reference solution (MySQL)

WITH per_event AS (
  SELECT e.event_id, e.category, e.capacity,
         COALESCE((SELECT SUM(o.tickets) FROM TicketOrder o
                   WHERE o.event_id = e.event_id AND o.status = 'confirmed'), 0) AS sold
  FROM LiveEvent e
)
SELECT category, COUNT(*) AS events, SUM(sold) AS tickets_sold, SUM(capacity) AS capacity,
       ROUND(100 * SUM(sold) / SUM(capacity), 2) AS sell_through_pct
FROM per_event
GROUP BY category
ORDER BY sell_through_pct DESC, category

Another way

SELECT c.category, c.events, COALESCE(s.sold, 0) AS tickets_sold, c.capacity,
       ROUND(100 * COALESCE(s.sold, 0) / c.capacity, 2) AS sell_through_pct
FROM (SELECT category, COUNT(*) AS events, SUM(capacity) AS capacity FROM LiveEvent GROUP BY category) c
LEFT JOIN (
  SELECT e.category, SUM(o.tickets) AS sold
  FROM LiveEvent e JOIN TicketOrder o ON o.event_id = e.event_id
  WHERE o.status = 'confirmed'
  GROUP BY e.category
) s ON s.category = c.category
ORDER BY sell_through_pct DESC, c.category ASC

← Zomato: Blinkit Stock-Outs Lasting a Day or More · Zomato: Restaurants Buying From Hyperpure Every Month of Q1 →