questdb — скилл для Claude Code: что делает и как установить

---
name: questdb
description: >
  Use this skill whenever working with QuestDB — a high-performance time-series
  database. Trigger on any mention of QuestDB, time-series SQL with SAMPLE BY,
  LATEST ON, ASOF JOIN, ILP ingestion, or the questdb Python/Go/Java/Rust/.NET
  client libraries. Also trigger when writing Grafana queries against QuestDB,
  creating materialized views for time-series rollups, working with order book
  or financial market data in QuestDB, or any SQL that involves designated
  timestamps or time-partitioned tables. QuestDB extends SQL with unique
  time-series keywords — standard PostgreSQL or MySQL patterns will fail.
  Always read this skill before writing QuestDB SQL to avoid hallucinating
  incorrect syntax.
---

# QuestDB Skill

## How to Use This Skill

**IMPORTANT — MINIMIZE ROUND-TRIPS:**
- Do NOT explore library source code (cryptofeed, questdb, etc.)
- Do NOT check library versions or verify callback signatures
- Do NOT read installed package files to "understand the API"
- Do NOT verify infrastructure (Docker containers, Grafana health) is running — trust the user's prompt
- Do NOT start `02_ingest.py` separately — `03_dashboard.py` launches it and verifies data automatically
- Do NOT read extra reference files for topics already covered in this skill file
- DO read reference files when their topic applies (e.g. enterprise.md for auth, grafana-advanced.md for complex panels)
- Do NOT use task tracking (TaskCreate/TaskUpdate) for straightforward builds
- Do NOT add `sleep` commands to wait for data or check background processes (the deploy script handles this)
- Do NOT Ctrl+C, restart, or re-launch the ingestion process once `03_dashboard.py` has started it
- Do NOT put VWAP, Bollinger, or RSI in separate timeseries panels — they are refIDs on the OHLC candlestick panel
- Do NOT omit or empty `fieldConfig.overrides` — they put RSI on a right Y-axis (0-100%) and spread on a right axis. Without them, different scales crush the chart flat.
- Do NOT set dashboard refresh to `"5s"` — the default is `"250ms"`
- When opening the dashboard URL in the browser, ALWAYS append `?refresh=250ms` to the URL. Without this, Grafana ignores the JSON refresh setting.
- All API details for cryptofeed, QuestDB ingestion, and Grafana are below — use them as-is
- Known Python environment issues are already solved in the templates below:
  **uvloop:** When creating a NEW venv, pip may pull a uvloop version that crashes on Python 3.10+
  (`RuntimeError: no current event loop`). Uninstall it after pip install in fresh venvs only.
  Never uninstall uvloop from a user's existing venv — their setup already works.
  **macOS SSL:** Homebrew Python lacks system CA certificates. Any outbound HTTPS/WSS
  connection (exchange WebSockets, API calls) fails without certifi. Always set
  `SSL_CERT_FILE` via certifi at the top of scripts that make network connections.
  These fixes are baked into the pip commands and Python templates below — copy them exactly.

This skill contains ready-to-use SQL, schemas, ingestion code, and Grafana
queries. **Write the files and run them.** A typical pipeline is 3 files
(schema setup, ingestion script, dashboard deploy) — write them, execute them, done.

### Execution Scenarios

Pick the scenario that matches the user's request. Run the exact commands shown.
Each bash code block below is one Bash tool call. Execute immediately - do not
deliberate over shell syntax. Blocks at the same step can run as parallel tool calls.

