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.
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.
Construct a JSON object literal from individual column values within a query, returning structured data without a separate serialization step.
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.
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.
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.
Remove rows from one table when a matching row exists in another table using a subquery.
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 every possible pairing of rows from two tables using `CROSS JOIN`.
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.
Assign sequential integers to rows within a partition without collapsing the result set the way `GROUP BY` does.
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.
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 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.