Build the auction status table
Company: Point72
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Take-home Project
You are given three tables for an auction system.
**Table: `buyers`**
- `id` INT PRIMARY KEY
- `username` VARCHAR
**Table: `lots`**
- `id` INT PRIMARY KEY
- `name` VARCHAR
- `starting_price` DECIMAL(10,2)
- `bid_step` DECIMAL(10,2)
**Table: `bids`**
- `buyer_id` INT
- `lot_id` INT
- `bid_time` TIMESTAMP
**Relationships**
- `bids.buyer_id` references `buyers.id`
- `bids.lot_id` references `lots.id`
Assume `bid_time` is stored in UTC, and for each lot the latest bid is uniquely determined by `bid_time`.
Write a SQL query that returns **one row per lot** with the following columns:
- `name`: the lot name
- `starting_price`: the lot's starting price
- `bids`: the total number of bids placed on that lot
- `current_price`: `starting_price + bid_step * number_of_bids`
- `current_winner`: the username of the buyer who placed the most recent bid on that lot
Include lots that have received no bids. For such lots, return:
- `bids = 0`
- `current_price = starting_price`
- `current_winner = NULL`
Quick Answer: This question evaluates SQL data manipulation skills and relational querying competencies, including joins, aggregations, NULL handling, and identifying the most recent event per group, within the Data Manipulation (SQL/Python) domain for a Data Scientist role.