04 Run the market queries
Seven queries against 26.5M ticks, each replacing a page of SQL: approximate counts, topK, OHLC candlesticks, percentiles, -If combinators, argMax, and the sort key finale.
These are ClickHouse's "functions that replace a page of SQL." Paste each block, run it, and
read the green box for what to expect. All of them run against the forex table you just
loaded.
4.1 Approximate vs exact — the headline trick
How many distinct quote timestamps are in 26.5M ticks? Three functions answer the same question with different trade-offs. Run them one at a time and watch the timer in the query stats — running them separately is the whole point.
-- Approximate count of distinct timestamps (HyperLogLog)
SELECT uniq(datetime) AS distinct_ts FROM forex;-- Exact count — precise, but scans every value
SELECT uniqExact(datetime) AS distinct_ts FROM forex;-- Adaptive + tunable accuracy vs memory
SELECT uniqCombined(datetime) AS distinct_ts FROM forex;You should see
About 24.6 million distinct timestamps from all three. But uniq returns in ~0.2 s,
uniqExact takes ~3 s (about 17× slower) for the precise number, and uniqCombined lands
within ~0.4% in well under a second. At billions of rows, that gap is the difference between
instant and a coffee break.
4.2 Top-N in one function
The most actively quoted pairs — no GROUP BY / ORDER BY / LIMIT needed:
-- One row: an array of the 8 most actively quoted pairs (by tick count)
SELECT topK(8)(pair) AS most_active FROM forex;You should see
A single cell holding an array of 8 pairs, most-quoted first (gold, XAU/USD, tops it).
topK collapses the whole table into that ranked list in one pass — it stands in for a
GROUP BY … ORDER BY count() DESC LIMIT 8.
4.3 Time-series in one pass — OHLC candlesticks
Daily open / high / low / close for gold — the bread-and-butter market query:
-- One row per day: gold (XAU/USD) open, high, low, close — a candlestick chart
SELECT
toDate(datetime) AS day,
argMin(bid, datetime) AS open, -- bid at the day's first tick
max(bid) AS high,
min(bid) AS low,
argMax(bid, datetime) AS close -- bid at the day's last tick
FROM forex
WHERE base = 'XAU' AND quote = 'USD'
GROUP BY day
ORDER BY day;You should see
One row per day for January 2020. argMin(bid, datetime) is the bid at the day's first tick
(the open) and argMax the last (the close) — no window functions, no self-joins. Watch gold
climb from ~1,520 to ~1,610 across the month.
4.4 Percentiles without window gymnastics
The bid/ask spread is a liquidity gauge — and averages hide the tail, so use percentiles:
-- One row per pair: median and 99th-percentile bid/ask spread (tighter = more liquid)
SELECT
pair,
round(quantile(0.5)(ask - bid), 6) AS median_spread,
round(quantile(0.99)(ask - bid), 6) AS p99_spread
FROM forex
GROUP BY pair
ORDER BY median_spread ASC;You should see
One row per pair, tightest spread first. EUR/USD comes out most liquid (~0.00002, sub-pip);
gold the widest. quantile is computed approximately in one pass — no sorting the whole column.
4.5 Combinators — one scan, two answers
Average spread during active vs quiet trading hours, side by side, in a single scan:
-- One row per pair: avg spread during the active window vs quiet hours
SELECT
pair,
count() AS ticks,
round(avgIf(ask - bid, toHour(datetime) BETWEEN 7 AND 20), 6) AS spread_active,
round(avgIf(ask - bid, toHour(datetime) NOT BETWEEN 7 AND 20), 6) AS spread_quiet
FROM forex
GROUP BY pair
ORDER BY ticks DESC;You should see
Active-window and quiet-hours spreads in the same result. The -If combinator bolts a
condition onto any aggregate: avgIf(x, cond) averages x only where cond is true — no
two-pass query, no CASE WHEN. Nearly every aggregate takes it (countIf, sumIf,
quantileIf, …).
4.6 The latest value — argMax
The most recent quote for every pair, in one pass:
-- One row per pair: the latest quoted bid and the timestamp it was seen
SELECT
pair,
argMax(bid, datetime) AS last_bid,
max(datetime) AS as_of
FROM forex
GROUP BY pair
ORDER BY pair;You should see
The latest bid per pair. argMax(bid, datetime) returns the bid from the row with the
greatest datetime — the everyday "last price / latest status" query, without a window
function or self-join.
4.7 The finale — sort key vs no sort key
Same query shape — count the ticks matching a condition — but one filter hits the sort key and the other doesn't. First, run both counts:
-- Optimized: base+quote ARE the leading sort key — the index skips to that pair
SELECT count() FROM forex WHERE base = 'XAU' AND quote = 'USD';
-- Non-optimized: bid is NOT in the sort key — no index, scans all 26.5M ticks.
-- (cache off so the full scan shows on every run)
SELECT count() FROM forex WHERE bid > 1.5
SETTINGS use_query_condition_cache = 0;Hard to spot the difference?
Both counts come back in a few milliseconds, so the timing alone barely moves — count() is
fast either way. The real difference is how much data each one has to touch. To actually see
it, ask ClickHouse to show its plan with EXPLAIN indexes = 1, which reports how many
granules (blocks of ~8,192 rows) it will read.
-- Optimized: the primary index skips straight to the XAU/USD rows
EXPLAIN indexes = 1
SELECT count() FROM forex WHERE base = 'XAU' AND quote = 'USD';
-- Non-optimized: bid isn't in the sort key, so nothing can be skipped
EXPLAIN indexes = 1
SELECT count() FROM forex WHERE bid > 1.5;You should see
In the optimized plan, the Indexes → PrimaryKey section shows only a slice of granules
selected — about 492 / 3,234 (~16K rows). In the non-optimized plan there's no index to
apply, so it reads 3,234 / 3,234 granules — every single row. Same query shape, wildly
different work. The lesson: put the columns you filter on most at the front of your
ORDER BY.
Keep this one on your clipboard
You'll hand that non-optimized WHERE bid > 1.5 query to the AI in the
next module.
03 Load the data — two ways
The same 26.5M ticks loaded twice: ClickPipes, the managed pipeline you would use in production, then the s3() one-liner — and when to reach for which.
05 Ask the AI to fix a query
Hand the full-scan query from module 04 to the ClickHouse Assistant and watch it diagnose the missing sort key, then offer a skip index and a projection with ready-to-run SQL.