Rank archive
Guides grouped by the expected difficulty and amount of SQL context they assume.
Reference values from preceding or following rows within an ordered partition without writing a self-join.
Return a value from the first row of a window partition, useful for comparing each row against the group leader.
Produce multiple independent aggregation levels in one query using `GROUPING SETS` — more flexible than `ROLLUP` because you choose exactly which combinations to include.
Use `CASE WHEN` inside `SUM` or `COUNT` to produce multiple metrics from a single pass over grouped data.
Collect values from multiple rows into a single JSON array, ordered and grouped by other columns.
Group a stream of user events into sessions by labelling each event with a session ID that increments whenever the gap since the previous event exceeds a threshold.
Use `WIDTH_BUCKET(value, low, high, n)` to assign each row to one of `n` equal-width buckets between `low` and `high` — the foundation for building numeric histograms directly in SQL.
Construct a JSON object literal from individual column values within a query, returning structured data without a separate serialization step.
Accumulate a breadcrumb path string as a recursive CTE walks a parent-child tree.
Accumulate a column's values row by row using `SUM` as a window function.
Use `SUM(...) OVER ()` to compare each row's value to the total without collapsing the result set.
Use percent-rank window functions to express each row's position within an ordered group.
Define several named subqueries in one `WITH` block, each building on the previous, before the final `SELECT`.
Return all rows from both tables, filling NULLs on either side when there is no matching row.
Pull the prior year's value into the current row with `LAG`, then compute the percentage change — a common pattern for revenue, signups, and other business KPIs.
Find the median or any percentile value using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server, or a window-function workaround in MySQL and SQLite.
Smooth time-series data by computing a rolling average over a fixed window of preceding rows using `AVG … OVER (ORDER BY … ROWS BETWEEN n PRECEDING AND CURRENT ROW)`.
Use `GROUP BY CUBE(col_a, col_b)` to generate subtotals for every possible combination of those columns — the full power set — in a single query.
Measure the strength of a linear relationship between two columns with `CORR(y, x)` and covariance with `COVAR_SAMP` / `COVAR_POP` in PostgreSQL.
Aggregate multiple string values from grouped rows into a single delimited string.
Use `ROWS BETWEEN` in a window function to aggregate only the rows immediately surrounding each row.
Reshape a wide table by turning multiple columns into key-value row pairs — the inverse of a PIVOT operation.
Use `ROW_NUMBER()` to identify duplicate rows and delete every copy except the one you want to keep.
Remove child rows that reference missing parent rows before enforcing referential integrity.
Remove rows from one table when a matching row exists in another table using a subquery.
Remove rows from one table based on matching rows in another table.
Flag values that fall outside the interquartile fence `[Q1 − 1.5 × IQR, Q3 + 1.5 × IQR]` using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server.
Return the portion of a string that matches a regular expression pattern, useful for parsing structured text without fixed delimiters.
Return all rows from the right table and matching rows from the left, filling NULL where no match exists.
Produce a row for every date in a range — even dates with no data — by generating a date spine and left-joining actual records onto it.
Return rows where a JSON array contains a specific scalar value.
Generate the expected numbers and left join to the existing table to find missing ids or sequence values.
Compute the least-squares slope, intercept, and R² of a linear regression directly in SQL using `REGR_SLOPE(y, x)`, `REGR_INTERCEPT(y, x)`, and `REGR_R2(y, x)` in PostgreSQL.
Produce a consecutive sequence of integers without a base table — using `generate_series` in PostgreSQL or a recursive CTE in all other engines.
Produce every possible pairing of rows from two tables using `CROSS JOIN`.
Use LAST_VALUE with an explicit full-partition frame to compare each row with the final row in its group.
Create stable run groups without a primary key by subtracting two `ROW_NUMBER()` calculations over the same ordered events.
Atomically insert a row if it does not exist or update it if it does, using the database's native conflict-resolution syntax.
Reference the same table twice using aliases to compare or relate rows within the same dataset.
Acquire a row-level write lock inside a transaction so no other session can modify those rows before your update completes.
Rescale each row's value to the number of standard deviations it sits above or below the mean using `(value − AVG … OVER ()) / STDDEV_SAMP … OVER ()`.
Assign sequential integers to rows within a partition without collapsing the result set the way `GROUP BY` does.
Mark a named checkpoint inside an open transaction so you can roll back to that point without aborting the whole transaction.
Assign rank numbers to rows within a partition, controlling whether tied ranks leave gaps in the sequence.
Extract a scalar value from a JSON column using the JSON function or operator each engine supports.
Use `LATERAL` (or `CROSS APPLY` on SQL Server) to let a subquery reference columns from the preceding table in the `FROM` clause.
Truncate a timestamp to the start of a month, day, or hour using engine-specific functions.
Use `ROW_NUMBER()` to rank rows within each group and keep only the highest-ranked row.
Control how much a transaction can see concurrent changes made by other transactions.
Create an index on one or more columns to make lookups and joins faster.
Expand a comma-separated or delimited string into one row per element, turning a single cell into a proper set of rows.
Use `MERGE` to insert new rows, update matching ones, and delete rows that should be removed — all in a single statement against a source table.
Use a correlated subquery inside `EXISTS` to include rows only when related rows are found in another table.
Use `WITH RECURSIVE` to repeatedly join a query to its own results, enabling traversal of tree or graph structures.
Use a join inside an `UPDATE` statement to copy or derive values from a related table.
Schema and metadata choices tend to survive longer than any one statement, which is why naming and structure deserve more care than a temporary query draft.