**Scenario A — Everything from scratch (Docker + venv + pipeline):**
Use separate parallel Bash tool calls (one command per call) to parallelize:
```bash
docker run -d --name questdb -p 9000:9000 -p 9009:9009 -p 8812:8812 questdb/questdb:latest
```
```bash
docker run -d --name grafana -p 3000:3000 -e GF_INSTALL_PLUGINS=questdb-questdb-datasource -e GF_SECURITY_ADMIN_PASSWORD=admin -e GF_DASHBOARDS_MIN_REFRESH_INTERVAL=250ms grafana/grafana:latest
```
```bash
python3 -m venv .venv
```
Then sequentially:
```bash
.venv/bin/pip install -q cryptofeed questdb 'psycopg[binary]' requests numpy certifi
```
```bash
.venv/bin/pip uninstall uvloop -y 2>/dev/null
```
Then wait for both services (each in its own Bash call):
```bash
curl -sf --retry 30 --retry-delay 1 --retry-all-errors -o /dev/null "http://localhost:9000/exec?query=SELECT+1"
```
```bash
curl -sf --retry 30 --retry-delay 1 --retry-all-errors -o /dev/null http://localhost:3000/api/health
```
Then configure the datasource:
```bash
curl -s -X POST http://localhost:3000/api/datasources -u admin:admin -H "Content-Type: application/json" -d '{"name":"QuestDB","type":"questdb-questdb-datasource","access":"proxy","jsonData":{"server":"host.docker.internal","port":8812,"username":"admin","tlsMode":"disable","timeout":"120","queryTimeout":"60"},"secureJsonData":{"password":"quest"}}'
```
**Datasource fields (QuestDB Grafana plugin uses jsonData, NOT the standard url field):**
- `server`: hostname only, no port, no protocol (e.g. `host.docker.internal`)
- `port`: integer, separate from server (e.g. `8812`)
- `tlsMode`: must be `"disable"` for local Docker — omitting it defaults to TLS enabled, which breaks the connection
- `username`/`password`: QuestDB defaults are `admin`/`quest`
Then write 3 files (schema, ingestion, dashboard) and run them.

**Scenario B — Containers running, need venv:**
```bash
python3 -m venv .venv
```
```bash
.venv/bin/pip install -q cryptofeed questdb 'psycopg[binary]' requests numpy certifi
```
```bash
.venv/bin/pip uninstall uvloop -y 2>/dev/null
```
Then write 3 files and run them.

**Scenario C — User provides existing venv path:**
Trust the user's venv as-is. Do NOT run pip install or uninstall anything.
Write 3 files and run them. Use the user's venv path for all `python` commands.

**Scenario D — Everything already running, just need pipeline scripts:**
Write 3 files and run them. No infrastructure setup needed.

Additional references in the `references/` directory — only read when the user's
request goes beyond what this file covers:
- `common-mistakes.md` — Wrong patterns → correct QuestDB equivalents (read when writing novel SQL not already templated below)
- `grafana-advanced.md` — Read only for Plotly order book depth charts or advanced features not in the dashboard template below
- `indicators.md` — Read when user asks for indicators beyond OHLC/VWAP/Bollinger/RSI (MACD, ATR, Stochastic, OBV, Drawdown, Keltner, Donchian, etc.)
- `cookbook.md` — Index of official QuestDB cookbook recipes: finance (slippage, markout, IS, ECN scorecard, last-look, indicators, volume/order-flow, risk) and time-series patterns (FILL strategies, session windows, latest-N-per-partition, sparse sensor joins). Read when the user asks for an execution-quality/TCA metric, a named indicator, or a time-series pattern you don't already have inline.
- `sql-grammar.md` — Complete index of QuestDB keywords, functions (by category with signatures), data types, and operators. **Read when you need to know "does QuestDB have function X?" or want the correct signature for a function.** Includes native finance builtins (`vwap()`, `twap()`, `spread()`, `mid()`, `wmid()`, `l2price()`), statistical functions, array/matrix operations, and more.
- `enterprise.md` — **Read when QuestDB uses authentication, HTTPS, tokens, or ACLs** (skip for open source)

## Critical Rule

**QuestDB is NOT PostgreSQL.** It supports the PostgreSQL wire protocol for
querying, but has its own SQL extensions for time-series operations. Standard
PostgreSQL patterns like `time_bucket()`, `DISTINCT ON`, `HAVING`, and
`generate_series()` do not exist.

## Live Documentation Access

**Every QuestDB docs page has a plain-markdown twin** at the same URL with `.md`
appended. Fetch these with `curl` for fast, clean, LLM-friendly reads — no HTML
to strip, no scraping required. Prefer an authoritative doc fetch over guessing
when you need a keyword, syntax detail, or edge case not covered inline here.

