SQL Analytics
Studies in this cluster, in series order. Each one keeps its own URL.
SQL
Query plans, joins, and the index the interviewer hopes you mention.
SQL Analytics
6 studies- 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.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.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.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.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.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.