Candles cookbook

Ready market-data queries: candles from minute data and from trades, daily change, top gainers, volume leaders, 52-week high and low, moving averages and filled gaps.

• Category: Time-series
• Keywords: cookbook, recipes, candles, TA folders, indicator table, candles by timer, precompile, ta-folders.mjs, OHLC, candlestick, chart data, 5-minute candles, hourly candles, weekly candles, monthly candles, daily change, percent change, change %, top gainers, top losers, movers, volume leaders, turnover, broker turnover, 52-week high, 52-week low, moving average, SMA, EMA, RSI, crossover, golden cross, VWAP, gaps, missing minutes, FILL(PREV), stock market
• See also: SAMPLE BY, LATEST ON, Technical indicators, Views (materialized-view), Timers, Trading calendars, Window and series functions, Aggregate functions, Time and time zones (nepal-time)

Copy these queries and change the symbol, the dates and the periods. All of them run on the sample market data (the prices are made up).

Most recipes use three building blocks:

• SAMPLE BY makes candles: first(open), max(high), min(low), last(close), sum(volume).
• LATEST ON finds the newest row of each symbol.
• Series functions (lag, sma, ema, rsi, ...) with OVER (PARTITION BY symbol ORDER BY time) look at the rows before (window functions, indicators).

Candles from minute data

Syntax:

SELECT time, symbol,
       first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close,
       sum(volume) AS volume
FROM ochl_minutes
WHERE time IN 'day'
SAMPLE BY 5m | 15m | 30m
ORDER BY symbol, time

Makes bigger candles from 1-minute candles. Open is the first open, close the last close, high the biggest high, low the smallest low, volume the sum.

• Notes:
  • For 5, 15 and 30 minutes, UTC and Nepal buckets are the same. For hours use the next recipe.
  • Always filter by time: the query then reads only the partitions it needs.
  • A bucket with no trades gives no candle. See filling gaps.

Example: 5-minute candles of both symbols, first 3 of each (sample data: sample)

SELECT time, symbol,
       first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close,
       sum(volume) AS volume
FROM ochl_minutes
WHERE time IN '2026-09-09' AND time < '2026-09-09T11:15:00+05:45'
SAMPLE BY 5m
ORDER BY symbol, time;

Result:

(6 rows)

Example: The newest 3 candles first (for a chart that loads backwards) (sample data: sample)

SELECT time, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST' AND time IN '2026-09-10'
SAMPLE BY 15m
ORDER BY time DESC
LIMIT 3;

Result:

(3 rows)

Hourly candles on a local clock (Nepal)

Syntax:

SELECT time, first(open), max(high), min(low), last(close), sum(volume)
FROM ochl_minutes
WHERE symbol = '...' AND time IN 'day'
SAMPLE BY 1h TIME ZONE 'Asia/Kathmandu'

The sample market opens at 11:00 local time in Nepal (05:15 UTC). The same idea works for any zone, for example America/New_York. Plain SAMPLE BY 1h makes UTC hours, which cut the trading day at the wrong places. TIME ZONE 'Asia/Kathmandu' makes the hours 11:00, 12:00, 13:00 and 14:00 Nepal time.

• Notes:
  • The result times are in UTC: 05:15Z is 11:00 in Nepal.
  • vwap(price, volume) is the volume-weighted average price of the candle.
  • QuestDB's SAMPLE BY 1h ALIGN TO CALENDAR WITH OFFSET '00:15' gives the same buckets and also runs.

Example: Hourly candles with VWAP for both symbols (sample data: sample)

SELECT time, symbol,
       first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close,
       sum(volume) AS volume, round(vwap(close, volume), 2) AS vwap
FROM ochl_minutes
WHERE time IN '2026-09-10'
SAMPLE BY 1h TIME ZONE 'Asia/Kathmandu'
ORDER BY symbol, time;

Result:

(8 rows)

Daily candles from trade log trades

Syntax:

SELECT date, symbol,
       first(rate) AS open, max(rate) AS high, min(rate) AS low, last(rate) AS close,
       sum(quantity) AS volume, sum(amount) AS turnover, count(*) AS trades
FROM trade_log
SAMPLE BY 1d

Makes candles from single trades. The price is rate, the volume is quantity, the turnover is amount (quantity × rate).

• Notes:
  • The trade log has only the trade date, not the time of each trade. All trades of a day have the same time, so first and last follow the order the rows are stored in (here: by transaction_id). With a real trade time column, first and last follow the time.
  • To compare with the official candles, join the result with ochl_day on symbol and date.

Example: Daily candles and turnover from trades (sample data: sample)

SELECT date, symbol,
       first(rate) AS open, max(rate) AS high, min(rate) AS low, last(rate) AS close,
       sum(quantity) AS volume, round(sum(amount), 2) AS turnover, count(*) AS trades
FROM trade_log
SAMPLE BY 1d
ORDER BY symbol, date;

Result:

(7 rows)

Weekly candles, Sunday to Thursday

Syntax:

SELECT time, symbol, first(open), max(high), min(low), last(close), sum(volume)
FROM ochl_day
WHERE time IN 'year'
SAMPLE BY 1w WEEK START SUNDAY

The sample market trades Sunday to Thursday, as some exchanges do. WEEK START SUNDAY makes each week start on Sunday, so one trading week is one candle.

• Notes: the daily rows are at 00:00 UTC, so do not add TIME ZONE here (the week would be stamped Saturday 18:15 UTC).

Example: Weekly candles of every symbol (sample data: sample)

SELECT time, symbol,
       first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close,
       sum(volume) AS volume
FROM ochl_day
WHERE time IN '2026'
SAMPLE BY 1w WEEK START SUNDAY
ORDER BY symbol, time;

Result:

(9 rows)

Monthly and yearly candles

Syntax:

SAMPLE BY 1M      -- month: capital M
SAMPLE BY 1y      -- year

1M is a calendar month; small 1m is one minute. Months without rows are not shown.

Example: Monthly candles of COAST (sample data: sample)

SELECT time, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close, sum(volume) AS volume
FROM ochl_day
WHERE symbol = 'COAST'
SAMPLE BY 1M;

Result:

(3 rows)

Example: Yearly high, low and last close of every symbol (sample data: sample)

SELECT time, symbol, max(high) AS year_high, min(low) AS year_low, last(close) AS year_close
FROM ochl_day
SAMPLE BY 1y
ORDER BY symbol, time;

Result:

(6 rows)

Daily change %

Syntax:

SELECT time, symbol, close,
       lag(close) OVER (PARTITION BY symbol ORDER BY time) AS prev_close,
       pct_change(close) OVER (PARTITION BY symbol ORDER BY time) AS change_pct
FROM ochl_day

The change from the day before: (close - previous close) / previous close × 100. lag(close) is the close of the row before; pct_change(close) computes the percent for you.

• NULL: the first row of each symbol has no day before, so its change is NULL.
• Notes:
  • Without PARTITION BY symbol, all rows are one series: COAST's first row would use CITY's last close.
  • WHERE runs before lag. With WHERE time IN '2026-09-10' alone there is no day before. Compute in a subquery over more days, and pick the day outside (subqueries).

Example: Change % of the last three days (sample data: sample)

SELECT time, symbol, close,
       lag(close) OVER (PARTITION BY symbol ORDER BY time) AS prev_close,
       round(pct_change(close) OVER (PARTITION BY symbol ORDER BY time), 2) AS change_pct
FROM ochl_day
WHERE time >= '2026-09-08'
ORDER BY symbol, time;

Result:

(9 rows)

Example: Wrong: WHERE keeps one day, so there is no day before (sample data: sample)

SELECT time, symbol, close, round(pct_change(close) OVER (PARTITION BY symbol ORDER BY time), 2) AS change_pct
FROM ochl_day
WHERE time IN '2026-09-10';

