Auction Buys Above Their Franchise's Average Price

Medium
461.

Auction Buys Above Their Franchise's Average Price

MediumSubqueries

MySQL

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

ColumnType
franchise_idint
franchise_namevarchar
home_cityvarchar

Primary key: franchise_id.

One row per franchise in the league.

Table: AuctionSale

ColumnType
sale_idint
auction_yearint
player_namevarchar
franchise_idint
price_lakhint

Primary key: sale_id.

One row per player sold, with the hammer price in lakh rupees. franchise_id is always in Franchise.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Franchise

franchise_idfranchise_namehome_city
1Mumbai MonarchsMumbai
2Chennai CheetahsChennai
3Jaipur JaguarsJaipur

AuctionSale

sale_idauction_yearplayer_namefranchise_idprice_lakh
12024Liam Brooks11200
22024Rohan Iyer1300
32024Kabir Khan1450
42024Noah Fernando2800
52024Dev Mehta2800
62025Arjun Nair1650
72025Vikram Rao3975
82025Ishaan Bose3200

Output

franchise_nameauction_yearplayer_nameprice_lakh
Mumbai Monarchs2024Liam Brooks1200
Jaipur Jaguars2025Vikram Rao975
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.

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

ColumnType
franchise_idint
franchise_namevarchar
home_cityvarchar

Primary key: franchise_id.

One row per franchise in the league.

Table: AuctionSale

ColumnType
sale_idint
auction_yearint
player_namevarchar
franchise_idint
price_lakhint

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_idfranchise_namehome_city
1Mumbai MonarchsMumbai
2Chennai CheetahsChennai
3Jaipur JaguarsJaipur

AuctionSale

sale_idauction_yearplayer_namefranchise_idprice_lakh
12024Liam Brooks11200
22024Rohan Iyer1300
32024Kabir Khan1450
42024Noah Fernando2800
52024Dev Mehta2800
62025Arjun Nair1650
72025Vikram Rao3975
82025Ishaan Bose3200

Output

franchise_nameauction_yearplayer_nameprice_lakh
Mumbai Monarchs2024Liam Brooks1200
Jaipur Jaguars2025Vikram Rao975

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_price

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