SELECT
  timestamp, symbol,
  first(price),
  last(price),
  CASE
   when symbol = 'BTC-USD' THEN first(price)
   else last(price)
  END
FROM (trades where timestamp in today())
SAMPLE BY 1h
ALIGN TO CALENDAR WITH OFFSET '-00:15';

SELECT
    t.symbol,
    t.timestamp,
    t.side,
    t.price AS exec_price,
    avg(p.bid_price) AS avg_bid,
    avg(p.ask_price) AS avg_ask,
    avg(p.ask_price - p.bid_price) AS avg_spread,
    -- Slippage: did you pay more than ask (buy) or receive less than bid (sell)?
    CASE 
        WHEN t.side = 'buy' THEN t.price - avg(p.ask_price)
        ELSE avg(p.bid_price) - t.price
    END AS slippage
FROM trades t
WINDOW JOIN prices p
    ON (t.symbol = p.symbol)
    RANGE BETWEEN 5 minutes PRECEDING AND 0 seconds PRECEDING
    EXCLUDE PREVAILING
ORDER BY t.timestamp;