Skip to content

Commit 79b2d3f

Browse files
javierjavier
authored
Merge branch 'main' into jv/named_windows
2 parents b7527f3 + 75e3f33 commit 79b2d3f

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

59 files changed

+4065
-401
lines changed

‎documentation/concepts/materialized-views.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -306,7 +306,7 @@ Specify a partitioning scheme larger than the sampling interval:
306306

307307
```questdb-sql
308308
CREATE MATERIALIZED VIEW my_view AS (
309-
SELECT timestamp, symbol, sum(amount) FROM trades SAMPLE BY 8h
309+
SELECT timestamp, symbol, sum(amount) AS total_amount FROM trades SAMPLE BY 8h
310310
) PARTITION BY DAY;
311311
```
312312

‎documentation/configuration/configuration-utils/_replication.config.json‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,5 +94,57 @@
9494
"native.max.blocking.threads": {
9595
"default": "cpuCount * 4",
9696
"description": "Maximum number of threads for parallel blocking disk IO read/write operations for replication (and other). These threads are ephemeral: They are spawned per need and shut down after a short duration if no longer in use. These are not cpu-bound threads, hence the relative large number. The default should be appropriate for most use cases."
97+
},
98+
"replication.primary.cleaner.enabled": {
99+
"default": "true",
100+
"description": "Master switch for the WAL cleaner."
101+
},
102+
"replication.primary.cleaner.interval": {
103+
"default": "10m",
104+
"description": "Time between cleanup cycles. Range: 1s – 24h."
105+
},
106+
"replication.primary.cleaner.checkpoint.source": {
107+
"default": "true",
108+
"description": "Use checkpoint history as a cleanup trigger source."
109+
},
110+
"replication.primary.cleaner.backup.window.count": {
111+
"default": "backup.cleanup.keep.latest.n or 5",
112+
"description": "Minimum complete backups/checkpoints per instance before cleanup starts. Defaults to `backup.cleanup.keep.latest.n` if backups are enabled, otherwise `5`."
113+
},
114+
"replication.primary.cleaner.delete.concurrency": {
115+
"default": "4 – 12 (auto)",
116+
"description": "Concurrent deletion tasks. Derived from `replication.requests.max.concurrent`. Range: 4 – 32."
117+
},
118+
"replication.primary.cleaner.max.requests.per.second": {
119+
"default": "service-dependent",
120+
"description": "Rate limit for object store delete requests. Set to `0` for unlimited. Range: 0 – 10000."
121+
},
122+
"replication.primary.cleaner.progress.write.interval": {
123+
"default": "5s",
124+
"description": "How often progress is persisted during a cleanup cycle. Lower values mean less re-work after a crash but more writes. Range: 100ms – 60s."
125+
},
126+
"replication.primary.cleaner.dropped.table.cooloff": {
127+
"default": "1h",
128+
"description": "Wait time after `DROP TABLE` before removing the table's data from object storage. Guards against clock skew."
129+
},
130+
"replication.primary.cleaner.retry.attempts": {
131+
"default": "20",
132+
"description": "Retries for transient object store failures during cleanup. Range: 0 – 100."
133+
},
134+
"replication.primary.cleaner.retry.interval": {
135+
"default": "2s",
136+
"description": "Delay between cleanup retries. Range: 0 – 5m."
137+
},
138+
"checkpoint.history.enabled": {
139+
"default": "true (when replication is enabled)",
140+
"description": "Enable the checkpoint history tracker. Requires replication."
141+
},
142+
"checkpoint.history.keep.count": {
143+
"default": "100",
144+
"description": "Maximum checkpoint records retained per instance."
145+
},
146+
"checkpoint.history.long.retry.interval": {
147+
"default": "1m",
148+
"description": "Retry interval for syncing checkpoint history to the object store after burst retries fail."
97149
}
98150
}

‎documentation/cookbook/demo-data-schema.md‎

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -214,7 +214,6 @@ The FX dataset includes several materialized views providing pre-aggregated data
214214
#### FX trades OHLC
215215

216216
- **`fx_trades_ohlc_1m`** - OHLC candlesticks from trade executions at 1-minute intervals
217-
- **`fx_trades_ohlc_1h`** - OHLC candlesticks from trade executions at 1-hour intervals
218217
- **`fx_trades_ohlc_1d`** - OHLC candlesticks from trade executions at 1-day intervals
219218

