Create OHLC Aggregates from Tick Data in Python

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Robinhood.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Robinhood during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Create OHLC Aggregates from Tick Data in Python