CREATE TABLE IF NOT EXISTS trades (
    symbol SYMBOL CAPACITY 2048 CACHE,
    side SYMBOL CAPACITY 4 CACHE,
    price DOUBLE,
    amount DOUBLE,
    timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY HOUR;

INSERT INTO trades SELECT
    rnd_symbol_zipf(1_000, 2.0) AS symbol,
    rnd_symbol('buy', 'sell') as side,
    rnd_double() * 20 + 10 AS price,
    rnd_double() * 20 + 10 AS amount,
    generate_series as timestamp
  FROM generate_series('2025-01-01', '2025-01-01T04:00:00', '1720u');

CREATE TABLE IF NOT EXISTS prices (
    ts TIMESTAMP,
    sym SYMBOL CAPACITY 1024,
    bid DOUBLE,
    ask DOUBLE
  ) timestamp(ts) PARTITION BY HOUR BYPASS WAL;

INSERT INTO prices
  SELECT
      '2024-12-31T23'::timestamp + (600*x) + rnd_long(-20, 20, 0) as ts,
      rnd_symbol_zipf(1_000, 2.0),
      rnd_double() * 10.0 + 5.0,
      rnd_double() * 10.0 + 5.0
      FROM long_sequence(40_000_000);

SELECT t.*, avg(p.bid) avg_big, avg(p.ask) avg_ask
FROM (trades WHERE timestamp BETWEEN '2025-01-02' AND '2025-01-02T04:00:00') t
WINDOW JOIN prices p ON p.sym = t.symbol
  RANGE BETWEEN 1 second PRECEDING and 1 second FOLLOWING LIMIT 100;