81 Advanced SQL Interview Questions and Answers (2026)

A window function computes a value across a set of rows (a "window") related to the current row, but unlike GROUP BY, it does not collapse those rows: every input row is preserved and gets its own computed value alongside the detail.

• Aggregate + GROUP BY collapses rows: N input rows become one row per group; you lose access to the individual detail rows in the same query.

• Window functions preserve rows: Declared with OVER (...), they run after the WHERE/GROUP BY/HAVING and return a value per row while keeping all rows visible.

• When to choose which:

  • Use GROUP BY when you only want the summary (one total per category).

  • Use a window function when you need the detail and the aggregate together (each row plus its group total, a running sum, or a rank).

Without PARTITION BY, the entire result set is treated as one single partition, so the window function computes across all rows together.

• One global window: e.g. AVG(salary) OVER () returns the overall average on every row.

• Behavior still depends on ORDER BY: With no ORDER BY, the aggregate spans all rows uniformly. With ORDER BY but no partition, a running/cumulative calculation is applied across the whole set.

• It is optional: PARTITION BY simply subdivides the data; omitting it is valid and common for whole-table totals or ranks.

Inside OVER (...), PARTITION BY splits rows into independent groups and ORDER BY sequences the rows within each group; together they determine which rows the function sees and in what order for the current row's calculation.

• PARTITION BY: resets the calculation per group: Analogous to GROUP BY but without collapsing rows; the function restarts independently for each partition (e.g. per department).

• ORDER BY: sequences rows within the partition: Required for order-sensitive functions (ROW_NUMBER, LAG, running totals) and it implicitly defines a default frame from the start of the partition to the current row.

• Together they define the window: For any given row, the window is: its partition, ordered as specified, bounded by the frame clause.

Window functions perform calculations across a related set of rows while keeping each row intact, which makes them ideal for analytics like ranking, running totals, and row-to-row comparisons that plain aggregates cannot express.

• Ranking functions: ROW_NUMBER() (unique sequence), RANK() (gaps on ties), DENSE_RANK() (no gaps): e.g. rank employees by salary within a department.

• Offset/navigation functions: LAG() and LEAD() access previous/next rows: e.g. compare a month's revenue to the prior month.

• Aggregate windows: SUM(), AVG() over a frame for running totals and moving averages.

• Distribution functions: NTILE(), PERCENT_RANK(), CUME_DIST() for buckets and percentiles.

Many window functions are inherently order-dependent: their result only has meaning once the rows have a defined sequence, so ORDER BY is required (or strongly needed) to make the output deterministic.

• Order-defined functions require it: ROW_NUMBER(), RANK(), LAG(), LEAD() have no concept of "first", "previous", or "next" without an ordering.

• It drives the frame: Running totals and moving averages depend on ORDER BY to know what "up to the current row" means.

• Determinism: Without it, row order is undefined and results can vary between executions; SQL does not guarantee table order.

• When it is optional: Pure aggregates over a whole partition (e.g. AVG(x) OVER (PARTITION BY dept)) don't need ordering because the result is order-independent.

Inside a window function, ORDER BY orders rows within each partition and, for ranking and cumulative functions, defines both the logical order of computation and an implicit frame, so it changes results rather than just presentation.

• Defines row order within a partition: Ranking functions (ROW_NUMBER(), RANK(), DENSE_RANK()) assign numbers based on this order.

• Enables an implicit frame:

  • With ORDER BY present, aggregates default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, turning them into running totals.

  • Without ORDER BY, the frame is the entire partition, giving one aggregate value repeated across all rows.

• Distinct from the query-level ORDER BY: The window's ORDER BY only affects the function; a final ORDER BY is still needed to sort the output.

A Common Table Expression (CTE) is a named, temporary result set defined with a WITH clause that exists only for the duration of a single query. It lets you break complex logic into readable, sequential building blocks.

• Structure:

  • Declared as WITH name AS (SELECT ...) and referenced by name in the main query.

  • Multiple CTEs can be chained, and later ones can reference earlier ones.

• Advantages over subqueries:

  • Named steps read linearly instead of inside-out nesting.

  • A single CTE can be referenced multiple times without duplicating SQL.

  • Supports recursion via WITH RECURSIVE (hierarchies, graphs).

• Advantages over temp tables:

  • No DDL, no cleanup, no separate transaction: scoped to one statement.

  • Often inlined by the optimizer, avoiding the write/read cost of a temp table.

• Trade-off: a temp table can win when the same expensive result is reused across many separate queries, since a CTE is rebuilt each statement.

A self-join is joining a table to itself, using table aliases so the same table plays two roles. It is necessary whenever rows in a table relate to other rows in the same table: hierarchies, sequences, or pairwise comparisons.

• How it works: Give two aliases (e.g. e and m) and join on a column relating one row to another.

• Common analytical uses:

  • Hierarchies: an employees table where manager_id points to another row's employee_id, to list each employee beside their manager's name.

  • Pairwise comparison: find pairs of products in the same category, or customers in the same city.

  • Sequential/gap analysis: compare a row to the previous one (though window functions like LAG() are often cleaner).

• Watch-outs: Add a predicate like a.id < b.id to avoid self-matches and duplicate mirrored pairs.

Use a FULL OUTER JOIN when you need every row from both tables, matching where possible but keeping unmatched rows from either side with NULLs filling the gaps. It's the go-to for reconciliation: finding what exists on one side but not the other.

• Semantics: Returns matched pairs plus unmatched left rows plus unmatched right rows: a union of LEFT and RIGHT join behavior.