```bash
# Any doc page — just append .md to the URL
curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/horizon-join.md"
curl -sH "Accept: text/markdown" "https://questdb.com/docs/cookbook/sql/finance.md"

# Full documentation index for LLMs — lists every page with its .md URL
curl -s "https://questdb.com/docs/llms.txt"
```

Use `llms.txt` to discover a page when you don't know the exact path. It is the
authoritative directory of every doc and cookbook recipe.

---

## SQL Reference

### CREATE TABLE

```sql
CREATE TABLE IF NOT EXISTS trades (
    ts TIMESTAMP,
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    amount DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);
```

Key rules:
- `TIMESTAMP(col)` designates the time column — required for SAMPLE BY, LATEST ON, ASOF JOIN
- `SYMBOL` type for any repeated string (tickers, categories, status codes) — much faster than VARCHAR
- `PARTITION BY DAY|MONTH|YEAR|HOUR` — use the most common query granularity
- `WAL` enables concurrent writes (required for ILP ingestion)
- `DEDUP UPSERT KEYS(ts, symbol)` deduplicates on (timestamp, symbol) — idempotent ingestion

**Column types:** BOOLEAN, BYTE, SHORT, INT, LONG, FLOAT, DOUBLE, CHAR, VARCHAR,
SYMBOL, TIMESTAMP, DATE, LONG256, GEOHASH, UUID, IPv4, DOUBLE[], FLOAT[], INT[],
LONG[], SHORT[], UUID[] — plus 2D arrays like `DOUBLE[][]`

**ALTER TABLE:**
```sql
ALTER TABLE trades ADD COLUMN exchange SYMBOL;
ALTER TABLE trades DROP COLUMN exchange;
ALTER TABLE trades RENAME COLUMN amount TO qty;
ALTER TABLE trades ALTER COLUMN exchange SET TYPE SYMBOL;
```

### SAMPLE BY (Time-Series Aggregation)

SAMPLE BY is QuestDB's time-bucketing. It replaces `GROUP BY time_bucket()`.
Requires a designated timestamp.

```sql
SELECT ts, symbol,
    first(price) AS open, max(price) AS high,
    min(price) AS low, last(price) AS close,
    sum(amount) AS volume
FROM trades
WHERE ts > '2025-01-01'
SAMPLE BY 1h;
```

Key rules:
- Valid intervals: `1s`, `5s`, `1m`, `15m`, `1h`, `1d`, `1M` (month)
- `ALIGN TO CALENDAR` aligns buckets to clock boundaries (this is the default, so it can be omitted)
- `FILL(PREV | NULL | LINEAR | value)` fills gaps — goes AFTER SAMPLE BY
- In Grafana, default to `SAMPLE BY 5s` unless the user specifies a different bar size
- `first()`, `last()` return first/last values within each time bucket
- `count_distinct(col)` instead of `COUNT(DISTINCT col)`
- No `HAVING` — use a subquery: `SELECT * FROM (... SAMPLE BY ...) WHERE volume > 1000`

### LATEST ON (Last Value Per Group)

Returns the most recent row per group. Replaces `DISTINCT ON` / `ROW_NUMBER()`.

```sql
SELECT * FROM trades
WHERE ts > dateadd('h', -1, now())
LATEST ON ts PARTITION BY symbol;
```

Rules:
- `LATEST ON ts` must reference the designated timestamp
- `PARTITION BY` is required
- Add a `WHERE` time filter for performance

### Joins

QuestDB has a family of specialised time-series joins. Pick the one that matches
the question — reaching for ASOF JOIN by default when HORIZON or WINDOW would be
idiomatic is a common LLM mistake.

| Pattern | Use |
|---------|-----|
| Point-in-time match: "value at trade time" | **ASOF JOIN** (or HORIZON JOIN with `LIST (0)`) |
| Match strictly before trade time | **LT JOIN** |
| Interleave two time series chronologically | **SPLICE JOIN** |
| **Price at trade time AND at offsets ±N (markout, IS decomposition, adverse selection)** | **HORIZON JOIN** |
| **Aggregate quotes within a ±N window around each trade (rolling stats, quote density, pre-/post-trade averages)** | **WINDOW JOIN** |
| Per-outer-row subquery: top-N per group, dynamic filters, per-row SAMPLE BY / LATEST ON / ASOF | **LATERAL JOIN** |

