Resale Listings Marked Up Above Their Event's Average

Medium
86.

Resale Listings Marked Up Above Their Event's Average

MediumSubqueries

MySQL

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

ColumnType
listing_idint
event_idint
seat_novarchar
face_valueint
ask_priceint
listed_ondate

Primary key: listing_id.

One row per resale listing; prices in rupees. ask_price may be below the face value (a discount, negative markup).

Example

The rows must come back in the order shown.

resale_listings

listing_idevent_idseat_noface_valueask_pricelisted_on
110A-14200035002025-03-02
210A-15200024002025-03-02
310B-02150015002025-03-04
411G-3099919992025-03-05
512F-01400036002025-03-06
612F-02400050002025-03-06
712F-03400043002025-03-07

Output

listing_idevent_idseat_nomarkup
110A-141500
612F-021000
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.

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

ColumnType
listing_idint
event_idint
seat_novarchar
face_valueint
ask_priceint
listed_ondate

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_idevent_idseat_noface_valueask_pricelisted_on
110A-14200035002025-03-02
210A-15200024002025-03-02
310B-02150015002025-03-04
411G-3099919992025-03-05
512F-01400036002025-03-06
612F-02400050002025-03-06
712F-03400043002025-03-07

Output

listing_idevent_idseat_nomarkup
110A-141500
612F-021000

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_id

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

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