Result:

(3 rows)

Top gainers and losers

Syntax:

SELECT symbol, prev_close, close, change_pct
FROM (SELECT time, symbol, close, lag(close) OVER (PARTITION BY symbol ORDER BY time) AS prev_close
      FROM ochl_day WHERE time >= 'a few days before') t
WHERE time IN 'the day'
ORDER BY change_pct DESC
LIMIT 10

Gainers of a day: the change %, biggest first. Losers: the same with ORDER BY change_pct (smallest first).

• Notes:
  • The inner WHERE time >= ... should start a few days before the day you want (weekends and holidays have no rows). Then KDB does not read the whole table.
  • For the market right now, use the live snapshots with LATEST ON (second example).

Example: Top gainers of 2026-09-10 from daily candles (sample data: sample)

SELECT symbol, prev_close, close,
       round((close - prev_close) / prev_close * 100, 2) AS change_pct
FROM (
  SELECT time, symbol, close,
         lag(close) OVER (PARTITION BY symbol ORDER BY time) AS prev_close
  FROM ochl_day
  WHERE time >= '2026-09-01'
) t
WHERE time IN '2026-09-10'
ORDER BY change_pct DESC;

Result:

(3 rows)

Example: Top 2 gainers and top 2 losers right now (live snapshots) (sample data: sample)

SELECT symbol, ltp, pct_change FROM live_data
LATEST ON timestamp PARTITION BY symbol
ORDER BY pct_change DESC LIMIT 2;

SELECT symbol, ltp, pct_change FROM live_data
LATEST ON timestamp PARTITION BY symbol
ORDER BY pct_change LIMIT 2;

Result:

SELECT symbol, ltp, pct_change FROM live_data LATEST ON timestamp PARTITION BY symbol ORDER BY pct_c

(2 rows)

SELECT symbol, ltp, pct_change FROM live_data LATEST ON timestamp PARTITION BY symbol ORDER BY pct_c

(2 rows)

Volume and turnover leaders

Syntax:

SELECT symbol, sum(volume) AS volume FROM ochl_day WHERE time IN 'day' GROUP BY symbol ORDER BY volume DESC LIMIT n
SELECT symbol, sum(quantity), sum(amount) AS turnover FROM trade_log WHERE date IN 'day' GROUP BY symbol ORDER BY turnover DESC

The most traded shares of a day, by volume (number of shares) or by turnover (money).

• Notes: the trade log has buyer and seller brokers, so you can also rank brokers. Each trade counts for its buyer and its seller: UNION ALL the two sides first.

Example: Volume leaders of a day (daily candles) (sample data: sample)

SELECT symbol, volume, close
FROM ochl_day
WHERE time IN '2026-09-10'
ORDER BY volume DESC
LIMIT 3;

Result:

(3 rows)

Example: Turnover leaders of a day (trade log) (sample data: sample)

SELECT symbol, sum(quantity) AS shares, round(sum(amount), 2) AS turnover, count(*) AS trades
FROM trade_log
WHERE date IN '2026-09-10'
GROUP BY symbol
ORDER BY turnover DESC;

Result:

(4 rows)

Example: Brokers with the biggest turnover (bought + sold) (sample data: sample)

SELECT b.name, round(sum(t.amount), 2) AS turnover, count(*) AS trades
FROM (
  SELECT buyer AS broker, amount FROM trade_log WHERE date IN '2026-09-10'
  UNION ALL
  SELECT seller AS broker, amount FROM trade_log WHERE date IN '2026-09-10'
) t
JOIN brokers b ON b.id = t.broker
GROUP BY b.name
ORDER BY turnover DESC
LIMIT 3;

Result:

(3 rows)

52-week high and low

Syntax:

SELECT symbol, max(high) AS high_52w, min(low) AS low_52w
FROM ochl_day
WHERE time > dateadd('d', -365, now())
GROUP BY symbol