• Typical use cases:

  • Reconciliation: compare two datasets (e.g. source vs. target) to find records missing on either side.

  • Merging partial datasets where neither table is a strict superset of the other.

• Finding mismatches: Filter with WHERE a.key IS NULL OR b.key IS NULL to isolate rows that failed to match on one side.

• Caveat: Not supported natively in MySQL: emulate with a LEFT JOIN UNION a RIGHT JOIN.

These are set operators that combine the results of two queries by rows (not columns like joins). UNION returns rows in either set, INTERSECT returns rows in both, and EXCEPT returns rows in the first but not the second. All require both queries to have compatible column counts and types.

• UNION: Combines both result sets and removes duplicates; use UNION ALL to keep duplicates (faster, no dedup sort).

• INTERSECT: Returns only rows appearing in both sets: useful to find common records between two populations.

• EXCEPT (called MINUS in Oracle): Returns rows in the first query not present in the second: order matters, it is directional.

• Reconciliation use: To find what differs between two tables, run A EXCEPT B and B EXCEPT A; empty results on both means the sets are identical.

• Note: INTERSECT and EXCEPT implicitly remove duplicates and compare NULLs as equal, unlike a join's equality.

All three assign a position to rows within an ordered partition, but they differ in how they treat ties. ROW_NUMBER() gives every row a unique number, RANK() gives ties the same rank but leaves gaps, and DENSE_RANK() gives ties the same rank with no gaps.

• ROW_NUMBER():

  • Always distinct: 1, 2, 3, 4 even for tied values.

  • Use for deduplication or pagination where each row needs a unique ordinal.

• RANK():

  • Ties share a rank, next rank skips: 1, 2, 2, 4.

  • Use for competition-style rankings where gaps are meaningful (two silver medals, no bronze).

• DENSE_RANK():

  • Ties share a rank, next rank is contiguous: 1, 2, 2, 3.

  • Use when you want ranking tiers without gaps, e.g. distinct salary bands.

• Impact of choice: With no ties, all three produce identical results; the difference only surfaces on duplicate ordering values.

Use LAG() and LEAD() when a row's value needs to be compared to a previous or following row without a self-join. They reach across rows within an ordered partition: LAG() looks backward, LEAD() looks forward.

• What they do: Return a column value from a row N positions away (default 1), with an optional default for missing neighbors.

• Classic problems they solve:

  • Period-over-period change: this month's revenue minus last month's.

  • Gap detection: time elapsed between consecutive events per user.

  • Trend flags: comparing a value to the prior value to mark increases or decreases.

• Why not a self-join: Offset functions are simpler and faster: no join key gymnastics, and ordering is explicit in the window.

Rank within groups by combining a ranking window function with PARTITION BY: the partition restarts the ranking for each group, and the ORDER BY inside the window defines the metric direction. Choose the ranking function based on how ties should behave.

• Core structure:

  • PARTITION BY group_col resets numbering per group.

  • ORDER BY metric DESC ranks the highest metric as 1 (use ASC for lowest-first).

• Picking the function:

  • ROW_NUMBER() for a strict "top N per group" where exactly N rows are wanted.

  • RANK() or DENSE_RANK() when tied entities should share a position.