```sql
-- ASOF JOIN: nearest right row with ts ≤ left.ts
SELECT * FROM trades ASOF JOIN quotes ON (symbol);

-- LT JOIN: strictly ts < left.ts
SELECT * FROM trades LT JOIN quotes ON (symbol);

-- SPLICE JOIN: merge two time series interleaved
SELECT * FROM trades SPLICE JOIN quotes ON (symbol);

-- HORIZON JOIN: ASOF at a grid of time offsets, one parallel pass.
-- For each trade, computes metrics at -1m, -30s, 0, 30s, 1m, ...
-- Supports multiple right-hand tables (only the last carries RANGE/LIST/AS).
SELECT h.offset / 1_000_000_000 AS horizon_sec, t.symbol,
       avg((m.best_bid + m.best_ask) / 2) AS avg_mid
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
RANGE FROM -1m TO 5m STEP 30s AS h
WHERE t.timestamp IN '$yesterday'
ORDER BY t.symbol, horizon_sec;

-- Or with explicit offsets for implementation shortfall:
--   LIST (0, 1800s) AS h  →  at_fill and at_30m columns (via PIVOT)

-- WINDOW JOIN: aggregate right-table rows within a ±time window around each left row.
-- EXCLUDE PREVAILING omits the last-known value before the window start.
SELECT t.symbol, t.timestamp,
       avg(c.bid_price) AS avg_bid_pm5s,
       count()          AS num_quotes
FROM fx_trades t
WINDOW JOIN core_price c ON (t.symbol = c.symbol)
    RANGE BETWEEN 5 seconds PRECEDING AND 5 seconds FOLLOWING
    EXCLUDE PREVAILING
WHERE t.symbol = 'EURUSD' AND t.timestamp IN '$now-1h..$now';

-- LATERAL JOIN: subquery evaluated per outer row, can reference outer columns.
-- Classic top-N per group — two largest fills for each order:
SELECT o.id, o.desk, t.qty
FROM orders o
JOIN LATERAL (
    SELECT qty FROM fills
    WHERE order_id = o.id
    ORDER BY qty DESC LIMIT 2
) t
ORDER BY o.id, t.qty DESC;
```

Join rules:
- All time-series joins (ASOF, LT, SPLICE, HORIZON, WINDOW) require a **designated
  timestamp** on both tables. Timestamps are matched automatically.
- `ON (symbol)` matches the key column; both tables must use the same column name.
  Use `ON (t.sym = q.sym)` when the names differ.
- Standard INNER JOIN and LEFT JOIN also work for non-time-series joins.
- **HORIZON JOIN**: no other join types in the same level, no SAMPLE BY, no window
  functions inside, WHERE filters left table only. Wrap in a subquery for anything else.
- **WINDOW JOIN**: cannot be combined with GROUP BY, window functions, or other
  join types. Wrap the join in a CTE then aggregate/join in the outer query.
- **LATERAL JOIN**: only INNER, LEFT, or CROSS variants supported. Requires a
  parenthesised subquery. Inside the subquery you can reference outer columns and
  use SAMPLE BY, LATEST ON, ASOF JOIN, window functions, UNION ALL, etc.

For the full syntax of HORIZON / WINDOW / LATERAL (including mixed-precision
timestamps, dynamic window bounds, and multi-table HORIZON patterns), fetch:
- `curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/horizon-join.md"`
- `curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/window-join.md"`
- `curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/lateral-join.md"`

For ready-made **execution analytics** queries (slippage, markout, implementation
shortfall, last-look, ECN scorecard) — all built on HORIZON JOIN — see
`references/cookbook.md` or https://questdb.com/docs/cookbook/sql/finance.md.

### UNNEST (Arrays and JSON Arrays to Rows)

QuestDB supports `UNNEST` for expanding arrays - or JSON arrays stored as
VARCHAR - into rows. Appears in the `FROM` clause and behaves like a table.