The highest high and lowest low of the last 365 days. dateadd('d', -365, t) is the time 365 days before t (date and time functions).

• Notes: the examples use the fixed date 2026-09-10 so the result does not change. For "until today" write now().

Example: 52-week high and low of every symbol (sample data: sample)

SELECT symbol, max(high) AS high_52w, min(low) AS low_52w
FROM ochl_day
WHERE time > dateadd('d', -365, CAST('2026-09-10' AS TIMESTAMP))
GROUP BY symbol
ORDER BY symbol;

Result:

(3 rows)

Example: How far the last close is below the 52-week high (sample data: sample)

SELECT h.symbol, l.close, h.high_52w, h.low_52w,
       round((l.close - h.high_52w) / h.high_52w * 100, 2) AS below_high_pct
FROM (SELECT symbol, max(high) AS high_52w, min(low) AS low_52w
      FROM ochl_day
      WHERE time > dateadd('d', -365, CAST('2026-09-10' AS TIMESTAMP))
      GROUP BY symbol) h
JOIN (SELECT symbol, close FROM ochl_day LATEST ON time PARTITION BY symbol) l ON l.symbol = h.symbol
ORDER BY below_high_pct DESC;

Result:

(3 rows)

Prices after a bonus share or a split jump on the ex-date. To chart or compute indicators over the history without that jump, read the table with ADJUSTED BY.

Moving averages and crossovers

Syntax:

sma(close, n) OVER (PARTITION BY symbol ORDER BY time)
ema(close, n) OVER (PARTITION BY symbol ORDER BY time)
rsi(close, n) OVER (PARTITION BY symbol ORDER BY time)

sma is the simple moving average of the last n rows, ema the exponential one, rsi the Relative Strength Index (0 to 100). See indicator functions for all of them.

• NULL: before there are n rows (for rsi: n + 1), the result is NULL.
• Notes:
  • Use PARTITION BY symbol when the rows have many symbols.
  • Compute over enough history in a subquery, then filter the days you want to show outside.
  • An indicator can be inside another one: lag(sma(close, 3)) OVER (...), cross_above(sma(close, 3), sma(close, 5)) OVER (...). The inner one, written without OVER, takes the outer one's PARTITION BY and ORDER BY; KDB computes it first, in a subquery of its own.

Example: 5-day SMA and EMA, newest rows of CITY (sample data: sample)

SELECT time, symbol, close,
       round(sma(close, 5) OVER (PARTITION BY symbol ORDER BY time), 2) AS sma5,
       round(ema(close, 5) OVER (PARTITION BY symbol ORDER BY time), 2) AS ema5
FROM ochl_day
WHERE symbol = 'CITY' AND time IN '2026'
ORDER BY time DESC
LIMIT 3;

Result:

(3 rows)

Example: RSI of every symbol on the last day (computed over all days) (sample data: sample)

SELECT time, symbol, close, rsi5
FROM (
  SELECT time, symbol, close,
         round(rsi(close, 5) OVER (PARTITION BY symbol ORDER BY time), 1) AS rsi5
  FROM ochl_day
) t
WHERE time IN '2026-09-10'
ORDER BY rsi5 DESC;

Result:

(3 rows)

Example: Crossover in one line: cross_above and cross_below (sample data: sample)

SELECT time, close,
       CASE WHEN cross_above(sma(close, 3), sma(close, 5)) OVER (ORDER BY time) THEN 'BUY'
            WHEN cross_below(sma(close, 3), sma(close, 5)) OVER (ORDER BY time) THEN 'SELL'
            ELSE '' END AS signal
FROM ochl_day
WHERE symbol = 'VOLT' AND time IN '2026'
ORDER BY time DESC
LIMIT 5;

Result:

(5 rows)

Example: Crossover written out: BUY when the 3-day SMA goes above the 5-day SMA (sample data: sample)

