QuestDB SQL Cheatsheet: SAMPLE BY, ASOF JOIN, SYMBOL & Partitioning in One Page

CREATE TABLE metrics (
    ts TIMESTAMP,
    host SYMBOL,              -- machine name, dozens to thousands
    metric SYMBOL,            -- metric name, dozens
    val DOUBLE
) PARTITION BY DAY;

CREATE TABLE ticks (
    ts TIMESTAMP,
    sym SYMBOL INDEX 256,     -- trading pair, hundreds to thousands
    px DOUBLE,
    vol DOUBLE
) PARTITION BY HOUR;

CREATE TABLE events (
    ts TIMESTAMP,
    user_id VARCHAR,          -- unique per row, SYMBOL would blow up
    event VARCHAR,
    payload JSON
);

# single row, no timestamp (server time is used)
echo 'cpu,host=srv1,metric=usage val=42.5' | nc localhost 9009

# batch + explicit nanosecond timestamp
echo 'cpu,host=srv1,metric=usage val=42.5 1729555200000000000' | nc localhost 9009

# auto-DDL: QuestDB creates the table on first sight of the measurement
echo 'mem,host=srv1,metric=used_kb val=1048576' | nc localhost 9009

-- 1. per-5-minute average over the last hour
SELECT ts, AVG(val) AS avg_val
FROM metrics WHERE ts >= now() - INTERVAL 1 HOUR
SAMPLE BY 5m;

-- 2. per-5-minute max + the timestamp of that max
SELECT ts, MAX(val) AS peak, MAX_AGGREGATE_TIMESTAMP(val) AS peak_ts
FROM metrics WHERE ts >= now() - INTERVAL 1 DAY
SAMPLE BY 5m;

-- 3. fill gaps with the previous value (no misleading zeros)
SELECT ts, val FROM cpu SAMPLE BY 1m FILL(previous);

-- 4. fill gaps with linear interpolation
SELECT ts, val FROM cpu SAMPLE BY 1m FILL(linear);

-- 5. independent aggregation per host, results aligned by host
SELECT ts, host, AVG(val)
FROM metrics WHERE ts >= now() - INTERVAL 1 HOUR
SAMPLE BY 1m BY host;

-- 6. snap timestamps to the 10-second grid
SELECT ts, val FROM cpu SAMPLE BY 10s ALIGN TO SECOND;

-- 7. 5-minute windows offset by 50 seconds (windows start at :05, not :00)
SELECT ts, AVG(val) FROM cpu SAMPLE BY 5m OFFSET 50s;

-- 8. P95 per host
SELECT host, PERCENTILE(val, 0.95) AS p95
FROM metrics WHERE ts >= now() - INTERVAL 1 HOUR
GROUP BY host;

-- 9. two tables aligned by nearest timestamp (ASOF, the common case)
SELECT t.ts, t.px, f.rate
FROM ticks t ASOF JOIN fx f ON f.ts <= t.ts;

-- 10. ASOF + tolerance (FX rate is at most 10 minutes stale)
SELECT t.ts, t.px, f.rate
FROM ticks t ASOF JOIN fx f ON f.ts <= t.ts
WHERE t.ts - f.ts <= INTERVAL 10 MINUTE;

-- 11. ASOF + equi-key (multi-source alignment)
SELECT r.ts, r.val, c.firmware
FROM readings r ASOF JOIN config c
ON r.host = c.host AND c.ts <= r.ts;

-- 12. backtest look-ahead guard (strictly less-than)
SELECT fill.ts, signal.side
FROM fills fill ASOF JOIN signals signal
ON signal.ts < fill.ts;

-- 13. window function: pair each row with itself 5 minutes earlier
SELECT ts, val, LAG(val, 5) OVER (ORDER BY ts) AS val_5m_ago
FROM cpu WHERE ts >= now() - INTERVAL 1 HOUR;

-- 14. rolling mean (100-row window)
SELECT ts, AVG(val) OVER (ORDER BY ts ROWS 100 PRECEDING) AS roll_avg
FROM cpu WHERE ts >= now() - INTERVAL 1 HOUR;

-- 15. count
SELECT COUNT(*) FROM ticks WHERE ts >= now() - INTERVAL 1 DAY;

-- 16. distinct value count
SELECT COUNT(DISTINCT sym) FROM ticks;

-- 17. count grouped by hour
SELECT ts, COUNT(*)
FROM ticks WHERE ts >= now() - INTERVAL 24 HOUR
SAMPLE BY 1h;

-- 18. JSON field extraction
SELECT ts, payload->>'region' AS region FROM events
WHERE ts >= now() - INTERVAL 1 HOUR;

-- 19. list columns of a table
SELECT * FROM columns WHERE table_name = 'metrics';

-- 20. partition listing
SELECT * FROM partitions WHERE table_id =
  (SELECT id FROM tables WHERE name = 'metrics');

-- declare at creation time (ILP auto-DDL has no partitioning; pre-create with DDL)
CREATE TABLE cpu (...) PARTITION BY DAY;
CREATE TABLE ticks (...) PARTITION BY HOUR;
CREATE TABLE logs (...) PARTITION BY MONTH;

-- drop one partition (date string format varies with partition granularity)
SELECT DROP_PARTITION('cpu', '2026_01_15');   -- DAY granularity
SELECT DROP_PARTITION('ticks', '2026_01');     -- MONTH granularity

-- inspect partition storage
SELECT * FROM partitions WHERE table_id = (SELECT id FROM tables WHERE name='cpu');