Query candlestick views

So far in this tutorial, you have created the schema to store tick data, and set up multiple candlestick views. In this section, use some example candlestick queries and see how they can be represented in data visualizations.

1-min BTC/USD candlestick chart

Start with a one_min_candle continuous aggregate, which contains 1-min candlesticks:

SELECT * FROM one_min_candle
WHERE symbol = 'BTC/USD' AND bucket >= NOW() - INTERVAL '24 hour'
ORDER BY bucket

1-hour BTC/USD candlestick chart

If you find that 1-min candlesticks are too granular, you can query the one_hour_candle continuous aggregate containing 1-hour candlesticks:

SELECT * FROM one_hour_candle
WHERE symbol = 'BTC/USD' AND bucket >= NOW() - INTERVAL '2 day'
ORDER BY bucket

1-day BTC/USD candlestick chart

To zoom out even more, query the one_day_candle continuous aggregate, which has one-day candlesticks:

SELECT * FROM one_day_candle
WHERE symbol = 'BTC/USD' AND bucket >= NOW() - INTERVAL '14 days'
ORDER BY bucket

BTC vs. ETH 1-day price changes delta line chart

You can calculate and visualize the price change differences between two symbols. In a previous example, you saw how to do this by comparing the opening and closing prices. But what if you want to compare today's closing price with yesterday's closing price? Here's an example how you can achieve this by using the LAG() window function on an already existing candlestick view:

SELECT *, ("close" - LAG("close", 1) OVER (PARTITION BY symbol ORDER BY bucket)) / "close" AS change_pct
FROM one_day_candle
WHERE symbol IN ('BTC/USD', 'ETH/USD') AND bucket >= NOW() - INTERVAL '14 days'
ORDER BY bucket