220219
These views are continuously updated and optimized for dashboard and analytics queries on FX data.
@@ -250,12 +249,12 @@ CREATE TABLE 'trades' (
250249
#### Columns
251250

252251
- **`timestamp`** - Time when the trade was executed (designated timestamp)
253-
- **`symbol`** - Cryptocurrency trading pair from the 12 tracked symbols (see list below)
252+
- **`symbol`** - Cryptocurrency trading pair from the active symbol set (see common pairs below)
254253
- **`side`** - Trade side: **buy** or **sell**
255254
- **`price`** - Execution price of the trade
256255
- **`amount`** - Trade size (volume in base currency)
257256

258-
The table tracks **12 cryptocurrency pairs**: ADA-USDT, AVAX-USDT, BTC-USDT, DAI-USDT, DOT-USDT, ETH-BTC, ETH-USDT, LTC-USDT, SOL-BTC, SOL-USDT, UNI-USDT, XLM-USDT.
257+
Common actively traded pairs include: ADA-USDT, AVAX-USDT, BTC-USDT, DAI-USDT, DOT-USDT, ETH-BTC, ETH-USDT, LTC-USDT, SOL-BTC, SOL-USDT, UNI-USDT, XLM-USDT. Historical data may include additional symbols.
259258

260259
#### Sample data
261260

@@ -300,7 +299,7 @@ These views are continuously updated and provide faster query performance for cr
300299

301300
**FX tables** (`core_price` and `market_data`) use a **3-day TTL (Time To Live)**, meaning data older than 3 days is automatically removed. This keeps the demo instance responsive while providing sufficient recent data.
302301

303-
**Cryptocurrency trades table** has **no retention policy** and contains historical data dating back to **March 8, 2022**. This provides over 3 years of real cryptocurrency trade history for long-term analysis and backtesting.
302+
**Cryptocurrency trades table** has **no retention policy** and contains historical data dating back to **March 8, 2022**. This provides multiple years of real cryptocurrency trade history for long-term analysis and backtesting.
304303

305304
## Using the demo data
306305

‎documentation/cookbook/index.md‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,12 +18,24 @@ Each recipe provides a focused solution to a specific problem, with working code
1818

1919
## Structure
2020

21-
The Cookbook is organized into three main sections:
21+
The Cookbook is organized into the following sections:
2222

2323
- **SQL Recipes** - Common SQL patterns, window functions, and time-series queries
24+
- **[Capital Markets](/docs/cookbook/sql/finance/)** - Technical indicators, execution analysis, and risk metrics for financial data
25+
- **[Time-Series Patterns](/docs/cookbook/sql/time-series/elapsed-time/)** - Common patterns for working with time-series data
26+
- **[Advanced SQL](/docs/cookbook/sql/advanced/rows-before-after-value-match/)** - Complex query patterns like pivoting, funnels, and histograms
2427
- **Programmatic** - Language-specific client examples and integration patterns
2528
- **Operations** - Deployment, configuration, and operational tasks
2629

30+
### Post-trade and execution analysis
31+
32+
QuestDB's time-series joins (`ASOF JOIN`, `HORIZON JOIN`) and high-resolution timestamps make it well-suited for **Transaction Cost Analysis (TCA)** and post-trade workflows. The [Execution & Post-Trade Analysis](/docs/cookbook/sql/finance/) section includes recipes for:
33+
34+
- [Slippage measurement](/docs/cookbook/sql/finance/slippage/) - Per-fill and aggregated slippage against mid and top-of-book
35+
- [Markout analysis](/docs/cookbook/sql/finance/markout/) - Post-trade price reversion curves and adverse selection detection
36+
- [Last look detection](/docs/cookbook/sql/finance/last-look/) - Millisecond-granularity counterparty analysis
37+
- [Implementation shortfall](/docs/cookbook/sql/finance/implementation-shortfall/) - Cost decomposition into spread, permanent, and temporary impact
38+
2739
## Running the examples
2840

2941
**Most recipes run directly on our [live demo instance at demo.questdb.com](https://demo.questdb.com)** without any local setup. Queries that can be executed on the demo site are marked with a direct link to run them.

‎documentation/cookbook/sql/finance/atr.md‎

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
---
2+
title: ATR (Average True Range)
3+
sidebar_label: ATR
4+
description: Calculate Average True Range to measure market volatility for position sizing and stop-loss placement
5+
---
6+
7+
Average True Range (ATR) measures market volatility by calculating the average of true ranges over a period. Unlike simple high-low range, true range accounts for gaps between periods, making it more accurate for volatile markets.
8+
9+
## Problem
10+
11+
You want to measure volatility to set appropriate stop-losses or position sizes. Simple high-low range misses overnight gaps, and standard deviation assumes normal distribution which markets don't follow.
12+
13+
## Solution
14+
15+
```questdb-sql demo title="Calculate 14-period ATR"
16+
DECLARE
17+
@symbol := 'EURUSD',
18+
@lookback := '$now - 1M..$now'
19+
20+
WITH with_prev AS (
21+
SELECT
22+
timestamp,
23+
symbol,
24+
high,
25+
low,
26+
close,
27+
lag(close) OVER (PARTITION BY symbol ORDER BY timestamp) AS prev_close
28+
FROM market_data_ohlc_15m
29+
WHERE symbol = @symbol
30+
AND timestamp IN @lookback
31+
),
32+
true_range AS (
33+
SELECT
34+
timestamp,
35+
symbol,
36+
high,
37+
low,
38+
close,
39+
greatest(
40+
high - low,
41+
abs(high - prev_close),
42+
abs(low - prev_close)
43+
) AS tr
44+
FROM with_prev
45+
WHERE prev_close IS NOT NULL
46+
)
47+
SELECT
48+
timestamp,
49+
symbol,
50+
round(close, 5) AS close,
51+
round(tr, 6) AS true_range,
52+
round(avg(tr, 'period', 14) OVER (PARTITION BY symbol ORDER BY timestamp), 6) AS atr
53+
FROM true_range
54+
ORDER BY timestamp;
55+
```
56+
57+
The query:
58+
1. Gets previous close using `lag()` to detect gaps
59+
2. Calculates true range as the greatest of:
60+
- Current high - current low (intraday range)
61+
- |Current high - previous close| (gap up)
62+
- |Current low - previous close| (gap down)
63+
3. Applies 14-period EMA smoothing to get ATR
64+
65+
## Interpreting results
66+
67+
- **High ATR**: Market is volatile, use wider stops
68+
- **Low ATR**: Market is quiet, can use tighter stops
69+
- **Rising ATR**: Volatility increasing, often during trends or breakouts
70+
- **Falling ATR**: Volatility decreasing, often during consolidation
71+
72+
## Common uses
73+
74+
**Stop-loss placement:**
75+
```sql
76+
-- Stop at 2x ATR below entry
77+
entry_price - 2 * atr AS stop_loss
78+
```
79+
80+
**Position sizing:**
81+
```sql
82+
-- Risk 1% of account, sized by ATR
83+
(account_size * 0.01) / atr AS position_size
84+
```
85+
86+
:::note EMA vs Wilder's smoothing
87+
This recipe uses standard EMA smoothing via `avg(value, 'period', 14)` where α = 2/(N+1). Wilder's original ATR uses α = 1/N, which is more gradual. For exact Wilder smoothing with a 14-period lookback, use `avg(value, 'period', 27)`. Most modern platforms offer both variants.
88+
:::
89+
90+
:::info Related documentation
91+
- [EMA window function](/docs/query/functions/window-functions/reference/#avg)
92+
- [lag() function](/docs/query/functions/window-functions/reference/#lag)
93+
- [greatest() function](/docs/query/functions/numeric/#greatest)
94+
:::

‎documentation/cookbook/sql/finance/bid-ask-spread.md‎

Lines changed: 81 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,81 @@
1+
---
2+
title: Bid-ask spread
3+
sidebar_label: Bid-ask spread
4+
description: Calculate bid-ask spread metrics for transaction cost analysis and liquidity measurement
5+
---
6+
7+
The bid-ask spread is the difference between the best ask (lowest sell price) and best bid (highest buy price). It represents the cost of immediately executing a round-trip trade and is a key measure of market liquidity.
8+
9+
## Problem
10+
11+
You want to measure market liquidity and transaction costs. Narrow spreads indicate liquid markets with low trading costs, while wide spreads suggest illiquidity or market stress.
12+
13+
## Solution
14+
15+
```questdb-sql demo title="Calculate bid-ask spread metrics"
16+
DECLARE
17+
@symbol := 'EURUSD',
18+
@lookback := '$now - 1h..$now'
19+
20+
SELECT
21+
timestamp,
22+
symbol,
23+
round(bid_price, 5) AS bid,
24+
round(ask_price, 5) AS ask,
25+
round(ask_price - bid_price, 6) AS spread_absolute,
26+
round((ask_price - bid_price) / ((bid_price + ask_price) / 2) * 10000, 2) AS spread_bps,
27+
round((bid_price + ask_price) / 2, 5) AS mid_price
28+
FROM core_price
29+
WHERE symbol = @symbol
30+
AND timestamp IN @lookback
31+
ORDER BY timestamp;
32+
```
33+
34+
The query calculates:
35+
- **Absolute spread**: ask - bid
36+
- **Spread in basis points**: spread / mid_price × 10,000
37+
- **Mid price**: (bid + ask) / 2
38+
39+
## Aggregated spread analysis
40+
41+
```questdb-sql demo title="Average spread by time period"
42+
DECLARE
43+
@symbol := 'EURUSD',
44+
@lookback := '$now - 1d..$now'
45+
46+
SELECT
47+
timestamp,
48+
symbol,
49+
round(avg((ask_price - bid_price) / ((bid_price + ask_price) / 2) * 10000), 2) AS avg_spread_bps,
50+
round(min((ask_price - bid_price) / ((bid_price + ask_price) / 2) * 10000), 2) AS min_spread_bps,
51+
round(max((ask_price - bid_price) / ((bid_price + ask_price) / 2) * 10000), 2) AS max_spread_bps,
52+
count() AS quote_count
53+
FROM core_price
54+
WHERE symbol = @symbol
55+
AND timestamp IN @lookback
56+
SAMPLE BY 1h
57+
ORDER BY timestamp;
58+
```
59+
60+
## Interpreting results
61+
62+
- **Tight spread**: Highly liquid market conditions and lower immediate transaction costs
63+
- **Wide spread**: Illiquid or volatile period, higher transaction costs
64+
- **Spread widening**: Often precedes or accompanies volatility
65+
- **Intraday patterns**: Spreads typically widen during off-hours and narrow during active sessions
66+
67+
:::note Spread conventions (venue-dependent)
68+
Typical spreads vary a lot by venue, instrument, and session:
69+
70+
- FX majors: often around sub-pip to a few pips in liquid hours (roughly sub-1 to several bps)
71+
- FX minors/emerging pairs: typically wider than majors
72+
- Crypto: can range from tight to very wide depending on pair and exchange
73+
- Equities: often quoted in ticks/cents rather than bps
74+
75+
Treat these as rough guidelines, not fixed thresholds.
76+
:::
77+
78+
:::info Related documentation
79+
- [SAMPLE BY](/docs/query/sql/sample-by/)
80+
- [Aggregation functions](/docs/query/functions/aggregation/)
81+
:::

‎documentation/cookbook/sql/finance/donchian-channels.md‎

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
---
2+
title: Donchian Channels
3+
sidebar_label: Donchian Channels
4+
description: Calculate Donchian Channels to identify breakouts and trading ranges using highest high and lowest low
5+
---
6+
7+
Donchian Channels plot the highest high and lowest low over a period, creating a channel that tracks price range. Breakouts above the upper channel or below the lower channel often signal trend continuation.
8+
9+
## Problem
10+
11+
You want to identify breakout levels and trading ranges. Moving averages smooth price but don't show clear breakout levels. Donchian Channels show exactly where price needs to go to break out of its recent range.
12+
13+
## Solution
14+
15+
```questdb-sql demo title="Calculate 20-period Donchian Channels"
16+
DECLARE
17+
@symbol := 'EURUSD',
18+
@lookback := '$now - 1M..$now'
19+
20+
WITH channels AS (
21+
SELECT
22+
timestamp,
23+
symbol,
24+
close,
25+
max(high) OVER (
26+
PARTITION BY symbol
27+
ORDER BY timestamp
28+
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
29+
) AS upper_channel,
30+
min(low) OVER (
31+
PARTITION BY symbol
32+
ORDER BY timestamp
33+
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
34+
) AS lower_channel
35+
FROM market_data_ohlc_15m
36+
WHERE symbol = @symbol
37+
AND timestamp IN @lookback
38+
)
39+
SELECT
40+
timestamp,
41+
symbol,
42+
round(close, 5) AS close,
43+
round(upper_channel, 5) AS upper_channel,
44+
round(lower_channel, 5) AS lower_channel,
45+
round((upper_channel + lower_channel) / 2, 5) AS middle_channel
46+
FROM channels
47+
ORDER BY timestamp;
48+
```
49+
50+
The query calculates:
51+
- **Upper channel**: 20-period highest high
52+
- **Lower channel**: 20-period lowest low
53+
- **Middle channel**: Average of upper and lower
54+
55+
## Interpreting results
56+
57+
- **Price breaks above upper**: Bullish breakout, potential long entry
58+
- **Price breaks below lower**: Bearish breakout, potential short entry
59+
- **Price at middle**: Neutral zone
60+
- **Narrow channel**: Low volatility, breakout likely coming
61+
- **Wide channel**: High volatility, trend in progress
62+
63+
:::note Turtle Trading
64+
Donchian Channels were famously used by the Turtle Traders. Their system entered on 20-day breakouts and exited on 10-day breakouts in the opposite direction.
65+
:::
66+
67+
:::info Related documentation
68+
- [min/max window functions](/docs/query/functions/window-functions/reference/#min)
69+
- [Window functions overview](/docs/query/functions/window-functions/overview/)
70+
:::

0 commit comments

Comments
 (0)