Auction Buys Above Their Franchise's Average Price
After each year's player auction a franchise's analyst reviews the signings that cost more than the franchise's average price that year.
- Difficulty: Medium
- Topics: Subqueries
- Dialect: MySQL
- Problem: #461
Problem statement
After each year's player auction a franchise's analyst reviews the signings that cost more than the franchise's average price that year. The average is over every player the same franchise bought in the same auction_year.
Return the columns franchise_name, auction_year, player_name and price_lakh for every such signing. A franchise that bought one player that year has nothing above its own average. Return the rows in any order.
Tables
Table: Franchise
| Column | Type |
|---|---|
| franchise_id | int |
| franchise_name | varchar |
| home_city | varchar |
Primary key: franchise_id.
One row per franchise in the league.
Table: AuctionSale
| Column | Type |
|---|---|
| sale_id | int |
| auction_year | int |
| player_name | varchar |
| franchise_id | int |
| price_lakh | int |
Primary key: sale_id.
One row per player sold, with the hammer price in lakh rupees. franchise_id is always in Franchise.
Examples
Example 1
Franchise
| franchise_id | franchise_name | home_city |
|---|---|---|
| 1 | Mumbai Monarchs | Mumbai |
| 2 | Chennai Cheetahs | Chennai |
| 3 | Jaipur Jaguars | Jaipur |
AuctionSale
| sale_id | auction_year | player_name | franchise_id | price_lakh |
|---|---|---|---|---|
| 1 | 2024 | Liam Brooks | 1 | 1200 |
| 2 | 2024 | Rohan Iyer | 1 | 300 |
| 3 | 2024 | Kabir Khan | 1 | 450 |
| 4 | 2024 | Noah Fernando | 2 | 800 |
| 5 | 2024 | Dev Mehta | 2 | 800 |
| 6 | 2025 | Arjun Nair | 1 | 650 |
| 7 | 2025 | Vikram Rao | 3 | 975 |
| 8 | 2025 | Ishaan Bose | 3 | 200 |
Output
| franchise_name | auction_year | player_name | price_lakh |
|---|---|---|---|
| Mumbai Monarchs | 2024 | Liam Brooks | 1200 |
| Jaipur Jaguars | 2025 | Vikram Rao | 975 |
How to solve Auction Buys Above Their Franchise's Average Price
The comparison value changes from row to row: each sale is measured against its own franchise's average in its own year. That is the textbook use of a correlated subquery — (SELECT AVG(price_lakh) FROM AuctionSale t WHERE t.franchise_id = s.franchise_id AND t.auction_year = s.auction_year) is evaluated with the outer row's values, and the sale is kept when its price is strictly above it.
Correlating on both columns matters: a franchise's 2024 spree should not lift the bar for its 2025 buys. A franchise with a single signing in a year has an average equal to that price, so > excludes it; two players bought at the same price (800 and 800) are both equal to the average and neither is listed.
The same averages can be computed once — a GROUP BY franchise_id, auction_year derived table joined back, or AVG(...) OVER (PARTITION BY franchise_id, auction_year) beside every row. Those read the table a fixed number of times, while a naive correlated plan rescans it per row; most optimisers turn the correlated form into the grouped join anyway.
Reference solution (MySQL)
SELECT f.franchise_name, s.auction_year, s.player_name, s.price_lakh
FROM AuctionSale s
JOIN Franchise f ON f.franchise_id = s.franchise_id
WHERE s.price_lakh > (
SELECT AVG(t.price_lakh) FROM AuctionSale t
WHERE t.franchise_id = s.franchise_id AND t.auction_year = s.auction_year
)Another way
SELECT f.franchise_name, s.auction_year, s.player_name, s.price_lakh FROM AuctionSale s JOIN Franchise f ON f.franchise_id = s.franchise_id JOIN (SELECT franchise_id, auction_year, AVG(price_lakh) AS avg_price FROM AuctionSale GROUP BY franchise_id, auction_year) a ON a.franchise_id = s.franchise_id AND a.auction_year = s.auction_year WHERE s.price_lakh > a.avg_priceAnother way
SELECT f.franchise_name, w.auction_year, w.player_name, w.price_lakh FROM (SELECT s.*, AVG(price_lakh) OVER (PARTITION BY franchise_id, auction_year) AS avg_price FROM AuctionSale s) w JOIN Franchise f ON f.franchise_id = w.franchise_id WHERE w.price_lakh > w.avg_price← Fantasy Gameweek Scores With the Captain's Double Points · Share of Seats Booked Two Weeks Early for Each Match →