```sql
-- Native array: expand the sizes at level 2 of each order book row
SELECT t.symbol, u.vol
FROM market_data t, UNNEST(t.asks[2]) u(vol)
WHERE t.timestamp IN '$now-1m..$now' AND t.symbol = 'EURUSD';

-- WITH ORDINALITY gives a 1-based level index (resets per input row)
SELECT m.symbol, u.vol, u.level
FROM market_data m, UNNEST(m.asks[2]) WITH ORDINALITY u(vol, level)
WHERE m.timestamp IN '$now-1m..$now';

-- JSON array (VARCHAR) with typed columns — great for ingesting API payloads
SELECT u.trade_id, u.price, u.size, u.side, u.time
FROM UNNEST(
    payload_varchar::VARCHAR
    COLUMNS(trade_id LONG, price DOUBLE, size DOUBLE, side VARCHAR, time TIMESTAMP)
) u;
```

Key rules:
- `UNNEST` must be in `FROM` (or after `CROSS JOIN` / comma), not in `SELECT`
- Native arrays: currently `DOUBLE[]` only. `UNNEST` of a `DOUBLE[][]` yields
  `DOUBLE[]` rows - chain a second `UNNEST` to fully flatten.
- JSON: `COLUMNS(name TYPE, ...)` extracts typed fields. Supported types:
  BOOLEAN, SHORT, INT, LONG, DOUBLE, VARCHAR, TIMESTAMP. Invalid JSON / NULL /
  empty string all produce 0 rows (no error).
- `ordinality` is reserved — alias it (`u(val, pos)`) or quote it.

Full reference: `curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/unnest.md"`

### Window Functions

```sql
-- ROWS frame (count-based)
AVG(close) OVER (ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sma20

-- CUMULATIVE shorthand (QuestDB extension, equivalent to ROWS UNBOUNDED PRECEDING)
SUM(volume) OVER (ORDER BY ts CUMULATIVE) AS running_total

-- PARTITION BY
LAG(close) OVER (PARTITION BY symbol ORDER BY ts) AS prev_close

-- EMA (exponential moving average — QuestDB extension)
avg(price, 'period', 14) OVER (ORDER BY ts) AS ema14
```

Supported: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `LAG()`, `LEAD()`,
`FIRST_VALUE()`, `AVG()`, `SUM()`, `MIN()`, `MAX()`, `COUNT()`

**Important:** `stddev_samp()` may not work in window frames. For standard deviation,
compute manually: `sqrt(avg(x*x) - avg(x)^2)` — see Bollinger Bands query below.

### Materialized Views

Auto-updated, incremental aggregations triggered by new data:

```sql
CREATE MATERIALIZED VIEW IF NOT EXISTS candles_5s AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades SAMPLE BY 5s
) PARTITION BY DAY;
```

Rules:
- Source query MUST use SAMPLE BY
- Only aggregation functions allowed (no WHERE, no JOIN, no window functions)
- Use `IF NOT EXISTS` to avoid errors on re-run
- Cascade views for multi-resolution: `trades → 5s → 1m → 1h` (see Schema Design below)
- Invalidate/rebuild: `ALTER MATERIALIZED VIEW candles_5s INVALIDATE`

### Views (non-materialized)

QuestDB also supports regular views - virtual tables defined by a SELECT
statement, evaluated on every query. Use them for reusable query abstractions
that don't need incremental precomputation.

```sql
CREATE VIEW IF NOT EXISTS latest_prices AS (
    SELECT * FROM trades
    LATEST ON ts PARTITION BY symbol
);

-- Query it like a table
SELECT * FROM latest_prices WHERE symbol = 'BTC-USDT';

-- Modify or drop
ALTER VIEW latest_prices AS (SELECT ...);   -- redefine
COMPILE VIEW latest_prices;                  -- recompile after schema changes
DROP VIEW latest_prices;
```

Use a **materialized view** when you need incremental SAMPLE BY rollups. Use a
**regular view** for everything else (latest-value lookups, filtered subsets,
join abstractions).

### Timestamp Filtering — TICK Syntax (Preferred)