SELECT time, close, fast, slow,
       CASE WHEN fast > slow AND prev_fast <= prev_slow THEN 'BUY'
            WHEN fast < slow AND prev_fast >= prev_slow THEN 'SELL'
            ELSE '' END AS signal
FROM (
  SELECT time, close, fast, slow, lag(fast) AS prev_fast, lag(slow) AS prev_slow
  FROM (
    SELECT time, close, round(sma(close, 3), 2) AS fast, round(sma(close, 5), 2) AS slow
    FROM ochl_day
    WHERE symbol = 'VOLT' AND time IN '2026'
  ) a
) b
WHERE time >= '2026-09-01';

Result:

(8 rows)

Indicators on candles

Syntax:

SELECT time, close, sma(close, n) AS sma
FROM (SELECT time, last(close) AS close FROM ... SAMPLE BY interval) candles

To put an indicator on weekly or hourly candles, make the candles in a subquery and compute the indicator outside. In one SELECT with SAMPLE BY, a window function runs over the candles, so it may only use the time bucket, group keys or aggregates, not a raw column like close.

• Notes: the subquery result has a time column, so the series functions outside use its time order.

Example: SAMPLE BY and sma(close) in one SELECT: close is not a candle value (sample data: sample; shows an error)

SELECT time, last(close) AS close, sma(close, 2) AS sma2
FROM ochl_day
WHERE symbol = 'CITY'
SAMPLE BY 1w WEEK START SUNDAY;

Result:

Error: line 1, column 40: column 'close' is used by a window function in a query with aggregates, so it must be a key of the groups: add it to the SELECT list or to GROUP BY

Example: The fix: hourly Nepal candles inside, a 2-candle SMA outside (sample data: sample)

SELECT time, close, round(sma(close, 2), 2) AS sma2
FROM (
  SELECT time, last(close) AS close
  FROM ochl_minutes
  WHERE symbol = 'CITY' AND time IN '2026-09-10'
  SAMPLE BY 1h TIME ZONE 'Asia/Kathmandu'
) h;

Result:

(4 rows)

Candle and indicator tables kept by a timer

Syntax:

-- 1. candles, kept up to date after every insert
CREATE MATERIALIZED VIEW ta_v_h1 AS (
  SELECT time, symbol, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close, sum(volume) AS volume
  FROM minute_table SAMPLE BY 1h ALIGN TO CALENDAR WITH OFFSET '00:15');
-- 2. a table for the candles plus indicator columns
CREATE TABLE ta_h1 (time TIMESTAMP, symbol SYMBOL, open DOUBLE, ..., rsi14 DOUBLE, ...) TIMESTAMP(time);
-- 3. a procedure that rebuilds it in one transaction
CREATE PROCEDURE ta_build_h1() AS BEGIN
  DELETE FROM ta_h1;
  INSERT INTO ta_h1 SELECT time, symbol, open, ..., rsi(close, 14) OVER (PARTITION BY symbol ORDER BY time) AS rsi14, ... FROM ta_v_h1;
END;
-- 4. a timer that runs it after each trading day (POST /api/timers)
{"name": "ta_h1", "schedule": "trading nepse 15:10", "sql": "REFRESH MATERIALIZED VIEW ta_v_h1 INCREMENTAL; CALL ta_build_h1()"}

Screeners and charts often read the same candles again and again: 5-minute, 15-minute, hourly, weekly and monthly, each with a set of indicators. Build them once in KDB, and read the table.

• Notes:
  • Nepal is UTC+05:45. 5- and 15-minute buckets are the same in UTC and Nepal time. Hours need WITH OFFSET '00:15' (or TIME ZONE 'Asia/Kathmandu'), see hourly candles.
  • Weekly and monthly candles come from the day table: SAMPLE BY 1w WEEK START SUNDAY and SAMPLE BY 1M, see weekly candles.
  • Give each timeframe its own names. KDB names ignore case, so ta_1m and ta_1M would be the same table: use m5, m15, h1, w1, mo1.
  • bench/006/ta-folders.mjs in the source sets all of this up for the five timeframes, writes one file per symbol and timeframe, and with --compare <folder> checks the files against another set, column by column with a tolerance (node bench/006/ta-folders.mjs prints the help; sql prints the whole recipe).

