SQL patterns that come up every week: CTEs, window functions, JSON, indexes, and the dialect quirks between engines.
How to use common table expressions in SQL Server. Covers WITH syntax, chaining CTEs, recursive CTEs for hierarchies, the MAXRECURSION 100 default, the semicolon quirk, and CTE vs temp table.
How to use window functions in SQL Server with OVER, PARTITION BY, and frame clauses. Covers ROW_NUMBER, RANK, LAG/LEAD, the RANGE default gotcha, and the 2022 WINDOW clause and IGNORE NULLS.
How data type choice drives compression and query speed in ClickHouse. Covers right-sizing integers, LowCardinality dictionary encoding and when it helps, Enum vs LowCardinality, the real cost of Nullable, FixedString, Date vs DateTime sizing, and avoiding String for everything.
How ClickHouse data skipping indexes work, the five index types (minmax, set, bloom_filter, ngrambf_v1, tokenbf_v1), how GRANULARITY controls the index block size, when a skip index actually helps, and the correlation rule that decides whether it skips anything at all.
How to model key-value and repeated-group data in ClickHouse with Map(K, V), Nested, and Tuple. Covers m[k] access and its linear cost, mapKeys/mapValues subcolumns, mapContains, the Nested-is-parallel-arrays model, the flatten_nested setting, and when to reach for a Map versus a Nested versus parallel arrays.
A practical guide to the MergeTree family in ClickHouse: plain MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree and VersionedCollapsingMergeTree. How merges work, what each engine deduplicates or pre-aggregates, the eventual-consistency trap, and how to pick one.
How PARTITION BY works in ClickHouse: choosing a partition key, partition pruning, partition-level operations (DROP, DETACH, ATTACH, MOVE, FREEZE), the too-many-parts problem, and why most teams over-partition.
How projections work in ClickHouse: normal (reordering) projections and aggregating projections, how the optimizer picks them, the storage and write-amplification trade-offs, backfilling existing data, and when a materialized view is the better choice.
How ClickHouse table functions let you SELECT from and INSERT into S3, files, URLs, remote ClickHouse, PostgreSQL, and MySQL without loading data first. Covers s3, file, url, remote, postgresql, mysql, schema inference, glob patterns, and when to promote a table function into a permanent table engine.
How to update and delete rows in ClickHouse: heavyweight mutations (ALTER TABLE UPDATE/DELETE), lightweight deletes, lightweight updates, why they rewrite parts, how to monitor mutation progress in system.mutations, and engine-based alternatives that avoid mutations entirely.
Working with arrays in ClickHouse: building and indexing them, arrayJoin to unfold rows, the higher-order functions arrayMap/arrayFilter/arrayReduce with lambdas, the ARRAY JOIN clause, and the GROUP BY arrays produced by groupArray.
How the WITH clause works in ClickHouse: scalar expressions, named subquery CTEs, the inlining-by-default behavior that catches people out, materialized CTEs, and recursive CTEs for hierarchical data.
How to work with dates and times in ClickHouse: truncation with toStartOf*, dateDiff and date arithmetic, formatting and parsing, time zones, and the rounding gotchas that catch newcomers.
How dictionaries work in ClickHouse: creating them with CREATE DICTIONARY, the dictGet family of lookup functions, choosing a layout (flat, hashed, complex_key_hashed, range_hashed, cache, direct), and using a dictionary as a fast alternative to JOIN.
How incremental materialized views work in ClickHouse: the trigger-on-insert model, the target table and TO syntax, rolling up with SummingMergeTree and AggregatingMergeTree, the -State and -Merge combinators, and the gotchas around backfilling and source-table coupling.
How sampling works in ClickHouse: declaring a SAMPLE BY key, the SAMPLE k / SAMPLE n / SAMPLE k OFFSET m forms, scaling aggregates with _sample_factor, determinism, and when approximate results are worth the speedup.
Working with strings in ClickHouse: length and case, substrings with 1-based indexing, splitting and joining, search and replace including regular expressions, and the UTF-8 versus byte distinction that trips people up.
How TTL works in ClickHouse: row-level deletes, column-level expiry, moving data between storage tiers, rollups with TTL GROUP BY, recompression, when TTL actually fires during merges, and partitioning so whole partitions drop instead of rewriting parts.
How to use ClickHouse aggregate functions, including the -If/-Array/-State/-Merge combinators, approximate counting with uniq, quantiles, and groupArray for analytical queries.
A practical guide to JOINs in ClickHouse: the supported join types and strictness modifiers, the join algorithms you can configure, GLOBAL JOIN for distributed tables, and when a dictionary beats a join.
How to query JSON in ClickHouse, comparing the String + JSONExtract approach with the native JSON data type introduced in v24.8, plus parsing, paths, and performance tradeoffs.
How to use window functions in ClickHouse with OVER, PARTITION BY, and frame clauses. Covers ROW_NUMBER, RANK, LAG/LEAD, moving averages, and the RANGE-default gotcha.
How to use MySQL EXPLAIN, EXPLAIN FORMAT=JSON, and EXPLAIN ANALYZE (8.0.18+) to understand query execution plans, identify slow spots, and optimize queries.
How MySQL generated columns work -- the difference between VIRTUAL and STORED, when to index them, practical use cases for computed columns, and the limitations to know.