SQL Join for Data Retrieval | Dataford Interview Questions

Barrett-Jackson needs a lot-level view of bidding activity for its Scottsdale auction. Write a PostgreSQL query that combines auction lot records with their bids and preserves lots that have received no valid bids.

Requirements

Return only lots from the Barrett-Jackson Scottsdale auction held on January 25, 2025.
Include every qualifying lot, including lots without bids.
Return the lot number, vehicle title, reserve price, count of non-null bids, and highest bid amount.
Add bidding_status: No bids, No reserve, Reserve met, or Below reserve. Sort by lot number ascending.

Schema

auction_lots

Column	Type	Description
lot_idPK	INTEGER	Unique auction lot identifier
lot_number	INTEGER	Public auction lot number
auction_name	VARCHAR(120)	Name of the Barrett-Jackson auction event
auction_date	DATE	Date of the auction
vehicle_title	VARCHAR(200)	Vehicle title and description
reserve_price	NUMERIC(12,2)	Minimum acceptable sale price

bids

Column	Type	Description
bid_idPK	INTEGER	Unique bid identifier
lot_id	INTEGER	Auction lot receiving the bid
bidder_id	VARCHAR(40)	Bidder identifier
amount	NUMERIC(12,2)	Bid amount
bid_time	TIMESTAMP	Timestamp when the bid was submitted

Your question is SQL Join for Data Retrieval. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem