Katkat
Back

Build the auction status table

Point72Data Scientist · Online Assessment
HardData ScientistOnline AssessmentData Manipulation (SQL/Python)

Last updated

This problem tests advanced SQL skills, specifically the ability to perform window functions, handle state transitions, and aggregate time-series data to reconstruct the final status of entities.

Problem Statement

You are working with an auction_events table that logs all changes in the status of auctions. Your goal is to generate a summary report showing the final state of every auction in the database.

Table Schema: auction_events

ColumnTypeDescription
event_idINTUnique identifier for the event
auction_idINTIdentifier for the auction item
statusVARCHARThe status (e.g., 'OPEN', 'PENDING', 'CLOSED', 'CANCELLED')
event_timeTIMESTAMPWhen the status change occurred

Task

Write a SQL query to return a table containing auction_id, final_status, and total_duration_in_hours.

  1. final_status is the status of the auction at the time of the most recent event.
  2. total_duration_in_hours is the difference between the most recent event time and the earliest event time for each auction_id, expressed in hours. If an auction only has one event, the duration is 0.

Example

Input:

event_idauction_idstatusevent_time
1101OPEN2023-01-01 10:00:00
2101CLOSED2023-01-01 14:00:00
3102OPEN2023-01-01 09:00:00

Output:

auction_idfinal_statustotal_duration_in_hours
101CLOSED4.0
102OPEN0.0

Constraints

  • auction_id is not unique.
  • event_time is strictly increasing for a single auction_id.
  • Ignore auctions that have no events (though all IDs in the table have at least one event).

Hints

Hint 1

Consider using `GROUP BY` to find the `MIN` and `MAX` timestamps for each `auction_id`.

Hint 2

To find the `final_status`, you can use the `ROW_NUMBER()` window function partitioned by `auction_id` and ordered by `event_time` descending.

Hint 3

Alternatively, you can join the result of an aggregation back to the original table on both `auction_id` and the calculated `max_event_time`.

Follow-up questions

  • How would you modify your query to calculate the time spent in each specific status for every auction?
  • If the `auction_events` table contained millions of rows per day, what indexing strategy would you propose to optimize this query?
  • How would you handle the data if an auction was 'REOPENED', meaning the final status might be 'OPEN' after being 'CLOSED'?