SQL for data science

WHERE filters individual rows before grouping and aggregation occur, so it cannot reference aggregate functions. HAVING filters groups after GROUP BY and aggregation, so it can use aggregates like COUNT or SUM. For best performance, push row-level predicates into WHERE so fewer rows reach the aggregation step.

The logical order is FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT. Because SELECT is evaluated after GROUP BY but before ORDER BY, column aliases defined in SELECT are usable in ORDER BY but not in WHERE (some engines like MySQL and PostgreSQL also permit aliases in GROUP BY/HAVING as a non-standard extension).

ROW_NUMBER assigns a unique sequential integer with no ties. RANK assigns the same rank to ties but leaves gaps afterward (1,1,3). DENSE_RANK assigns the same rank to ties with no gaps (1,1,2). Choose ROW_NUMBER for deterministic deduplication and DENSE_RANK when you want consecutive rank values.

SELECT id, val,
 ROW_NUMBER() OVER (ORDER BY val) rn,
 RANK() OVER (ORDER BY val) rk,
 DENSE_RANK() OVER (ORDER BY val) drk
FROM t;

COUNT(*) counts all rows including those with NULLs, while COUNT(column) counts only rows where that column is non-NULL. Therefore if the column contains NULLs, COUNT(column) will be smaller. COUNT(DISTINCT column) further removes duplicates among non-NULL values.

Use ROW_NUMBER() partitioned by user and ordered by event time descending, then filter rn = 1 in an outer query or CTE. This is generally faster and clearer than correlated subqueries or self-joins on MAX(time), and it handles ties deterministically when you add tiebreaker columns to the ORDER BY.

WITH r AS (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) rn
 FROM events)
SELECT * FROM r WHERE rn = 1;

It computes a running (cumulative) total from the first row of the partition up to and including the current row, in the window's ORDER BY sequence. Without an explicit frame, an ordered aggregate defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which can behave differently with tied ORDER BY values by summing all peers together.

SUM(amt) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

ROWS counts physical rows relative to the current row, so a frame of N preceding means exactly N rows. RANGE works on logical value ranges of the ORDER BY expression, grouping all rows with equal ordering values (peers) into the same frame boundary. With duplicate ORDER BY values the two can produce very different aggregates.

INNER JOIN keeps only rows with matches in both tables. LEFT JOIN keeps all left rows plus matched right rows (NULLs where no match). FULL OUTER JOIN keeps all rows from both sides, filling NULLs where either side lacks a match. The choice directly affects row counts and downstream null handling in analyses.

If you put a predicate on a right-table column in WHERE (e.g., r.status = 'active'), rows where the right side is NULL get filtered out, eliminating the unmatched left rows and effectively making it an inner join. To preserve the outer semantics, move the right-table condition into the ON clause instead of WHERE.

-- Preserve outer rows:
SELECT l.*, r.*
FROM l LEFT JOIN r ON l.id = r.id AND r.status = 'active';

A correlated subquery references columns from the outer query, so it must conceptually be re-evaluated for each outer row, which can lead to O(n*m) execution if not optimized. Modern optimizers often rewrite them as joins or semi-joins, but where they cannot, rewriting manually as a JOIN or window function usually improves performance.

EXISTS is preferred for existence checks, especially with correlated subqueries and large result sets, and it short-circuits on the first match. Critically, NOT IN behaves incorrectly when the subquery returns any NULL (it can yield no rows because the comparison evaluates to UNKNOWN), whereas NOT EXISTS handles NULLs safely. EXISTS also avoids materializing the full subquery list.

Gaps-and-islands involves identifying consecutive runs (islands) or breaks (gaps) in ordered data such as login dates. A classic technique subtracts a ROW_NUMBER() from the sequence value (e.g., date minus row number); rows in the same consecutive run share a constant difference, which you then GROUP BY to collapse into islands.

SELECT user_id, MIN(d) AS start_d, MAX(d) AS end_d
FROM (
 SELECT user_id, d,
 d - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d))::int AS grp
 FROM logins
) t
GROUP BY user_id, grp;

