The analytical half of SQL — CTEs and recursion, the full window-function arc, GROUPING SETS, pivoting, dates, strings and regex, JSON/JSONB, arrays and LATERAL — run against PostgreSQL 18 over the bookshop.
WITH clauses that let you read a query top-to-bottom instead of inside-out — naming a subquery, chaining several, referencing one from another, and the inlining rule that changed in Postgres 12. Run against PostgreSQL 18.
WITH RECURSIVE, the one query that can refer to its own output — generating sequences of numbers and dates, walking a hierarchy row by row, and the cycle that runs forever until you stop it. Run against PostgreSQL 18.
The OVER clause that computes across a set of rows without collapsing them — how a window differs from GROUP BY, and how ROW_NUMBER, RANK, DENSE_RANK, and NTILE each handle a tie. Run against PostgreSQL 18.
Controlling exactly which rows a window function spans — ROWS for running totals and moving averages, and the default RANGE frame that quietly includes tied peers and inflates your running sum. Run against PostgreSQL 18.
Window functions that read a specific other row — LAG and LEAD for month-over-month change, FIRST_VALUE and LAST_VALUE with the frame trap that quietly breaks LAST_VALUE, and the ROW_NUMBER top-N-per-group pattern. Run against PostgreSQL 18.
One query that returns the detail rows and their subtotals and the grand total together — GROUPING SETS to name each grouping, ROLLUP for running subtotals, CUBE for every combination, and GROUPING() to tell a subtotal NULL from a real one. Run against PostgreSQL 18.
Turning a category column into one column per value using conditional aggregation — sum/count with FILTER, the portable CASE form, the tablefunc crosstab alternative, and unpivoting back to rows with LATERAL and a VALUES list. Run against PostgreSQL 18.
The CASE expression in both forms, bucketing continuous values into labeled tiers, CASE in ORDER BY and GROUP BY, conditional aggregation as the recurring analytical idiom, and COALESCE and NULLIF as the CASE shorthands worth knowing. Run against PostgreSQL 18.
date_trunc for bucketing into months and weeks, interval arithmetic and extract/date_part and age for pulling time apart, and the generate_series left-join trick that makes empty periods show up as zero instead of vanishing. Run against PostgreSQL 18.
Reshaping text in the database — case and trimming, substrings and splitting, concatenation, then the pattern-matching ladder from LIKE up through POSIX regular expressions and the regexp_ family. Run against PostgreSQL 18.
Storing and querying semi-structured data inside a relational database — why jsonb beats json, the access operators, shredding a document into rows and building rows back into a document, containment and path queries, and where a GIN index fits. Run against PostgreSQL 18.
Postgres arrays as first-class values — array_agg, unnest, ANY/ALL, overlap — and then LATERAL, the correlated join that lets a subquery reference the outer row, with the clean top-N-per-group it makes possible. Run against PostgreSQL 18.
Assembling a real monthly business report from the whole toolbox — CTEs to stage, date_trunc to bucket, window functions for running totals and month-over-month growth, FILTER for splits, and a per-month ranking — built up one layer at a time. Run against PostgreSQL 18.