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
| Column | Type |
|---|---|
| event_id | int |
| title | varchar |
| category | enum(comedy, music, workshop, sports) |
| capacity | int |
Primary key: event_id.
One row per event listed on District; capacity is the tickets on sale.
Table: TicketOrder
| Column | Type |
|---|---|
| order_id | int |
| event_id | int |
| tickets | int |
| status | enum(confirmed, cancelled) |
Primary key: order_id.
One row per order of one or more tickets.
Examples
Example 1
LiveEvent
| event_id | title | category | capacity |
|---|---|---|---|
| 1 | Laugh Riot Live | comedy | 200 |
| 2 | Open Mic Night | comedy | 100 |
| 3 | Indie Nights | music | 500 |
| 4 | Pottery Basics | workshop | 100 |
| 5 | Bass Drop Fest | music | 300 |
| 6 | Sketching 101 | workshop | 100 |
TicketOrder
| order_id | event_id | tickets | status |
|---|---|---|---|
| 1 | 1 | 100 | confirmed |
| 2 | 1 | 50 | confirmed |
| 3 | 1 | 30 | cancelled |
| 4 | 2 | 50 | confirmed |
| 5 | 3 | 400 | confirmed |
| 6 | 4 | 60 | confirmed |
| 7 | 4 | 40 | confirmed |
Output
| category | events | tickets_sold | capacity | sell_through_pct |
|---|---|---|---|---|
| comedy | 2 | 200 | 300 | 66.67 |
| music | 2 | 400 | 800 | 50 |
| workshop | 2 | 100 | 200 | 50 |
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, categoryAnother 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 →