Use the ordered-set aggregate PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col), which interpolates between values for a continuous median. PERCENTILE_DISC(0.5) instead returns an actual data value (the discrete median). For engines lacking these, you can emulate it with NTILE or ROW_NUMBER over the ordered values.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amt
FROM orders;

PERCENTILE_CONT performs linear interpolation between adjacent ordered values and may return a value not present in the data, suitable for continuous distributions. PERCENTILE_DISC returns the first actual data value whose cumulative distribution is greater than or equal to the requested percentile, always returning a real observed value.

LAG(metric, 1) OVER (PARTITION BY entity ORDER BY period) returns the prior row's value within each partition, so you can compute deltas or growth rates by subtracting or dividing against the current row. LEAD() does the symmetric forward lookup. These avoid expensive self-joins for sequential comparisons.

SELECT period, revenue,
 revenue - LAG(revenue) OVER (ORDER BY period) AS mom_change
FROM monthly;

A recursive CTE iterates over hierarchical or graph-like data such as org charts or bill-of-materials. It requires an anchor member (the base/seed query), the UNION ALL operator, and a recursive member that references the CTE itself, with a termination condition reached when the recursive member returns no rows.

WITH RECURSIVE org AS (
 SELECT id, manager_id, 1 lvl FROM emp WHERE manager_id IS NULL
 UNION ALL
 SELECT e.id, e.manager_id, o.lvl+1
 FROM emp e JOIN org o ON e.manager_id = o.id)
SELECT * FROM org;

NULL = NULL evaluates to NULL (unknown), not TRUE, because NULL represents an unknown value under three-valued logic. Consequently equality joins and WHERE filters never match NULL to NULL; you must use IS NULL, IS NOT DISTINCT FROM, or COALESCE to handle them explicitly.

UNION removes duplicate rows by performing an implicit distinct/sort, which is expensive; UNION ALL simply concatenates all rows including duplicates and is much faster. For large data pipelines where you know inputs are disjoint or duplicates are acceptable, prefer UNION ALL to avoid the deduplication cost.

Use aggregate functions wrapped around CASE expressions, one per target column, grouped by the entity key: e.g., SUM(CASE WHEN month='Jan' THEN amt END). This conditional-aggregation pivot is portable across engines, unlike vendor-specific PIVOT syntax, and the aggregate collapses the rows per group.

SELECT product,
 SUM(CASE WHEN region='US' THEN sales END) AS us,
 SUM(CASE WHEN region='EU' THEN sales END) AS eu
FROM t GROUP BY product;

A clustered index defines the physical storage order of the table's rows (only one per table) so range scans on the key are efficient. A non-clustered index is a separate structure with pointers (or the clustered key) back to the rows, allowing multiple per table but adding a lookup step unless the index covers the query.

A covering index includes all columns a query needs (in the key and/or INCLUDE columns) so the engine can satisfy the query entirely from the index without touching the base table. This eliminates costly key lookups / heap fetches, dramatically reducing I/O for frequently run selective queries.

A full/sequential scan means the engine reads every row rather than using an index, which is fine for queries returning a large fraction of the table but a red flag for highly selective filters. Common fixes include adding a suitable index, making the predicate sargable, or updating stale statistics so the optimizer estimates cardinality correctly.

A predicate is non-sargable when it wraps the indexed column in a function or computation (e.g., WHERE YEAR(ts)=2025 or WHERE col+0=5), preventing the optimizer from using the index's sorted structure for a seek. Rewriting it as a range on the raw column (ts >= '2025-01-01' AND ts < '2026-01-01') restores index seekability.

-- Non-sargable:  WHERE YEAR(ts) = 2025
-- Sargable:
WHERE ts >= '2025-01-01' AND ts < '2026-01-01';

Assign ROW_NUMBER() OVER (PARTITION BY the_key ORDER BY a deterministic tiebreaker) and keep rows where rn = 1, deleting or filtering the rest. Using a deterministic ORDER BY (e.g., updated_at DESC, id) ensures reproducible results, unlike GROUP BY which cannot return the full non-aggregated row reliably.

