Ad Auction: Second Price
Google
Problem Statement
Google uses a variation of the Second-Price Auction model. In this model, the highest bidder wins the ad slot, but the price they pay is the bid amount of the second-highest bidder.
Write a query to identify the winner of each auction and the price they must pay. If multiple bidders have the same bid amount and it is the highest, the "second price" is still that same amount. If there is only one bidder for a slot, the price paid is 0 (assume no reserve price).
Rules:
- Output columns: ad_slot_id, winner_id, price_paid.
- The winner_id is the user_id with the highest bid.
- In case of a tie for the highest bid, the user_id with the lowest numerical value is the winner.
- price_paid is the bid_amount of the second-highest bidder.
- Result must be sorted by ad_slot_id ascending.
Tests your understanding of
Window Functions, Ranking and Marketplace Logic
Input Tables
bids
| bid_id(INTEGER) | ad_slot_id(INTEGER) | user_id(INTEGER) | bid_amount(DECIMAL) |
|---|---|---|---|
| 1 | 1001 | 10 | 5.5 |
| 2 | 1001 | 11 | 4.25 |
| 3 | 1001 | 12 | 6 |
| 4 | 1002 | 20 | 10 |
| 5 | 1002 | 21 | 10 |
| 6 | 1003 | 30 | 2.5 |
| 7 | 1004 | 40 | 1 |
| 8 | 1004 | 41 | 0.5 |
Expected Output
| ad_slot_id(INTEGER) | winner_id(INTEGER) | price_paid(DECIMAL) |
|---|---|---|
| 1001 | 12 | 5.5 |
| 1002 | 20 | 10 |
| 1003 | 30 | 0 |
| 1004 | 40 | 0.5 |
Tags
HardWindow FunctionsRankingMarketplace Logic
25-35 min
45%