Fill missing intervals with value from another column

Fill missing intervals using the previous value from a specific column to populate multiple columns.

Problem

You have a query like this:

SAMPLE BY with FILL(PREV)Demo this query
SELECT timestamp, symbol, avg(bid_price) as bid_price, avg(ask_price) as ask_price
FROM core_price
WHERE symbol = 'EURUSD' AND timestamp IN '$today'
SAMPLE BY 100T FILL(PREV, PREV);

But when there is an interpolation, instead of getting the PREV value for bid_price and previous for ask_price, you want both prices to show the PREV known value for the ask_price.

Solution

QuestDB supports referencing another aggregate column by alias inside PREV():

Fill bid_price with previous value of ask_priceDemo this query
SELECT timestamp, symbol, avg(bid_price) as bid_price, avg(ask_price) as ask_price
FROM core_price
WHERE symbol = 'EURUSD' AND timestamp IN '$today'
SAMPLE BY 100T FILL(PREV(ask_price), PREV);

The reference must match the target column's type, cannot be broadcast across aggregates, and is rejected when either side is a SYMBOL. For more flexible cases — for example, marking which rows were filled, or chaining custom expressions — the equivalent rewrite below uses window functions:

Fill bid and ask prices with value from ask priceDemo this query
WITH sampled AS (
  SELECT timestamp, symbol, avg(bid_price) as bid_price, avg(ask_price) as ask_price
  FROM core_price
  WHERE symbol = 'EURUSD' AND timestamp IN '$today'
  SAMPLE BY 100T FILL(null)
), with_previous_vals AS (
  SELECT *,
    last_value(ask_price) IGNORE NULLS OVER(PARTITION BY symbol ORDER BY timestamp) as filler
  FROM sampled
)
SELECT timestamp, symbol, coalesce(bid_price, filler) as bid_price,
       coalesce(ask_price, filler) as ask_price
FROM with_previous_vals;

Note the use of IGNORE NULLS modifier on the window function to make sure we always look back for a value, rather than just over the previous row.

You can mark which rows were filled by adding a column that flags interpolated values:

Show which rows were filledDemo this query
WITH sampled AS (
  SELECT timestamp, symbol, avg(bid_price) as bid_price, avg(ask_price) as ask_price
  FROM core_price
  WHERE symbol = 'EURUSD' AND timestamp IN '$today'
  SAMPLE BY 100T FILL(null)
), with_previous_vals AS (
  SELECT *,
    last_value(ask_price) IGNORE NULLS OVER(PARTITION BY symbol ORDER BY timestamp) as filler
  FROM sampled
)
SELECT timestamp, symbol, coalesce(bid_price, filler) as bid_price,
       coalesce(ask_price, filler) as ask_price,
       case when bid_price is NULL then true END as filled
FROM with_previous_vals;

Problem​

Solution​

Problem

Solution