• Filtering to top-N: Compute the rank in a CTE or subquery, then filter (window functions can't go in WHERE directly).

A running total is a windowed SUM ordered by a sequencing column with a frame that spans from the start of the partition up to the current row.

• The core expression:

  • SUM(amount) OVER (ORDER BY dt): accumulates from the first row through the current row.

  • Add PARTITION BY account to restart the total per group.

• Frame matters:

  • With an ORDER BY the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  • RANGE lumps together rows with equal ORDER BY values (ties get the same total); use ROWS if you want strict row-by-row accumulation.

• Prefer ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when timestamps can tie to avoid surprising jumps.

DATE_TRUNC snaps a timestamp down to the start of a given unit (e.g. the first instant of its week or month), so all timestamps in the same period collapse to one bucket label you can GROUP BY. Time bucketing is just grouping rows by that truncated value.

• How it works: DATE_TRUNC('month', ts) turns 2024-03-17 into 2024-03-01; 'week' returns the week's starting Monday (in Postgres).

• Bucket then aggregate: Put the truncated value in both SELECT and GROUP BY, then apply COUNT/SUM/AVG.

• Watch the edges:

  • Week start day and timezone matter: truncate in the correct tz or convert first, since UTC vs. local shifts which day/week a row lands in.

  • Fixed-width buckets (e.g. 15 min, 5 day) need date_bin (Postgres) or TIME_BUCKET (TimescaleDB); DATE_TRUNC only does calendar units.

• Empty buckets: DATE_TRUNC only emits periods that have rows; join against a generated calendar/date spine to show zero-count periods.

Pivoting turns distinct row values into columns (long to wide), and unpivoting turns columns back into rows (wide to long). Pivoting is for human-readable reporting; unpivoting normalizes wide data so it's easier to filter, aggregate, and join.

• Pivot (rows to columns):

  • Each unique value of a category becomes its own column, with an aggregate at each cell.

  • Use case: a sales report with months as columns and one row per product, so readers scan a grid instead of scrolling rows.

• Unpivot (columns to rows):

  • Collapses many measure columns into a (key, value) pair, giving tidy/long format.

  • Use case: a spreadsheet with Jan..Dec columns gets unpivoted to (month, amount) rows so you can GROUP BY, chart, or feed a time-series model.

• Rule of thumb: store data long (normalized) for computation, pivot wide only at the presentation layer.

A plain CTE is a one-shot named subquery that runs once; a recursive CTE references itself and iterates, making it the tool for problems of unknown or variable depth.

• Ordinary CTE:

  • A readability/structuring aid: factor a subquery out and reference it, possibly multiple times.

  • Evaluated a single time, no self-reference.

• Recursive CTE:

  • Requires the RECURSIVE keyword (in most engines) and an anchor + self-referencing member joined by UNION ALL.

  • Loops until the recursive member yields no rows.

• Shared trait: both are scoped to the statement they precede and don't persist like a view.

• Practical difference: a normal CTE can't traverse an arbitrarily deep tree; a recursive one can.

A window frame is the precise subset of rows within the ordered partition that a window function evaluates for the current row: it is the boundary that turns "the whole partition" into something like "the last 3 rows" or "everything up to now."

• It scopes aggregate windows: Defined with ROWS or RANGE plus bounds like UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING.

• It determines the result: The same SUM() gives a running total, a moving average, or a group total depending entirely on the frame.

• It has defaults: With ORDER BY the default is cumulative (start to current row); knowing this prevents silent miscalculations.

• Note: Ranking/offset functions ignore the frame; it applies to aggregate-style windows.

Choose a CTE when a query needs to name and reuse an intermediate result or express recursion, and a subquery when the logic is small, used once, and inline. For analytics the deciding factors are readability and reuse, with performance usually a wash on modern optimizers but worth checking.

• Readability:

  • CTEs read top-to-bottom as named steps, ideal for multi-stage analytical pipelines.

  • Deeply nested subqueries force you to read inside-out.

• Reusability:

  • A CTE can be referenced multiple times in the same query; a subquery must be repeated.

  • CTEs also enable recursion (WITH RECURSIVE), which subqueries cannot.

• Performance:

  • Often equivalent: most optimizers inline CTEs like subqueries.

  • Watch for engines that materialize CTEs (older PostgreSQL, or MATERIALIZED hints): a multi-referenced CTE computed once can help, but an optimization fence can block predicate pushdown.

• Rule of thumb: CTE for clarity, staged logic, or recursion; subquery for a one-off inline filter or scalar.

Debug a long CTE chain by isolating and inspecting each stage independently, since a CTE is just a named query you can run on its own. Verify row counts and keys step by step until the wrong output appears.

• Run each CTE in isolation: Replace the final query with SELECT * FROM cte_name to see exactly what one step produces.

• Check row counts at each stage: A sudden jump usually means a join fan-out; a drop means an over-restrictive filter or inner join.

• Validate join keys and grain: Confirm each CTE's granularity (one row per what?) before joining it to the next.

• Read the plan for surprises: Use EXPLAIN to spot unexpected materialization or missing index usage.

• Persist stages when needed: For very long chains, dump intermediate CTEs into temp tables so you can query and compare them repeatedly.

A non-equi join matches rows using operators other than equality (<, >, BETWEEN, !=), whereas an equi-join joins on =. It's essential when the relationship between tables is a range or a nearest-match rather than an exact key match.

• Equi-join vs non-equi: Equi-joins can use hash joins and are usually cheap; non-equi joins can't hash on equality, so they often fall back to nested loops or merge joins and can be costlier.

• Range joins: Map a value into a bucket: join a fact row to a tier table where amount BETWEEN lo AND hi, or attach the dimension row valid for a date.

• As-of joins: Match each row to the most recent prior row in another table (e.g. price at the time of a trade): join on b.ts <= a.ts and pick the max via ordering/window or LATERAL.

• Self non-equi joins: Comparing rows to earlier/later rows, e.g. counting pairs where one value exceeds another.

A semi-join returns rows from the left table that have at least one match on the right (existence check), and an anti-join returns left rows that have no match. Crucially, neither adds columns or duplicates rows from the right: they act purely as filters.

• Semi-join with EXISTS / IN:

  • WHERE EXISTS (SELECT 1 FROM ...) keeps a left row once a match is found, stopping early.

  • IN (subquery) is equivalent for a single column of non-NULL values.

• Anti-join with NOT EXISTS / NOT IN:

  • NOT EXISTS keeps left rows with no match: the standard "find orphans / missing records" pattern.

  • Trap: NOT IN returns no rows if the subquery yields any NULL, so prefer NOT EXISTS.

• Why not a plain join: A regular JOIN to test existence can fan out duplicates; semi/anti semantics avoid that and are often optimized into dedicated join operators.

A CROSS JOIN produces the Cartesian product: every row of one table paired with every row of another, with no join condition. It's useful whenever you deliberately need all combinations, most often to build a complete scaffold to left-join real data onto.

• Date spine / gap filling: Cross join a generated series of dates with the set of entities so every entity-date combination exists, then LEFT JOIN actuals to expose zero/missing periods.

• Generating combinations: All product × region pairs, all sizes × colors, or building test/matrix grids.

• Row multiplication: Cross join a tiny numbers/tally table to expand or unnest rows.

• Caution: Output is N × M rows: on large inputs this explodes, so cross join deliberately small sets. An accidental missing join condition silently becomes a cross join.

A non-correlated subquery is independent: it can be run once on its own and its result reused. A correlated subquery references columns from the outer query, so conceptually it re-evaluates for each outer row. The distinction drives both semantics and performance.

• Non-correlated: Executes once; good for a constant, a lookup set, or a precomputed aggregate: e.g. WHERE price > (SELECT AVG(price) FROM products).

• Correlated: Depends on the outer row (WHERE o.cust_id = c.id); naturally expresses per-row existence or per-row aggregates.

• Performance:

  • Correlated logic can imply row-by-row execution, but modern optimizers frequently rewrite it into a semi-join or hash join, so it's often fine.

  • When it isn't rewritten, a heavy correlated subquery over many outer rows can be slow; a join or window function may plan better.

• When to prefer which: Use non-correlated for shared constants/sets. Use correlated (or EXISTS) for existence tests and per-row logic, but consider window functions/joins when computing the same metric across a large result set.

An anti-join returns rows from the left table that have no matching row in the right table. It's the "find what's missing" operation, and it acts as a filter: no right-side columns are added and no rows are duplicated.

• Typical uses:

  • Orphan/missing detection: customers with no orders, products never sold, records failing to load into a target.

  • Reconciliation: rows in source not present in destination.

• How to write it: NOT EXISTS (preferred, NULL-safe), NOT IN (beware NULLs), or LEFT JOIN ... WHERE right_key IS NULL.

• Note: Optimizers usually recognize all three patterns as the same anti-join operator.

A semi-join returns rows from the left table that have at least one match on the right, but returns each qualifying left row only once and adds no columns from the right table. The key difference from an INNER JOIN is that an inner join can multiply rows and exposes right-side columns.

• LEFT SEMI JOIN (existence filter):

  • Emits a left row once as soon as one match exists, regardless of how many matches there are.

  • Only left columns are available in the output.

• INNER JOIN (combine + fan-out):

  • If a left row matches 5 right rows, you get 5 output rows.

  • Right columns are included, so it's for combining data, not just filtering.

• Practical note: Standard SQL has no SEMI JOIN keyword (it's explicit in Spark/Hive); in most databases you express it with EXISTS or IN, which the optimizer plans as a semi-join.

No, window functions cannot be used directly in a WHERE clause. This is because of logical query processing order: the WHERE clause is evaluated before window functions are computed, so the result doesn't exist yet at filtering time. The fix is to compute the window function in a subquery or CTE, then filter in an outer query.

• Why it fails:

  • Processing order is roughly FROM → WHERE → GROUP BY → HAVING → window functions (SELECT) → ORDER BY.

  • Windows run in the SELECT phase, after WHERE has already filtered.

• The alternative: Wrap it in a CTE or subquery to materialize the window result, then filter on that column in the outer WHERE.

ROW_NUMBER() deduplicates by numbering rows within each group of "duplicate" keys, then keeping only the first. You partition by the columns that define a duplicate, order to decide which copy to keep, and filter to row number 1.

• The pattern:

  1. PARTITION BY the columns that identify a duplicate (the "same" record).

  2. ORDER BY a column that ranks copies (e.g. keep the most recent).

  3. Keep rows where rn = 1 and discard the rest.

• Why ROW_NUMBER() over RANK(): ROW_NUMBER() guarantees exactly one survivor per group; RANK() would keep multiple rows on ties.

• Deleting duplicates:

  • Wrap it in a CTE and issue DELETE FROM cte WHERE rn > 1 (supported in engines like PostgreSQL and SQL Server).

  • Add a tie-breaker to ORDER BY so the survivor is deterministic.

Both are window functions that return a value from a position in the window frame: FIRST_VALUE() gives the first row's value in the frame and LAST_VALUE() the last. The surprise with LAST_VALUE() comes from the default frame, which ends at the current row, so it rarely reaches the true last row of the partition.

• Default frame is the culprit:

  • When you use ORDER BY without an explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  • So LAST_VALUE() returns the value at the current row, not the partition's final row.

• FIRST_VALUE() looks correct by accident: The frame always starts at UNBOUNDED PRECEDING, so the first value is stable regardless of the frame end.

• The fix: an explicit frame: Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING so the frame spans the whole partition.

All three describe a row's relative position, but PERCENT_RANK() and CUME_DIST() return continuous fractions of the distribution while NTILE() assigns discrete bucket numbers.

• PERCENT_RANK(): relative rank as a fraction:

  • Computed as (rank - 1) / (total_rows - 1), so it ranges from 0 to 1.

  • The first row is always 0; the last is always 1.

• CUME_DIST(): cumulative distribution:

  • Fraction of rows with a value <= the current row: count(rows <= current) / total_rows.

  • Ranges from just above 0 to 1; the last row is always 1.

• NTILE(n): discrete buckets:

  • Splits ordered rows into n groups as evenly as possible, labeling them 1..n (quartiles, deciles).

  • When rows don't divide evenly, earlier buckets get one extra row.

• Key contrast: The first two are continuous percentile-style measures; NTILE() is a coarse bucketing that ignores exact gaps between values.

The WINDOW clause lets you define a named window specification once and reuse it across multiple window functions, so you write the PARTITION BY / ORDER BY / frame only once.

• Defined after the HAVING clause: Syntax: WINDOW w AS (PARTITION BY ... ORDER BY ...), then reference it with OVER w.

• Why it's useful:

  • Removes repetition when several functions share the same window (less to read, less to mistype).

  • Single source of truth: change the partition/order once and every function updates.

• Can be extended: A named window can be referenced and refined, e.g. OVER (w ORDER BY x), to add ordering to a shared partition.

All three are extensions of GROUP BY that produce multiple grouping levels in one query. GROUPING SETS lists exactly the groupings you want; ROLLUP generates a hierarchy of subtotals; CUBE generates every possible combination.

• GROUPING SETS: explicit list:

  • You specify each grouping directly, e.g. GROUPING SETS ((a,b),(a),()).

  • Most flexible: produces only the groupings you name.

• ROLLUP: hierarchical subtotals:

  • ROLLUP(a,b) produces (a,b), (a), and () (the grand total).

  • Ideal for ordered hierarchies like year > month > day.

• CUBE: all combinations:

  • CUBE(a,b) produces (a,b), (a), (b), and (): 2^n groupings for n columns.

  • Use for multi-dimensional cross-tab analysis where any combination matters.

• When to choose which: Hierarchy of subtotals: ROLLUP. Every dimension combination: CUBE. A specific hand-picked set: GROUPING SETS.

Grouping extensions let the engine scan the source data once and compute all grouping levels in a single pass, whereas the UNION ALL approach re-scans and re-aggregates the table for each grouping.

• Single scan vs. many:

  • ROLLUP/CUBE/GROUPING SETS typically read the table (or index) once and derive higher-level totals from lower-level aggregates.

  • With n separate GROUP BY queries unioned, the table is read and sorted/hashed n times.

• Shared work: The optimizer can reuse a single sort or hash aggregation to roll subtotals up from the finest grouping.

• Clarity and correctness: One concise statement instead of many near-duplicate blocks, so less chance of the branches drifting out of sync.

• Caveat: The advantage is largest when the groupings share the same base rows and aggregate functions; unrelated aggregations may not benefit.

Conditional aggregation places a CASE (or filter) inside an aggregate so each aggregate only counts rows meeting a condition. This turns row values into columns, which is the mechanism behind pivoting and cohort metrics.

• The core pattern:

  • SUM(CASE WHEN cond THEN 1 ELSE 0 END) counts matching rows; SUM(CASE WHEN cond THEN amount END) totals only matching amounts.

  • Because non-matching rows yield NULL or 0, they don't pollute the aggregate.

• Pivoting: Turn category values into columns: one conditional aggregate per category, grouped by the row dimension.

• Cohort analysis: Group by signup cohort, then use conditional counts per activity month to measure retention.

• Portability note: The SQL-standard equivalent is COUNT(*) FILTER (WHERE cond), supported by Postgres.

Use aggregate window functions with different OVER clauses in the same SELECT: one SUM() over the whole table for the company total and another partitioned by team for the team total, then divide each salesperson's sales by them.

• Company total: empty OVER: SUM(sales) OVER () aggregates across every row without collapsing them.

• Team total: partitioned OVER: SUM(sales) OVER (PARTITION BY team) gives each row its team's total.

• Why window functions, not GROUP BY: They keep every salesperson row intact while attaching the totals, so you can compute both percentages side by side.

• Watch the division: Cast to a decimal or multiply by 100.0 to avoid integer truncation.

The FILTER clause restricts which rows an aggregate sees: agg(x) FILTER (WHERE condition) aggregates only rows matching the predicate. It is the SQL-standard, more readable equivalent of putting a CASE inside the aggregate.

• How it works: Each aggregate can carry its own FILTER, so you compute several conditional metrics in one pass over the data.

• Compared to CASE:

  • COUNT(*) FILTER (WHERE paid) equals COUNT(CASE WHEN paid THEN 1 END): both work because aggregates ignore NULL.

  • FILTER reads more clearly and states intent, especially with many conditional columns.

• When to prefer FILTER:

  • On engines that support it (PostgreSQL, SQLite): cleaner conditional aggregation and pivot-style summaries.

  • Fall back to CASE for portability (MySQL, SQL Server lack FILTER).

• Caveat: for SUM, CASE ... ELSE 0 and FILTER differ only when all rows are excluded (0 vs NULL).

A share-of-total (ratio-to-report) metric divides each row's value by a total computed over a window, so you divide a per-row number by an aggregate window function that returns the group total on every row.

• The pattern: value / SUM(value) OVER (...): the windowed SUM repeats the total on each row without collapsing rows.

• Choosing the window:

  • OVER () (empty): share of the grand total.

  • OVER (PARTITION BY region): share within each region.

• Watch-outs:

  • Guard against divide-by-zero with NULLIF(SUM(value) OVER (...), 0).

  • Cast to a decimal/float to avoid integer division truncating to 0.

Period-over-period growth compares a period's value to a prior period's value, which you fetch with LAG() over data ordered by time, then compute the percentage change.

• Aggregate first: Roll raw rows up to one row per period (e.g. per month) so LAG compares complete periods, not individual events.

• Pull the prior value:

  • MoM: LAG(total) OVER (ORDER BY month).

  • YoY: LAG(total, 12) OVER (ORDER BY month), or PARTITION BY month_number ORDER BY year to line up the same calendar month.

• Compute growth: (cur - prev) / prev, guarding with NULLIF(prev, 0).

• Gaps warning: LAG reads the previous present row, not the previous calendar period. Join to a date spine so missing months don't distort the offset.

A date spine (calendar table) is a table with one row per date over a continuous range. You generate it and LEFT JOIN your data onto it so every period is represented, even those with no events.

• How to generate it:

  • PostgreSQL: generate_series('2024-01-01'::date, '2024-12-31', '1 day').

  • Others: a recursive CTE, or a permanent dimension table with day/week/month/holiday attributes.

• Why it's crucial:

  • Raw data has no row for a day/month with zero activity, so GROUP BY silently skips those periods.

  • LEFT JOIN + COALESCE(value, 0) fills the gaps with explicit zeros.

• Downstream benefits:

  • Window functions like LAG and moving averages become correct because periods are truly contiguous.

  • Charts show flat/zero lines instead of connecting across missing points.

A moving average is AVG over a sliding window frame defined with ROWS (or RANGE) relative to the current row, so each row averages itself and its neighbors.

• Trailing average (typical): ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: a 7-row trailing average.

• Centered average: ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING: smooths around each point.

• ROWS vs RANGE: ROWS counts physical rows; RANGE spans by the ordering value (e.g. a true 7-day window regardless of missing days).

• Edge effect: early rows average fewer values. Filter them out, or join a date spine first so gaps don't distort ROWS-based counts.

Normalize the signed amount first (deposits positive, withdrawals negative) with a CASE expression, then take a running SUM of that signed value to get the balance.

• Step 1: derive a signed amount: CASE WHEN type = 'withdrawal' THEN -amount ELSE amount END.

• Step 2: accumulate it: SUM(signed) OVER (PARTITION BY account ORDER BY dt ROWS UNBOUNDED PRECEDING).

• Ordering: Order by a stable key (timestamp plus a tie-breaker like transaction id) so same-instant transactions accumulate deterministically.

• The signed-amount trick generalizes: any conditional running metric becomes a plain windowed SUM once you fold the condition into the summed expression.

Both produce the same wide result. Conditional aggregation is the portable approach: GROUP BY the row key and wrap each target category in a CASE inside an aggregate. A dedicated PIVOT operator (SQL Server, Oracle, Snowflake, recent DuckDB) is cleaner syntax but vendor-specific. Neither can discover columns at runtime, so dynamic pivoting is the main limitation.

• Conditional aggregation: SUM(CASE WHEN month='Jan' THEN amount END) per column; works everywhere and lets you mix different aggregates/conditions per column.

• PIVOT operator: Declarative: name the aggregate and the value list once; less boilerplate but syntax and support vary by engine.

• Shared limitation: columns must be known in advance:

  • SQL output columns are fixed at parse time, so you must list each pivot value literally.

  • Dynamic pivoting (unknown/changing categories) requires querying the distinct values first, building the SQL string in code, and executing it as dynamic SQL.

On pivot, NULL typically means "no rows matched that cell," so you decide whether that should read as 0 or stay unknown; on unpivot, you decide whether to keep or drop the NULL-valued cells. The right choice depends on whether absence means zero or truly missing.

• Pivot: fill empty cells:

  • Wrap with COALESCE(SUM(CASE...), 0) when a missing category legitimately means zero (e.g. no sales that month).

  • Leave NULL when the metric is genuinely unknown, so you don't fabricate zeros that skew averages.

• Unpivot: keep vs. exclude:

  • UNPIVOT excludes NULLs by default; INCLUDE NULLS (or a manual UNION ALL) keeps them.

  • Drop them if a missing measure is noise; keep them if the absence itself is meaningful (e.g. gaps in a time series).

• Distinguish NULL from zero deliberately: aggregates like AVG ignore NULL but are dragged down by injected 0s, so the fill choice changes results.

Exploding (unnesting) turns one row containing an array into multiple rows, one per element, so nested/repeated data becomes relational and queryable. You need it whenever a column holds a list (tags, line items, JSON array) and you want to filter, join, or aggregate on individual elements.

• How it's expressed:

  • Postgres: UNNEST(array) or jsonb_array_elements() in a lateral join.

  • BigQuery: CROSS JOIN UNNEST(arr); Snowflake: LATERAL FLATTEN; Spark/Hive: LATERAL VIEW explode().

• It's a row-multiplying join: The parent row repeats once per element; other columns are carried along, so you can then GROUP BY element values.

• Preserve context: Capture element position with WITH ORDINALITY (or the offset arg) if order matters.

• Empty/NULL arrays: An inner unnest drops rows with empty arrays; use a LEFT JOIN LATERAL to keep the parent row with a NULL element.

• When needed: normalizing JSON API payloads, expanding multi-value tags, or flattening order-line arrays before aggregation.

Use a recursive CTE whenever the answer depends on repeatedly applying the same step to its own output: walking parent/child chains, traversing a graph, or generating a sequence where each row derives from the previous one.

• Hierarchical data:

  • Org charts, category trees, bill-of-materials: start at a root and expand children level by level.

  • Naturally computes depth/level and full paths as you descend.

• Graph traversal:

  • Reachability, shortest-hop paths, dependency resolution across edges of unknown depth.

  • Must guard against cycles (track a visited path or cap depth).

• Generating series: Produce a range of numbers or dates (e.g. a calendar) to fill gaps or drive joins.

• Rule of thumb: reach for it when depth is variable/unknown; if depth is fixed, a plain join chain is simpler.

A recursive CTE is a named query that references itself, built from two parts joined by UNION ALL: an anchor that produces the starting rows and a recursive member that repeatedly feeds off the CTE's previous output until it returns nothing.

• Anchor query: The non-recursive seed: runs once to produce the initial result set (e.g. the root nodes).

• Recursive member:

  • References the CTE by name and joins it to base tables to derive the next set of rows.

  • Executes repeatedly: each iteration only sees the rows produced by the previous iteration.

• Termination condition:

  • There is no explicit STOP; recursion ends when an iteration produces zero new rows.

  • A WHERE filter (e.g. depth < N, or matching remaining children) is what eventually empties the output.

• Declared with WITH RECURSIVE (some engines like SQL Server just use WITH).

To get the first or last event per entity, rank each entity's rows by the ordering column and keep rank 1, or use a correlated/aggregate approach. Window functions are the clearest and most flexible tool.

• ROW_NUMBER() (most common):

  • PARTITION BY the entity, ORDER BY the timestamp (ascending for first, descending for last), keep rn = 1.

  • Breaks ties arbitrarily unless you add tiebreaker columns to the ordering.

• DISTINCT ON (PostgreSQL): SELECT DISTINCT ON (entity) ... ORDER BY entity, ts is a concise shortcut for one row per group.

• FIRST_VALUE() / LAST_VALUE():

  • Useful to attach the first/last event's value to every row without collapsing them.

  • Watch the frame: LAST_VALUE() needs an explicit frame like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

• Aggregate alternative: GROUP BY entity with MIN(ts)/MAX(ts) gets the boundary timestamp but not the other columns of that row without a re-join.

Top-N per group is a ranking problem: partition by the group, order by the metric, assign a rank with a window function, then filter to ranks ≤ N. The choice of ranking function decides how ties are handled.

• Pick the ranking function:

  • ROW_NUMBER(): exactly N rows, arbitrary tie-break.

  • RANK(): ties share a rank and leave gaps, so ties at the boundary can return more than N.

  • DENSE_RANK(): ties share a rank with no gaps, good for "top 3 distinct revenue levels".

• Structure:

  • Compute the rank in a subquery/CTE (PARTITION BY category ORDER BY revenue DESC), then WHERE rnk <= 3 in the outer query.

  • You can't filter on a window function in the same WHERE, hence the wrapping.

• Alternatives:

  • A correlated subquery counting how many rows outrank each row works but scales poorly.

  • LATERAL / CROSS APPLY with LIMIT N per group is efficient when the group list is small and indexed.

Bucketing means mapping continuous values into discrete ranges (buckets) and counting rows per bucket: you compute a bucket key per row, then GROUP BY it. SQL offers width-based, quantile-based, and function-based approaches.

• Fixed-width buckets (equal ranges):

  • Compute the bucket with arithmetic: FLOOR(value / width) * width gives the bucket's lower bound.

  • Use width_bucket(value, min, max, n) (Postgres/Oracle) to assign values into n equal-width buckets, handling out-of-range values.

• Equal-frequency buckets (quantiles): Use NTILE(n) OVER (ORDER BY value) to split rows into n groups with roughly equal counts (deciles, quartiles).

• Custom / irregular buckets: A CASE expression labels arbitrary ranges (0-18, 19-35, ...) when boundaries aren't uniform.

• Watch for empty buckets: Grouping only produces buckets that have data; LEFT JOIN against a generated series of bucket keys to show zero-count buckets.

Sampling returns a subset of rows so you can explore or estimate quickly without scanning everything. TABLESAMPLE is the built-in clause for this; it trades statistical precision and exact reproducibility for speed.

• TABLESAMPLE methods:

  • BERNOULLI: each row is included independently with the given probability, giving a true random row sample but scanning the whole table.

  • SYSTEM: samples at the page/block level, so it's much faster but clusters rows physically stored together (biased if data is ordered on disk).

  • Add REPEATABLE(seed) for a reproducible sample.

• Other approaches:

  • ORDER BY RANDOM() LIMIT n: exact random n rows but sorts the whole set (expensive on big tables).

  • Deterministic hash filter: WHERE MOD(HASH(id), 100) = 0 gives a stable ~1% sample that's consistent across runs and joins.

• When and why: Fast ad-hoc exploration, approximate aggregates, or building dev datasets.

• Trade-offs:

  • Sampling error: small samples give noisy estimates; scale counts by the sampling fraction.

  • SYSTEM speed comes with block-level bias; BERNOULLI is fairer but slower.

QUALIFY filters rows based on the result of a window function, the way HAVING filters aggregates. It lets you reference a window function in a filter without a wrapping subquery or CTE.

• Why it's needed:

  • Window functions are computed after WHERE, so you normally can't filter on ROW_NUMBER() in the same query; you'd nest it and add WHERE rn = 1.

  • QUALIFY evaluates after window functions, letting you filter inline.

• Typical uses:

  • Top-N or latest-per-group: QUALIFY ROW_NUMBER() OVER (...) = 1.

  • Deduplication, or keeping rows above a running/ranked threshold.

• Caveat: It's not standard SQL: supported by Snowflake, BigQuery, Teradata, DuckDB, but not (yet) Postgres/MySQL, where you fall back to a subquery.

Most aggregates silently ignore NULL inputs, while window functions follow the same NULL rules but can also produce NULLs at frame edges: the trap is confusing "ignored" with "treated as zero."

• Aggregates skip NULLs:

  • SUM, AVG, MAX ignore NULL rows entirely; AVG divides by the count of non-NULL values, not all rows.

  • COUNT(*) counts all rows, but COUNT(col) counts only non-NULL values: they diverge whenever NULLs exist.

• NULL is not zero: AVG(col) over {10, NULL} is 10, but treating NULL as 0 would give 5: decide which semantics you want and use COALESCE if zeros are intended.

• Window functions inherit NULL handling but add edge cases:

  • LAG/LEAD return NULL past the partition boundary; provide a default argument if needed.

  • ORDER BY sorts NULLs via NULLS FIRST/NULLS LAST, which changes running totals and rankings.

  • Some engines support IGNORE NULLS on LAG/FIRST_VALUE to skip gaps.

• Precautions:

  • Be explicit: COALESCE NULLs to a business default only when it is semantically correct.

  • Watch COUNT(col) vs COUNT(*) when computing ratios; a NULL-heavy column can inflate averages.

NTILE(n) is a window function that splits ordered rows into n roughly equal-sized buckets and labels each row with its bucket number (1 to n), which is ideal for quartiles, deciles, and percentile bands.

• How it works:

  • Requires ORDER BY to define ranking; optional PARTITION BY buckets within each group.

  • NTILE(4) gives quartiles, NTILE(10) deciles, NTILE(100) percentiles.

  • If rows don't divide evenly, earlier buckets get one extra row each.

• When it's useful: Customer segmentation (top 25% spenders), performance banding, and dividing data into equal cohorts.

• Key caveat:

  • NTILE balances bucket sizes, not value boundaries: identical (tied) values can land in different buckets, unlike PERCENTILE_CONT which returns actual value thresholds.

  • If you need a value cutoff (e.g. the 90th-percentile amount), use a percentile function, not NTILE.

The cleanest way is the ordered-set aggregate PERCENTILE_CONT(q) (or PERCENTILE_DISC) WITHIN GROUP (ORDER BY col); NTILE only approximates quantiles by bucketing rows and should be a fallback.

• Preferred: PERCENTILE functions:

  • Median is PERCENTILE_CONT(0.5); any quantile is just a different fraction (0.9, 0.99).

  • Exact, handles ties and interpolation correctly, and reads clearly.

• NTILE as a fallback: NTILE(100) then filter to a bucket gives an approximate percentile, but bucket edges are size-balanced, not value-based, so cutoffs are imprecise, especially with ties or small datasets.

• Portability workaround: On engines lacking percentile functions, order rows with ROW_NUMBER and COUNT(*), then pick the middle row(s) manually.

• Rule of thumb: Need an accurate quantile value: use PERCENTILE_CONT. Need to assign each row to a cohort: use NTILE.

These aggregates collapse many row values into one ordered collection: STRING_AGG (Postgres/SQL Server) and LISTAGG (Oracle) build a delimited string, while ARRAY_AGG builds an array; the inner ORDER BY makes the result deterministic.

• What each does:

  • STRING_AGG(col, ', ') concatenates values with a separator into one string.

  • ARRAY_AGG(col) collects values into an array, preserving structure and duplicates.

  • LISTAGG is Oracle's equivalent to STRING_AGG.

• Why the inner ORDER BY matters:

  • Without it, row order within a group is undefined, so the concatenated output is non-deterministic and can change between runs.

  • The syntax is STRING_AGG(col, ',' ORDER BY col): ordering lives inside the aggregate, not in the query's outer ORDER BY.

  • Deterministic output matters for reproducible reports, diffs, and downstream parsing.

• Practical notes: Use DISTINCT inside to dedupe, and watch length limits (LISTAGG can overflow in Oracle).

The frame clause defines which subset of the partition's rows the window function actually operates on relative to the current row (e.g. all rows from the start up to the current one). ROWS counts physical rows, while RANGE works on value ranges of the ORDER BY key, treating tied rows as a single logical unit.

• Syntax: e.g. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW or RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

• ROWS: physical offset: Counts an exact number of rows, so ties are handled independently. Use it for true row-based windows like a 3-row moving average.

• RANGE: logical value range: Includes every row whose ORDER BY value falls in the range. Use it when peers (equal values) should be grouped, or with value offsets like RANGE BETWEEN 7 PRECEDING over dates.

• The duplicate-peers trap: With RANGE ... CURRENT ROW, all rows tied on the ORDER BY value are included at once, so a running total jumps to the same value for every tied row instead of incrementing one by one. Switch to ROWS if you want a strict row-by-row cumulative sum.

The default frame depends on whether an ORDER BY is present: without it the frame is the entire partition, but with ORDER BY the default becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is a cumulative window, not the whole partition.

• Unordered (no ORDER BY): Frame = entire partition, so SUM(x) OVER (PARTITION BY dept) gives the full group total on every row.

• Ordered (ORDER BY present): Default frame is start-of-partition to current row, so the same SUM becomes a running total instead of a group total.

• Why it matters:

  • Simply adding ORDER BY can silently change an aggregate from a total to a cumulative sum, a common bug.

  • The default uses RANGE, so tied peers are lumped together: be explicit with ROWS when you need precise row boundaries.

The default RANGE frame is value-based, not row-based: all rows with the same ORDER BY value (peers) are treated as one group, so a running total includes every tied row at once instead of accumulating them one by one.

• RANGE vs ROWS:

  • RANGE ... CURRENT ROW means "up to and including all rows whose sort value equals the current row's value."

  • ROWS ... CURRENT ROW means "up to this physical row only."

• The gotcha:

  • If two rows share the same ordering value, a running SUM() jumps by the combined total on both rows: both show the same cumulative value.

  • You expected each duplicate to add incrementally, but RANGE collapses the peers.

• The fix: Use an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or make the ORDER BY key unique.

It depends on the engine: historically some databases materialized CTEs (computed once into a temp buffer), but most modern optimizers inline them like subqueries. Whether materialization helps or hurts depends on how many times the CTE is referenced.

• Engine behavior varies:

  • PostgreSQL before 12 always materialized (an optimization fence); 12+ inlines by default unless referenced multiple times or marked MATERIALIZED.

  • SQL Server and Oracle generally inline; you can hint otherwise.

• When materialization helps: An expensive CTE referenced several times is computed once, not per reference.

• When it hurts: A materialized CTE is an optimization fence: filters and joins from the outer query can't push down into it, so it may scan far more rows than needed.

• Practical advice: read the execution plan; use MATERIALIZED / NOT MATERIALIZED hints when the optimizer's choice is wrong.