As-of join: price at trade time

Pandas data engineering interview problem. Difficulty: intermediate. Pattern: Merges. About 16 minutes. Part of the Pro drill bank.

Match each stock trade with the most recent price quote available at or before the execution timestamp. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

Given two DataFrames trades and quotes: trades contains: trade_id, symbol, time (timestamp), shares quotes contains: symbol, time (timestamp), price Attach the latest quote price for the matching symbol at or immediately prior to each trade's execution time. If no prior quote exists for that symbol, the price remains missing (null). Output columns: trade_id, symbol, time, shares, price. Sort by trade_id and reset the index. Assign the final DataFrame to result.

Requirements

• Match on closest prior quote time per symbol.
• Sort by trade_id and reset index.

Constraints

• time column is datetime64[ns].
• trade_id is a unique integer identifier.

Examples

Input: trades trade_id | symbol | time | shares 1 | AAPL | 2024-01-01 10:00:05 | 10 2 | AAPL | 2024-01-01 10:00:20 | 20 3 | GOOG | 2024-01-01 10:00:25 | 15 4 | AAPL | 2024-01-01 10:00:40 | 30 quotes symbol | time | price AAPL | 2024-01-01 10:00:00 | 150.0 AAPL | 2024-01-01 10:00:15 | 150.5 GOOG | 2024-01-01 10:00:20 | 2800.0 AAPL | 2024-01-01 10:00:30 | 151.0 Output: trade_id | symbol | time | shares | price 1 | AAPL | 2024-01-01T10:00:05 | 10 | 150 2 | AAPL | 2024-01-01T10:00:20 | 20 | 150.5 3 | GOOG | 2024-01-01T10:00:25 | 15 | 2800 4 | AAPL | 2024-01-01T10:00:40 | 30 | 151 Each trade picks up the latest quote recorded for that ticker at or before trade execution time.

Topics: lakebench, pandas, merge_asof, temporal.

More interview problems · All interview problems · Learn data engineering