Create OHLC Aggregates from Tick Data in Python
Company: Robinhood
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
price_stream
+-----------+-------+
| timestamp | price |
+-----------+-------+
| 0 | 3 |
| 1 | 2 |
| 2 | 4 |
| 3 | 10 |
| 8 | 11 |
+-----------+-------+
##### Scenario
Streaming trading application receives tick data as "price:timestamp" pairs. You must generate 10-second OHLC aggregates and forward-fill gaps.
##### Question
Write a Python function that
parses an input string like "3:0,2:1,4:2,10:3,10:4,10:5,10:6,10:7,10:8,10:9,10:10,11:8";
buckets rows into [0-
10), [10-
20)… intervals by timestamp;
for every interval outputs first_price, last_price, min_price, max_price;
if an interval has no rows, copy the previous interval’s last_price into every statistic for the missing bucket.
##### Hints
Use floor(timestamp/
10) to find a bucket, keep running dict {bucket: [first,last,min,max]}, track last seen price for forward-fill.
Overview: This question evaluates a candidate's ability to perform time-series bucketing and OHLC aggregation from tick data, testing skills in data parsing, stateful aggregation, and handling missing-interval forward-filling; it falls under Data Manipulation (SQL/Python) and assesses practical application rather than purely conceptual understanding.
Given `price_stream(timestamp INT, price INT)` tick data, compute 10-second OHLC buckets.
For every 10-second interval from the minimum bucket present in the data through the maximum bucket present in the data, return:
- `bucket_start`
- `first_price`: earliest tick price in the bucket by timestamp, using lower price as a deterministic tie-breaker
- `last_price`: latest tick price in the bucket by timestamp, using higher price as a deterministic tie-breaker
- `min_price`
- `max_price`
If a bucket has no ticks, forward-fill all four price columns from the previous bucket's `last_price`. Order by `bucket_start`.
Tables
price_stream(timestamp INTEGER, price INTEGER)
Hints
- PostgreSQL integer division uses / when both operands are integers.
- Use ROW_NUMBER() to choose first and last prices per bucket.