**Always prefer TICK over `dateadd()` / `BETWEEN` for time filters.** TICK
(Temporal Interval Calendar Kit) is QuestDB's declarative syntax for time
intervals. It is more readable, generates optimized interval scans, handles
timezones and business days, and expresses complex multi-interval patterns that
would require UNION ALL or application-side logic otherwise.

**Syntax order:** `date [T time] @timezone #dayFilter ;duration`

```sql
-- Date variables: resolve at query time
WHERE ts IN '$today'                     -- full day (midnight to midnight)
WHERE ts IN '$yesterday'                 -- previous full day
WHERE ts IN '$tomorrow'                  -- next full day

-- Ranges with $now (point-in-time, microsecond precision)
WHERE ts IN '$now - 1h..$now'            -- last hour
WHERE ts IN '$now - 30m..$now'           -- last 30 minutes
WHERE ts IN '$now - 5bd..$now'           -- last 5 business days (skips weekends)

-- Duration suffix: extends forward from a point
WHERE ts IN '$now;1h'                    -- 1 hour starting now (forward)
WHERE ts IN '2025-01-15T09:30;6h30m'     -- NYSE trading session

-- Imprecise dates: month-level and year-level (expand to all days)
WHERE ts IN '[2025-01]'                  -- all of January (no need for [01..31])
WHERE ts IN '[2025]T09:30'              -- all of 2025 at 09:30
WHERE ts IN '2025-02T09:30'             -- bare imprecise (brackets optional alone)

-- Bracket expansion: generates multiple intervals
WHERE ts IN '2025-01-[10..15]'           -- days 10 through 15
WHERE ts IN '2025-01-[5,10..12,20]'      -- specific days + ranges
WHERE ts IN '2025-[01,06]-[10,15]'       -- Cartesian: Jan+Jun x 10th+15th
WHERE ts IN '2025-[01..03]T09:30'        -- month-level range: all days Jan-Mar

-- Day-of-week filters
WHERE ts IN '[2025-01]#workday'          -- weekdays in January
WHERE ts IN '[2025-01]#weekend'          -- weekends in January
WHERE ts IN '[2025-01]#Mon,Wed,Fri'      -- specific days in January

-- Timezone-aware (handles DST)
WHERE ts IN '2025-01-15T09:30@America/New_York;6h30m'

-- Combined: workdays at 09:30 New York time for all of January
WHERE ts IN '[2025-01]T09:30@America/New_York#workday;6h30m'

-- Mixed-precision date lists
WHERE ts IN '[2025-01-15, 2025-02]T09:30'  -- one day + full month

-- Time lists: multiple intraday windows
WHERE ts IN '2025-01-15T[09:00,14:30];1h'  -- two 1h windows on the same day

-- Date lists (brackets required for lists)
WHERE ts IN '[$today, $yesterday, 2025-01-15]'

-- ISO week dates
WHERE ts IN '2025-W01-[1..5]T09:00;8h'  -- Mon-Fri of week 1
```

**Key rules:**
- Date variables are case-insensitive (`$TODAY` = `$today`)
- `$today`/`$yesterday`/`$tomorrow` produce **full-day intervals**; `$now`
  produces a **point-in-time** (add range or duration to make it useful)
- Arithmetic units: `y` `M` `w` `d` `bd` `h` `m` `s` `T`(ms) `u`(us) `n`(ns).
  `bd` (business days) is valid in arithmetic only, not in durations.
  Case-sensitive: `M` = months, `m` = minutes, `T` = milliseconds.
- **Imprecise dates:** `YYYY-MM` expands to all days in that month, `YYYY` to
  all days in that year. `'[2025-01]T09:30#workday;6h30m'` replaces the verbose
  `'2025-01-[01..31]T09:30#workday;6h30m'`. Brackets optional when used alone
  (`'2025-02T09:30'` = `'[2025-02]T09:30'`), required in lists.
- Brackets required for: lists (`[$today, $yesterday]`), ranges with suffixes
  (`[$now - 2h..$now]@America/New_York`). Optional for standalone variables,
  bare ranges (`$now - 2h..$now`), and bare imprecise dates (`2025-01T09:30`).
