Tutorials, performance tips, interview guides, and database deep-dives — written to make SQL click.
A selective filter doesn't guarantee a small scan. Data skipping needs statistics, clustering, and untruncated values all at once -- an interactive diagnostic finds which one failed.
Broadcast, even shuffle, skewed shuffle -- same JOIN, same data size, wildly different runtimes. An interactive breakdown of all three and the thresholds that decide.
Waiting at capacity, waiting for compute, compilation, execution -- four phases make up the wall clock. An interactive breakdown of which one ate yours.
Result cache, disk cache, UI cache -- three different things, and only one survives a warehouse restart. An interactive diagnostic to find out which applied.
Your SQL can be unchanged and the dashboard still wrong. How DataOps adds data testing, observability and SLAs, with DORA metrics in SQL.
Git, pull requests, automated data tests, versioned migrations and safe Dev to Test to Prod promotion, with a GitHub Actions + dbt + Snowflake example.
Every warehouse size step doubles the credit cost, independent of the SQL running on it. An interactive credit calculator shows why sizing usually matters more.
A query that outgrows warehouse memory spills to local disk first, then remote storage — and remote spill is far worse. An interactive breakdown of each.
Result cache, warehouse cache, metadata cache — three different things. An interactive diagnostic to find out which one actually applied.
A tiny bytes-scanned number doesn't guarantee a fast query. Four real bottlenecks, with an interactive breakdown of where the time actually goes.
The full 4-step reconciliation framework, plus a live migration example where matching row counts hid three separate bugs.
"Remove the duplicates" is at least seven different problems. A live example where SELECT DISTINCT quietly misses two of them.
From JOIN fan-out to the NOT IN + NULL trap that silently returns zero rows — ten traps every SQL writer eventually hits, with a live example.
A dashboard that "grew" 135% overnight — and the one-line JOIN bug behind it. Build your own fan-out and watch it happen live.
The exact multiplier behind an inflated SUM(), three fixes ranked by robustness, and why AVG() breaks the same way. Live example included.
Right row count, wrong SUM. A live, runnable example of the JOIN fan-out bug hiding in plain sight, plus a free dataset and challenge.
How Genie turns plain English into SQL, what Genie spaces and trusted assets are, and how to set one up that's actually accurate.
AI_COMPLETE, Cortex Analyst, Cortex Search, and the CoCo agent — what each piece does, with real SQL you can run today.
Late-arriving data, backdated corrections, and re-run vs incremental recompute — root-causing metric drift.
Column-by-column CASE diffing and row hashing to pinpoint exactly what changed in a record, not just that it did.
Schema drift checks, row count comparisons, and checksum-based row diffing across environments.
Caching lag, timezone cutoffs, hidden BI filters, and aggregation-grain mismatches, with a real revenue example.
Join fan-out, WHERE vs JOIN condition placement, and implicit NULL exclusion — the usual suspects, explained.
EXCEPT, MINUS, and FULL OUTER JOIN — find exactly which rows and columns differ, with a real reconciliation example.
CDC design, schema evolution handling, and cost tradeoffs — architecture scenarios, not SQL syntax quizzes.
Partition pruning, clustering keys, materialized views, and right-sizing — the SQL-level levers that control your bill.
Open table formats compared — ACID on a data lake, time travel SQL syntax, and adoption guidance for each.
Credits, DBUs, or bytes scanned — pricing models, SQL syntax differences, and which workloads fit each platform.
Why cheap warehouse compute made load-then-transform the default, with SQL examples and when ETL still wins.
Row count reconciliation, null-rate checks, referential integrity, duplicate detection, and anomaly detection queries.
Added, dropped, and renamed upstream columns — schema-on-read vs contracts, and safe ALTER TABLE strategies.
Watermarks, idempotent MERGE loading, late-arriving data, and when a full reload is still the safer call.
Query-based, trigger-based, and log-based CDC compared — with real SQL Server CDC and MERGE-based apply examples.
Overwrite, version, remember one prior value, or a separate history table — four ways to handle a changing dimension.
Schema-on-write vs schema-on-read, and how open table formats let a lakehouse borrow the best of both.
Raw, cleaned, business-ready — the three-layer pattern behind almost every modern lakehouse pipeline, with SQL for each layer.
Grain, additive vs semi-additive vs non-additive facts, and the three fact table types — with SQL DDL.
Denormalized and fast to query, or normalized and storage-efficient — the dimensional modeling tradeoff, with real DDL.
LAG() with a 1-row offset — and why MoM is noisier than YoY for any business with a real seasonal cycle.
Sample ratio mismatch, conversion rate per variant, and where SQL's job ends and a statistics tool's job begins.
PERCENTILE_CONT vs PERCENTILE_DISC, per-group medians, and the manual workaround for MySQL, which has no native equivalent.
ROW_NUMBER with PARTITION BY, wrapped in a CTE — the classic greatest-n-per-group pattern, plus a correlated-subquery alternative.
SUM() OVER() as the denominator — no self-join, no subquery, plus the PARTITION BY variant for percent-of-group.
The CASE WHEN + aggregate crosstab pattern that works everywhere, plus SQL Server's PIVOT and PostgreSQL's crosstab().
UTC midnight isn't local midnight — why that quietly shifts every "daily" report, and the DST day that's 23 or 25 hours long.
A zero-order day produces no row at all. Building a date spine with GENERATE_SERIES or a recursive CTE makes the gap visible.
DATE_TRUNC, DATEPART, DATE_FORMAT — and the week-start default that silently shifts weekly totals across databases.
Three ways to represent "nothing" — and why defaulting every NULL to 0 can quietly change an average.
JSON_VALUE, JSON_EXTRACT, and the ->> operator across three databases, plus expanding a JSON array into rows.
SUBSTRING, TRIM, REPLACE, CONCAT, and REGEXP_REPLACE across PostgreSQL, MySQL, and SQL Server, with real cleaning examples.
Trimming, standardizing labels, blanks vs. NULLs, and a staging-table workflow that keeps raw data recoverable.
Fast for one customer, glacial for another — the same cached plan, and the RECOMPILE/OPTIMIZE FOR fixes that actually work.
Parameterized queries are the primary defense — but ORMs, dynamic sorting, and "safe" stored procedures still let it slip through.
Find the exact column and value with the modern detailed error or trace flag 460 — then widen the column instead of silently truncating.
Two joined tables share a column name, and SQL won't guess which one you mean — qualify it in every clause, not just SELECT.
A dropped default database or an orphaned login after a restore — three fixes, from a one-time connection override to sp_change_users_login.
Stickiness ratio, feature adoption, A/B test conversion — questions built around how product teams measure success.
Index selectivity, phantom reads, rewriting a correlated subquery, cyclic recursive CTEs — judgment, not memorization.
Marketplace-style schema, metrics-driven questions — top categories, late deliveries, seller trends, and more.
Churned customers, duplicate charges, biggest single-day sales drop — real business problems, fully worked.
SCD Type 2, late-arriving data, dedup, data quality checks, and idempotent upserts — pipeline reasoning, not syntax trivia.
Three commands, one shared goal, three very different blast radii — and only one leaves nothing behind.
Same job, holding an intermediate result — but scope, reuse, and indexing all differ between the three.
One extra word skips an entire sort-or-hash pass over the combined result — here's exactly what UNION costs.
One runs before aggregation and can use an index. The other runs after, on the results, and can't.
A library system, a grade tracker, a movie ratings database — full schema, sample data, and queries included.
Top products, revenue trend, RFM segmentation, and repeat purchase rate — five queries with real analytical range.
Readmission rate, length of stay, cost per diagnosis — plus exactly which datasets are safe to use publicly.
Raw, staging, dimension, fact, mart — five SQL layers that turn a messy CSV into a reporting-ready table.
8 project ideas, real free datasets, and what actually earns a recruiter's extra minute of attention.
DELETE WHERE id = ... doesn't work with no id. ROW_NUMBER() manufactures the identity a duplicate-riddled table never had.
Trailing vs. centered averages, edge-of-data behavior, and the ROWS vs RANGE gotcha that also bites running totals.
One LAG() call gets same-month-last-year. The real trap is comparing a full period against a partial one.
Surrogate keys, effective dates, and the MERGE statement that keeps a dimension's full history without ever overwriting a row.
Missing invoice numbers, login streaks, uptime windows — one ROW_NUMBER subtraction trick solves all of them.
No session_id column? LAG(), a gap threshold, and a running sum build one from a raw events table.
Conditional aggregation gets a loose funnel. Enforcing step order needs one more trick — both worked through in full.
SUM() OVER (ORDER BY ...) does the job — until tied dates expose a frame bug almost everyone hits once.
Assign cohorts, calculate period offsets, and pivot into the classic cohort-retention triangle — the full query, one CTE at a time.
NOT NULL, UNIQUE, CHECK, and DEFAULT — plus the MySQL CHECK constraint history that silently did nothing before 8.0.16.
READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE — dirty reads, phantom reads, and each database's default.
Atomicity, Consistency, Isolation, and Durability, explained with one running bank-transfer example.
One identifies the row, one enforces uniqueness on the side, one points at another table — with the NULL-handling gotcha in SQL Server.
A saved query that's always fresh, vs a stored result that's fast to read — when to use each, database by database.
UPDATE...FROM vs UPDATE JOIN vs Oracle's updatable subquery — and the multi-match trap that silently picks a random row.
Why OFFSET gets slower on every deeper page, and how keyset pagination keeps it constant — with before/after timing.
Auto-numbering primary keys in MySQL, PostgreSQL, SQL Server, and Oracle — and why gaps in the sequence are normal.
Pagination syntax across four databases, plus the classic ROWNUM-before- ORDER-BY trap that returns the wrong rows.
INSERT ON CONFLICT vs ON DUPLICATE KEY UPDATE vs MERGE — and why every upsert needs a unique constraint to work at all.
String aggregation syntax across PostgreSQL, SQL Server, MySQL, and Oracle — ordering, NULL handling, and separators.
How it differs from GROUP BY, multiple-column partitions, and the top-N-per-group pattern with ranking functions.
The offset and default arguments, PARTITION BY, month-over-month change, and gap detection — no self-join required.
COUNT, SUM, AVG, MIN, and MAX — how each one handles NULL, and how they work with and without GROUP BY.
It's rarely the syntax — join fan-out, NULL assumptions, and not narrating your reasoning out loud.
Correlated vs non-correlated, IN vs EXISTS, and the NOT IN with NULL trap — with output tables.
The ungrouped column error, WHERE vs HAVING order, and finding duplicates — with wrong vs correct output tables.
ROW_NUMBER vs RANK vs DENSE_RANK with ties, running totals, and LAG/LEAD — with output tables.
Simple vs searched CASE, pivoting with conditional aggregation, and the missing-ELSE trap — with output tables.
Cartesian products, self-joins, and join fan-out — six join questions with wrong vs correct output tables.
Given this table and this query — what comes out? Six predict-the-output questions with wrong vs correct results.
Eight classic traps — NULL comparisons, second-highest salary, UNION vs UNION ALL — with wrong vs correct output tables.
Why WHERE can't see a SELECT alias but ORDER BY can — and how GROUP BY, HAVING, and table aliases scope differently.
GROUP BY doesn't need COUNT() or SUM() — at its core it's a deduplication tool. How it compares to DISTINCT.
You can't filter on a total that doesn't exist yet — the logical reason HAVING comes after GROUP BY, and how it differs from WHERE.
Seven mistakes that pass code review but quietly wreck performance — each with a fix and before/after timing.
Diagnose, rewrite, index, verify, monitor — the exact order that actually works, with a full before/after example.
From your first EXPLAIN ANALYZE to cost numbers, join algorithms, and spotting a bad row estimate.
Five situations where adding an index makes performance worse, not better — with real INSERT timing before and after.
What each scan type does physically, and the selectivity math that decides which one the optimizer picks.
B-trees, single-column, composite, and covering indexes — how each one actually works, with real query timing.
Six root causes behind almost every slow query — missing indexes, bad predicates, SELECT *, and more — with before/after timing.
What actually happens between your query and its results: statistics, execution plans, and why two "equivalent" queries can run at very different speeds.
ROLLUP, CUBE, GROUPING SETS, and conditional aggregation — GROUP BY tricks that go well beyond the basics.
Walking a hierarchy — org charts, category trees, and bill-of-materials — with WITH RECURSIVE, explained from the ground up.
The subquery that references its outer query, row by row. How they work, why they're slow, and when they're worth it.
Same job, different tools. When a CTE makes a query more readable — and when a subquery is actually the better call.
Three ranking functions that look similar and behave very differently with ties. Which one to reach for, with side-by-side output.
OVER(), PARTITION BY, and running totals — window functions demystified with the simplest possible examples, no jargon required.
A 60-second debugging checklist: NULL logic, join fan-out, integer division, precedence, and more.
Short answer: after — always. Why, plus exactly what you can sort by once you've grouped.
Why = NULL never works, three-valued logic, the NOT IN trap,
and COALESCE — with fixes.
One counts rows, one skips NULLs, one counts unique values — proven on a single dataset. Plus the COUNT(1) myth.
Seven GROUP BY traps — from non-grouped columns to join fan-out — each with a clear fix and example.
Venn diagrams, sample tables, and the exact rows each join returns — plus the classic LEFT-JOIN gotcha.
Row filter, group filter, or projection? What each clause really does — with real, side-by-side SQL.
SQL doesn't run in the order you write it. The real logical order — and why you can't filter on a SELECT alias.
New articles are on the way.