SQL

SQL

Query plans, joins, and the index the interviewer hopes you mention.

• SQL Analytics

  6 studies
  1. 1.SQL Analytics — Window Functions, CTEs & Set-Based ThinkingSenior interviews fail when SQL is written as a loop over rows. Window functions, CTEs, and set-based thinking turn Top-N, running totals, and hierarchies into declarative plans. This hub maps the cluster. Indexes and EXPLAIN stay in the existing SQL Indexes pages.
  2. 2.Window Functions — PARTITION BY, ORDER BY & FramesOVER () computes an aggregate without collapsing rows. Interviews love frames because the default is surprising. Master PARTITION BY, ORDER BY, and ROWS vs RANGE before ranking and running totals.
  3. 3.Ranking & Top-N — ROW_NUMBER, RANK, DENSE_RANK, NTILETop-N per group is a senior interview staple. The wrong ranking function silently changes product behavior on ties. Compare ROW_NUMBER, RANK, DENSE_RANK, NTILE, and Postgres DISTINCT ON.
  4. 4.Running Totals, LAG/LEAD & Gaps-and-IslandsTime-series analytics in SQL is cumulative sums, period-over-period deltas, and session islands. These are window problems first. Recursive CTEs are usually overkill for a linear sequence.
  5. 5.CTEs — Non-Recursive vs Recursive HierarchiesWITH clauses stage complex analytics. Recursive CTEs walk trees such as org charts. Interviews probe readability versus materialization, and how you stop a cycle.
  6. 6.LATERAL Joins vs Correlated Subqueries — When to Choose WhatLATERAL lets a subquery or set-returning function see columns from preceding FROM items. It shines for per-row Top-N and table functions. Compare it with correlated subqueries and with a window Top-N.

• SQL indexes

  6 studies
  1. 1.Indexes, Cardinality & EXPLAIN PlansB-tree vs hash; selectivity; composite/covering; Seq/Index/Bitmap; Nested/Hash/Merge; EXPLAIN ANALYZE pitfalls.
  2. 2.B-tree vs Hash vs GIN vs GiSTB-tree is the default for equality + range + ORDER BY. Hash is equality-only (niche). GIN shines for arrays/JSONB/full-text (inverted). GiST powers geometry, ranges, and trigram similarity. Pick the access method by operator class, not vibes.
  3. 3.Composite & Covering IndexesComposite indexes follow leftmost prefix rules — (a,b,c) serves a, a,b, a,b,c, not b alone. Covering indexes (INCLUDE / index-only scans) store enough columns to avoid heap fetches. Column order is equality first, then range/sort.
  4. 4.Selectivity, Cardinality & StatisticsSelectivity is the fraction of rows matching a predicate. Cardinality is the estimated row count after filters/joins. Planners use statistics (ANALYZE) — n_distinct, MCV, histograms — to choose Seq Scan vs Index Scan. Bad stats mean bad plans.
  5. 5.EXPLAIN & EXPLAIN ANALYZEEXPLAIN shows the plan plus estimates. EXPLAIN (ANALYZE, BUFFERS) executes and shows actual time/rows plus buffer hits. Always compare Plan Rows vs Actual Rows; big mismatches mean stats or correlation problems. Never run ANALYZE on unchecked writes in prod without a transaction you can roll back.
  6. 6.Partial & Expression IndexesPartial indexes index only rows matching a WHERE (e.g. WHERE deleted_at IS NULL) — smaller, hotter cache, perfect for sparse predicates. Expression / functional indexes index LOWER(email) or (data->>'sku') so queries using the same expression can seek. Match expressions exactly.

• SQL Query Execution & Optimizer

  6 studies
  1. 1.How a SQL Query Actually Executes - Parse, Rewrite, Plan, Execute, Volcano vs Vectorized & Reading Plan TreesInterview hub: parse -> analyze -> rewrite -> plan -> execute; Volcano iterator vs vectorized vs compiled execution (runnable model counting next() calls, LIMIT pipelining); real PostgreSQL 17 run where one query shape gets index+nested-loop vs seq-scan+hash-join plans depending on the constant; reading plans top-down (control) vs bottom-up (data), inclusive vs exclusive time and q-error (runnable); PG vs MySQL vs SQLite vs DuckDB planner contrasts.
  2. 2.Join Algorithms - Nested Loop vs Index Nested Loop vs Hash Join vs Merge JoinNested loop vs index nested loop vs hash join vs merge join: runnable work-count model, textbook I/O cost formulas and Postgres-style batch math (runnable), real PG 17 plans forcing each algorithm (incl. Memoize) and a hash join spilling to Batches: 8 under small work_mem; when each wins table, the planned-10-got-1M nested loop failure, MySQL (no merge join, hash join 8.0.18+), SQLite automatic indexes, DuckDB range joins.
  3. 3.Cost-Based Optimizer & Join Ordering - Cost Constants, Dynamic Programming, Left-Deep vs Bushy, join_collapse_limit & GEQOPostgreSQL cost constants verified exactly against pg_class (Seq Scan = relpages + reltuples x cpu_tuple_cost); random_page_cost 4 vs 1.1 flipping seq vs index scan in real PG 17; runnable index-vs-seq crossover model and physical correlation; runnable Selinger DP join enumeration with left-deep vs bushy and the n!/Catalan explosion; join_collapse_limit/from_collapse_limit/GEQO with a real written-order plan; CBO vs rule-based vs Cascades vs adaptive vs learned.
  4. 4.Cardinality Misestimates & Plan Regressions - Correlated Columns, Extended Statistics, Generic Plans & the Slow-Overnight RunbookWhy estimates go wrong: independence assumption with correlated and anti-correlated columns (runnable q-errors and multiplicative error growth, Leis et al. VLDB 2015); real PG 17 CREATE STATISTICS dependencies/ndistinct fixing estimates; stale stats and out-of-range values; custom vs generic plans for skewed prepared statements (real plans + runnable heuristic sim), parameter sniffing contrast; auto_explain/pg_stat_statements; step-labeled slow-overnight runbook; pg_hint_plan and plan-pinning trade-offs.
  5. 5.Sorting, Aggregation, Spills & Parallel Query - External Merge, Top-N, HashAggregate vs GroupAggregate & work_mem MathSort strategies (quicksort, external merge, top-N heapsort, incremental sort) with a runnable external merge sort pass counter and top-N vs full sort comparisons; HashAggregate vs GroupAggregate; real PG 17 spills (external merge Disk, HashAggregate Batches/Disk Usage, hash_mem_multiplier) and a Gather/Partial Aggregate parallel plan; runnable work_mem worst-case sizing math; temp-file monitoring; MySQL/SQLite/DuckDB/warehouse contrasts.
  6. 6.Query Rewrites & SARGability - Unnesting, NOT IN vs NOT EXISTS, OR to UNION, Keyset Pagination & ORM N+1Subquery unnesting to semi/anti joins vs the NOT IN NULL trap (real PG 17 plans and counts plus runnable sqlite3 repro); SARGability: functions and casts on columns vs half-open ranges, MySQL string-vs-number index rule; BitmapOr vs OR-to-UNION; OFFSET vs keyset pagination (150,020 vs 20 rows read in real PG); runnable ORM N+1 fingerprint detector; rewrite catalog table.