- Overlapping intervals from bracket expansion are auto-merged.
- **Exchange calendars (Enterprise):** use `#XNYS`, `#XLON`, etc. (ISO 10383 MIC
  codes) instead of `#workday` to filter by real exchange trading schedules -
  holidays, early closes, and lunch breaks are handled automatically.
  `WHERE ts IN '[2025-01]#XNYS'` gives only NYSE trading sessions.
  See: `curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/operators/exchange-calendars.md"`

Full reference: `curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/operators/tick.md"`

### DECLARE (Variables)

```sql
DECLARE @start := '2025-01-01T00:00:00Z', @end := now();
SELECT * FROM trades WHERE ts BETWEEN @start AND @end;
```

### SQL Execution Order

`FROM → WHERE → SAMPLE BY → SELECT → LIMIT`
(No GROUP BY needed with SAMPLE BY. No HAVING at all.)

### Key Functions

**Timestamp:** `now()`, `systimestamp()`, `dateadd('unit', n, ts)`,
`datediff('unit', ts1, ts2)`, `to_timestamp('str', 'fmt')`,
`timestamp_floor('unit', ts)`, `timestamp_ceil('unit', ts)`,
`hour(ts)`, `day_of_week(ts)`, `week_of_year(ts)`, `year(ts)`, `month(ts)`

**String:** `concat('a', 'b')`, `left(s, n)`, `right(s, n)`, `length(s)`,
`starts_with(s, prefix)`, `lcase(s)`, `ucase(s)`, `replace(s, old, new)`,
`split_part(s, delim, idx)`, `regexp_replace(s, pattern, replacement)`

**Aggregation:** `first(x)`, `last(x)`, `count_distinct(x)`, `sum(x)`, `avg(x)`,
`min(x)`, `max(x)`, `haversine_dist_deg(lat1, lon1, lat2, lon2)`

**Array:** `array_cum_sum(arr)` — cumulative sum of array elements

---

## Data Ingestion

### ILP (InfluxDB Line Protocol) — Primary Method

Use ILP for all high-throughput ingestion. **Never use INSERT INTO for streaming data.**

```python
from questdb.ingress import Sender, TimestampNanos
import numpy as np

# Open Source (no auth). For Enterprise: read references/enterprise.md Quick Start
# (admin creates service account + token via REST → ingestion script uses token)
conf = "tcp::addr=localhost:9009;protocol_version=2;"

with Sender.from_conf(conf) as sender:
    sender.row(
        'trades',
        symbols={'symbol': 'BTC-USDT', 'side': 'buy'},
        columns={'price': 42000.50, 'amount': 1.5},
        at=TimestampNanos.now()
    )
    sender.flush()
```

Key patterns:
- `Sender.from_conf()` does NOT connect — use `with` context manager or call `sender.establish()`
- `symbols={}` for SYMBOL columns, `columns={}` for everything else
- Arrays MUST be `np.float64` numpy arrays, not Python lists
- TCP config requires `protocol_version=2` for array support
- HTTP transport: `http::addr=localhost:9000;`
- TCP transport: `tcp::addr=localhost:9009;`

**2D array ingestion (order books):**
```python
sender.row(
    'order_book',
    symbols={'symbol': 'BTC-USDT'},
    columns={
        'bids': [
            np.array([41999.0, 41998.5, 41998.0], dtype=np.float64),  # prices [1]
            np.array([2.1, 5.3, 10.0], dtype=np.float64),            # sizes  [2]
        ],
        'asks': [
            np.array([42001.0, 42001.5, 42002.0], dtype=np.float64),
            np.array([1.8, 4.2, 8.5], dtype=np.float64),
        ],
    },
    at=TimestampNanos.now()
)
```

**Other client libraries:** Go, Java, Rust, Node.js, C/C++, .NET — fetch `ingestion/clients/{language}`

### INSERT INTO — For Low Volume Only

```sql
INSERT INTO trades (ts, symbol, side, price, amount)
VALUES ('2025-02-09T10:00:00.000000Z', 'BTC-USDT', 'buy', 42000.50, 1.5);
```

### Querying via PG Wire

```python
import psycopg as pg
conn = pg.connect("user=admin password=quest host=localhost port=8812 dbname=qdb")
```

