Search code examples
sqlquestdb

How to generate a candle stick chart with QuestDB?

I have a table with market data. The table structure looks like this:

symbol side price amount timestamp
BTC-USD buy 35831.56 0.00051151 2023-11-15T10:42:11.754626Z

I need to aggregate prices for a given symbol by time intervals. In each time interval, I need to know: the opening price, closing price, maximum price and minimum price.

Solution

  • Open would be first price, close would be last price, Use sample.

    SELECT 
    TIMESTAMP,
    FIRST(PRICE) AS OPEN_PRICE,
    LAST(PRICE) AS CLOSE_PRICE,
    MAX(PRICE) AS MAX_PRICE,
    MIN(PRICE) AS MIN_PRICE,
FROM trades
SAMPLE BY 1h