DELETE FROM t WHERE id IN (
 SELECT id FROM (
 SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) rn
 FROM t) x WHERE rn > 1);

A CTE is a query-scoped named subquery that the optimizer may inline (not persisted). A temporary table physically stores intermediate rows for the session and can be indexed and reused across statements. A materialized view persists precomputed query results on disk and must be refreshed to reflect source changes, trading freshness for read speed.

In engines that inline CTEs a multiply-referenced CTE may be recomputed at each reference, while engines that always materialize them (e.g., PostgreSQL before version 12, where a CTE acted as an optimization fence) materialize without indexes and block predicate pushdown. A temp table lets you persist once, add indexes, and gather statistics, which can be faster for repeated or large intermediate sets.

Within each stratum, generate a random ordering using ROW_NUMBER() OVER (PARTITION BY stratum ORDER BY RANDOM()) and keep the first k rows per group, or filter by a random threshold. This guarantees a fixed sample size per group, unlike TABLESAMPLE which samples the whole table proportionally and ignores strata.

SELECT * FROM (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY segment ORDER BY RANDOM()) rn
 FROM users) t
WHERE rn <= 100;

Define each user's cohort by their first activity period (e.g., MIN(signup_month)), then join or self-join activity records to compute the period offset (activity_month - cohort_month). Aggregate distinct users per cohort and offset, and divide by the cohort's size to get retention rates, typically rendered as a triangular matrix.

WITH c AS (
 SELECT user_id, DATE_TRUNC('month', MIN(ts)) AS cohort
 FROM events GROUP BY user_id)
SELECT c.cohort,
 DATE_TRUNC('month', e.ts) AS m,
 COUNT(DISTINCT e.user_id) AS active
FROM events e JOIN c USING (user_id)
GROUP BY 1,2;

Order events per user and use LAG() to get the previous event time; flag a new session when the gap exceeds 30 minutes, then take a running SUM of those flags to produce a session id. This windowed approach avoids self-joins and scales linearly with proper partitioning.

SELECT *,
 SUM(new_sess) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM (
 SELECT *, CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
 > INTERVAL '30 min' THEN 1 ELSE 0 END AS new_sess
 FROM events) t;

ROLLUP generates a hierarchy of subtotals along an ordered list of columns plus a grand total (n+1 grouping levels). CUBE generates subtotals for every possible combination of the grouping columns (2^n levels). Use ROLLUP for hierarchical totals (year, month) and CUBE for full cross-tab subtotals.

With ROLLUP/CUBE, subtotal rows contain NULLs in the columns that were aggregated away, which is indistinguishable from genuine NULL data. GROUPING(col) returns 1 when the row is a subtotal/aggregate for that column and 0 otherwise, letting you label or filter subtotal rows correctly.

SELECT region, GROUPING(region) AS is_subtotal, SUM(sales)
FROM t GROUP BY ROLLUP(region);

OFFSET N requires the engine to scan and discard the first N rows on every page, so deep pages get progressively slower (O(N)). Keyset (seek) pagination instead filters WHERE (sort_key) > last_seen_value ORDER BY sort_key LIMIT k, using the index to jump directly to the next page in roughly constant time.

SELECT * FROM t
WHERE id > :last_id
ORDER BY id
LIMIT 50;

COALESCE returns the first non-NULL argument from a list, used to supply defaults. NULLIF(a, b) returns NULL when a equals b and otherwise returns a. A common NULLIF use is guarding division: dividing by NULLIF(denominator, 0) turns a divide-by-zero into NULL instead of an error.

SELECT revenue / NULLIF(visits, 0) AS rev_per_visit FROM t;

Taking AVG of already-averaged group values gives an unweighted average of averages, which is biased when groups have different sizes. The correct overall mean requires weighting by group counts: SUM(group_avg * group_count) / SUM(group_count), or equivalently SUM(raw_value)/COUNT(raw_value) over the underlying rows.

SELECT SUM(grp_sum) / SUM(grp_cnt) AS true_mean
FROM (SELECT SUM(x) grp_sum, COUNT(*) grp_cnt FROM t GROUP BY g) s;