Create OHLC Aggregates from Tick Data in Python

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.

10-Second OHLC with Forward-Filled Gaps

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.

Table price_stream

column	type	constraints
timestamp	INTEGER	NOT NULL
price	INTEGER	NOT NULL

Sample data:

price	timestamp
3	0
2	1
4	2
10	3
10	4
10	5
10	6
10	7
10	8
11	8
10	9
10	10

Expected output

Ten-second OHLC buckets, including forward-filled empty buckets between the minimum and maximum observed bucket.

bucket_start	first_price	last_price	max_price	min_price
0	3	10	11	2
10	10	10	10	10

Hints

1. PostgreSQL integer division uses / when both operands are integers.
2. Use ROW_NUMBER() to choose first and last prices per bucket.
3. Generate complete bucket starts with a recursive CTE, then forward-fill missing buckets.