SQL Quick Reference

SELECT   col, agg(col) AS aliasFROM     db.tableWHERE    row_conditionGROUP BY colHAVING   group_conditionORDER BY alias DESCLIMIT    100;

a = b        a != ba > b        a >= b        a BETWEEN x AND ya IN (1, 2, 3)s LIKE 'prefix%'col IS NULL   col IS NOT NULLcond1 AND cond2        cond1 OR (cond2 AND cond3)

-- WHERE filters rows before grouping-- HAVING filters groups after grouping (can use aggregates)SELECT customer_id, sum(amount) AS totalFROM staging.ordersWHERE status = 'completed'GROUP BY customer_idHAVING total > 1000;
-- group by an expressionGROUP BY toStartOfMonth(created_at)

count()            -- rowscount(col)         -- non-null values of colsum(col)  avg(col)  min(col)  max(col)uniq(col)          -- approximate distinct count (fast)uniqExact(col)     -- exact distinct count
-- conditional forms: normal args + a trailing conditioncountIf(amount >= 1000)sumIf(amount, status = 'completed')avgIf(amount, status = 'completed')uniqIf(customer_id, segment = 'pro')

FROM fact AS fLEFT JOIN dim AS d  ON f.key = d.key   -- keep all fact rows; dim cols NULL if no matchINNER JOIN dim AS d ON f.key = d.key   -- keep only rows that match on both sides

WITH step_one AS(    SELECT customer_id, sum(amount) AS revenue    FROM staging.orders    GROUP BY customer_id),step_two AS(    SELECT customer_id FROM step_one WHERE revenue > 1000)SELECT * FROM step_two;
-- a CTE can feed an insertWITH s AS ( SELECT ... )INSERT INTO mart.t (a, b)SELECT a, b FROM s;

CREATE DATABASE IF NOT EXISTS analytics;
CREATE TABLE IF NOT EXISTS analytics.orders(    order_id     UInt64,    customer_id  UInt64,    created_at   DateTime,    amount       Float64,    status       String,    loaded_at    DateTime DEFAULT now())ENGINE = MergeTreePARTITION BY toYYYYMM(created_at)     -- optional; coarse onlyORDER BY (created_at, order_id);      -- sort key: lead with filter columns

MergeTree                 keep every rowReplacingMergeTree(ver)   latest ver per sort key wins (eventually)SummingMergeTree          sum additive columns per sort key (eventually)

UInt8 UInt16 UInt32 UInt64        non-negative integersInt32 Int64                       signed integersFloat32 Float64                   approximate decimalsDecimal(P, S)                     exact decimals (money)String                            textDate  DateTime  DateTime64(3)     date / second / millisecond precisionNullable(T)                       T or NULLLowCardinality(String)            few distinct string values

now()                                   -- current timestamptoday()                                 -- current datetoStartOfDay(ts)   toStartOfHour(ts)toStartOfMonth(ts) toStartOfWeek(ts)toStartOfInterval(ts, INTERVAL 30 MINUTE)toYYYYMM(ts)       toYYYYMMDD(ts)       -- partition keysts >= now() - INTERVAL 3 HOUR           -- lookback window

from clickhouse_connect import get_client
client = get_client(host="clickhouse", port=8123,                    username="default", password="password")

# pass values as parameters, never with f-stringsdf = client.query_df(    "SELECT * FROM analytics.orders WHERE created_at > {since:DateTime}",    parameters={"since": last_processed},)

# 1. watermark from the targetres = client.query("SELECT maxOrNull(created_at) FROM mart.customer_metrics")since = res.result_columns[0][0]
# 2. read a recomputed window (covers late data), or everything on first runsql = "SELECT * FROM staging.orders"params = {}if since is not None:    sql += " WHERE created_at >= {since:DateTime}"    params = {"since": since}
df = client.query_df(sql, parameters=params)if df.empty:    return
# 3. idempotent write: replace the affected partition, then insert