Example: Hourly Nepal candles with indicators, rebuilt by one CALL (sample data: sample)

Setup:

CREATE MATERIALIZED VIEW ta_v_h1 AS (
  SELECT time, symbol, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close, sum(volume) AS volume
  FROM ochl_minutes SAMPLE BY 1h ALIGN TO CALENDAR WITH OFFSET '00:15');
CREATE TABLE ta_h1 (time TIMESTAMP, symbol SYMBOL, close DOUBLE, volume DOUBLE, sma3 DOUBLE, rsi3 DOUBLE) TIMESTAMP(time);
CREATE PROCEDURE ta_build_h1() AS BEGIN
  DELETE FROM ta_h1;
  INSERT INTO ta_h1
  SELECT time, symbol, close, volume,
         sma(close, 3) OVER (PARTITION BY symbol ORDER BY time),
         rsi(close, 3) OVER (PARTITION BY symbol ORDER BY time)
  FROM ta_v_h1;
END;

CALL ta_build_h1();
SELECT time, close, round(sma3, 2) AS sma3, round(rsi3, 1) AS rsi3
FROM ta_h1 WHERE symbol = 'CITY' ORDER BY time DESC LIMIT 4;

Result:

CALL ta_build_h1()

procedure 'ta_build_h1' done: 16 row(s) changed

SELECT time, close, round(sma3, 2) AS sma3, round(rsi3, 1) AS rsi3 FROM ta_h1 WHERE symbol = 'CITY'

(4 rows)

Example: Weekly candles (Sunday weeks) with a 2-week EMA (sample data: sample)

Setup:

CREATE MATERIALIZED VIEW ta_v_w1 AS (
  SELECT time, symbol, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close, sum(volume) AS volume
  FROM ochl_day SAMPLE BY 1w WEEK START SUNDAY);

SELECT time, close, round(ema(close, 2) OVER (PARTITION BY symbol ORDER BY time), 2) AS ema2
FROM ta_v_w1 WHERE symbol = 'VOLT' ORDER BY time DESC LIMIT 3;

Result:

(3 rows)

Filling gaps in minute candles

Syntax:

SAMPLE BY 1m FILL(PREV)
-- open, high, low, close = the minute before, volume = 0: one rule per column
SAMPLE BY 1m FILL(PREV, PREV, PREV, PREV, 0)
-- every minute of a fixed range, also before the first trade
SAMPLE BY 1m FROM '...' TO '...' FILL(PREV, 0)

Some minutes have no trades, so there is no candle. A chart with a fixed number of minutes needs a row for every minute.

• Notes:
  • FILL(PREV) copies every column of the minute before, also the volume. That is right for the price, wrong for the volume.
  • FILL(PREV, 0) gives each aggregate column its own rule, in SELECT order: close from the minute before, volume 0.
  • FROM ... TO ... fills a fixed range of minutes (a chart window), TO not included. See FROM / TO.
  • FILL fills every empty minute between the first and the last row, also the night. Filter one trading day (or a shorter range).
  • Details: FILL.

Example: COAST had no trades from 12:31 to 12:34: FILL(PREV) (sample data: sample)

SELECT time, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST' AND time >= '2026-09-10T12:30:00+05:45' AND time < '2026-09-10T12:36:00+05:45'
SAMPLE BY 1m FILL(PREV);

Result:

(6 rows)

Example: Candles with open, high, low, close from the minute before, volume 0 (sample data: sample)

SELECT time, first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'COAST'
SAMPLE BY 1m FROM '2026-09-10T12:30:00+05:45' TO '2026-09-10T12:36:00+05:45'
FILL(PREV, PREV, PREV, PREV, 0);

Result:

(6 rows)