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