SAMPLE BY and UNION precedence on QuestDB

Question

I have two tables which store candlestick data. One is real-time, another is backfilled from history which only contain 1 minute candlesticks. I have this working query

SELECT timestamp, first(open) as open, max(high) as high, min(low) as low, last(close) as close, sum(volume) as volume 
FROM "coinbase-candlesticks" 
WHERE symbol = 'BTC/USD'
SAMPLE BY 5m 
UNION
SELECT timestamp, first(open) as open, max(high) as high, min(low) as low, last(close) as close, sum(volume) as volume 
FROM "coinbase-historical" 
WHERE symbol = 'BTC/USD'
SAMPLE BY 5m 
ORDER BY timestamp ASC
LIMIT 100000;

Here's my question, can I use sample and union in the same query, does the UNION portion distinct the query before applying sample? I just want to make sure it does that so there isn't any overlap and the sample aggregation works properly.

Answer 1

If we run EXPLAIN on that query, we will see the UNION happens after the sample (in this case, the engine compiles the sample to a group by clause).

Therefore, we would need to subquery the union first, then sample second to make sure no overlap exists.

WITH candlestickQuery AS 
(
  SELECT timestamp, open, high, low, close, volume
  FROM "coinbase-candlesticks" 
  WHERE timeframe = 1 and symbol = 'BTC/USD'
  UNION
  SELECT timestamp, open, high, low, close, volume
  FROM "coinbase-historical" 
  WHERE timeframe = 1 and symbol = 'BTC/USD'
  ORDER BY timestamp ASC
) 
SELECT timestamp, first(open) as open, max(high) as high, min(low) as low, last(close) as close, sum(volume) as volume 
FROM candlestickQuery
SAMPLE BY 5m 
ORDER BY timestamp DESC;