### HTTP REST API

- **Query**: `GET http://localhost:9000/exec?query=URL_ENCODED_SQL`
- **URL-encode ALL special characters** including parentheses: `(` = `%28`,
  `)` = `%29`. Unencoded `()` in curl commands triggers a security prompt.
  Example: `count_distinct%28symbol%29` not `count_distinct(symbol)`
- **Enterprise**: see Quick Start in `references/enterprise.md`
- **POST is not supported** for the exec endpoint - use GET only
- Returns JSON: `{ "columns": [...], "dataset": [...] }`

---

## Schema Design

Key principles:
- Every time-series table needs a designated timestamp
- Use SYMBOL for any repeated string (tickers, categories, status codes)
- Partition by the most common query granularity
- WAL tables for concurrent write workloads
- DEDUP for idempotent ingestion

### Financial Market Data Pipeline Schema (Ready to Use)

```sql
CREATE TABLE IF NOT EXISTS trades (
    ts TIMESTAMP,
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    amount DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);

CREATE TABLE IF NOT EXISTS orderbook (
    ts TIMESTAMP,
    symbol SYMBOL,
    bid_prices DOUBLE[],
    bid_sizes DOUBLE[],
    ask_prices DOUBLE[],
    ask_sizes DOUBLE[]
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);

-- Cascading materialized views: trades → 5s → 1m → 1h
CREATE MATERIALIZED VIEW IF NOT EXISTS candles_5s AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades SAMPLE BY 5s
) PARTITION BY DAY;

CREATE MATERIALIZED VIEW IF NOT EXISTS candles_1m AS (
    SELECT ts, symbol,
        first(open) AS open, max(high) AS high,
        min(low) AS low, last(close) AS close,
        sum(volume) AS volume
    FROM candles_5s SAMPLE BY 1m
) PARTITION BY DAY;

CREATE MATERIALIZED VIEW IF NOT EXISTS candles_1h AS (
    SELECT ts, symbol,
        first(open) AS open, max(high) AS high,
        min(low) AS low, last(close) AS close,
        sum(volume) AS volume
    FROM candles_1m SAMPLE BY 1h
) PARTITION BY MONTH;
```

The Grafana queries below work with these exact table/view names.

---

## Demo & Sample Data

Infrastructure setup commands are in the **Execution Scenarios** section above.
Pick the scenario that matches, run the exact commands, then proceed here.

### Real-Time Crypto Feed (cryptofeed + QuestDB)

**Do NOT explore cryptofeed source code or check its version. Everything you need
is right here.** Copy this ingestion script verbatim. The certifi/SSL fix and all
imports are required — do not omit any lines:

```python
import os
import certifi
os.environ['SSL_CERT_FILE'] = certifi.where()  # Required: macOS lacks system CA certs for HTTPS/WSS

import numpy as np
from cryptofeed import FeedHandler
from cryptofeed.exchanges import OKX
from cryptofeed.defines import TRADES, L2_BOOK
from questdb.ingress import Sender, TimestampNanos

conf = "tcp::addr=localhost:9009;protocol_version=2;"  # Enterprise: see references/enterprise.md

async def trade_cb(t, receipt_timestamp):
    with Sender.from_conf(conf) as sender:
        sender.row(
            'trades',
            symbols={'symbol': t.symbol, 'side': t.side},
            columns={'price': float(t.price), 'amount': float(t.amount)},
            at=TimestampNanos(int(t.timestamp * 1e9))
        )
        sender.flush()

async def book_cb(book, receipt_timestamp):
    bids = book.book['bid']
    asks = book.book['ask']
    # Sort: bids descending by price, asks ascending
    bid_prices = sorted(bids.keys(), reverse=True)[:25]
    ask_prices = sorted(asks.keys())[:25]
    with Sender.from_conf(conf) as sender:
        sender.row(
            'orderbook',
            symbols={'symbol': book.symbol},
            columns={
                'bid_prices': np.array([float(p) for p in bid_prices], dtype=np.float64),
                'bid_sizes':  np.array([float(bids[p]) for p in bid_prices], dtype=np.float64),
                'ask_prices': np.array([float(p) f...