Resale Listings Marked Up Above Their Event's Average
Fans can resell tickets on the platform's marketplace. A listing's markup is ask_price - face_value.
- Difficulty: Medium
- Topics: Subqueries
- Dialect: MySQL
- Problem: #86
Problem statement
Fans can resell tickets on the platform's marketplace. A listing's markup is ask_price - face_value. The trust team reviews listings whose markup is strictly greater than the average markup of all listings for the same event.
Return listing_id, event_id, seat_no and markup of those listings, ordered by event_id, then listing_id. An event with a single listing never has one above its own average.
Tables
Table: resale_listings
| Column | Type |
|---|---|
| listing_id | int |
| event_id | int |
| seat_no | varchar |
| face_value | int |
| ask_price | int |
| listed_on | date |
Primary key: listing_id.
One row per resale listing; prices in rupees. ask_price may be below the face value (a discount, negative markup).
Examples
Example 1
resale_listings
| listing_id | event_id | seat_no | face_value | ask_price | listed_on |
|---|---|---|---|---|---|
| 1 | 10 | A-14 | 2000 | 3500 | 2025-03-02 |
| 2 | 10 | A-15 | 2000 | 2400 | 2025-03-02 |
| 3 | 10 | B-02 | 1500 | 1500 | 2025-03-04 |
| 4 | 11 | G-30 | 999 | 1999 | 2025-03-05 |
| 5 | 12 | F-01 | 4000 | 3600 | 2025-03-06 |
| 6 | 12 | F-02 | 4000 | 5000 | 2025-03-06 |
| 7 | 12 | F-03 | 4000 | 4300 | 2025-03-07 |
Output
| listing_id | event_id | seat_no | markup |
|---|---|---|---|
| 1 | 10 | A-14 | 1500 |
| 6 | 12 | F-02 | 1000 |
How to solve Resale Listings Marked Up Above Their Event's Average
Each listing is compared with a statistic of its own group, which is what a correlated subquery expresses: for the outer row l, compute AVG(ask_price - face_value) over the listings x with the same event_id, and keep l when its markup is strictly larger.
The average includes the listing itself, so a listing alone on its event equals its own average and is never kept; neither is any listing of an event where every markup is the same. Negative markups (discounts) simply pull the average down.
Two alternatives avoid recomputing the average per row. One aggregates once per event in a derived table and joins it back; the other uses SUM … OVER (PARTITION BY event_id) and COUNT … OVER to attach the group's totals to every row. Both compare markup * n > total_markup, which is the same inequality multiplied by the positive count, so no fractional average is ever formed. The correlated form is quadratic per event in the worst case; the other two are a single pass plus a sort.
Reference solution (MySQL)
SELECT l.listing_id, l.event_id, l.seat_no, l.ask_price - l.face_value AS markup
FROM resale_listings l
WHERE l.ask_price - l.face_value > (
SELECT AVG(x.ask_price - x.face_value) FROM resale_listings x WHERE x.event_id = l.event_id
)
ORDER BY l.event_id, l.listing_idAnother way
SELECT l.listing_id, l.event_id, l.seat_no, l.ask_price - l.face_value AS markup
FROM resale_listings l
JOIN (SELECT event_id, SUM(ask_price - face_value) AS total_markup, COUNT(*) AS n FROM resale_listings GROUP BY event_id) a ON a.event_id = l.event_id
WHERE (l.ask_price - l.face_value) * a.n > a.total_markup
ORDER BY l.event_id, l.listing_idAnother way
SELECT listing_id, event_id, seat_no, markup FROM (
SELECT listing_id, event_id, seat_no, ask_price - face_value AS markup,
SUM(ask_price - face_value) OVER (PARTITION BY event_id) AS total_markup,
COUNT(*) OVER (PARTITION BY event_id) AS n
FROM resale_listings
) t WHERE markup * n > total_markup
ORDER BY event_id, listing_id← Concert Sell-Through and Sales Status · Books With the Longest Reservation Queue →