SQL snippets

15 snippets

• SQL

  Top N rows per group

  Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.

• SQL

  Soft delete with a partial unique index

  Keep deleted rows but still enforce unique emails among active rows using a partial unique index.

• SQL

  Walk a tree with a recursive CTE

  Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.

• SQL

  RANK, DENSE_RANK and ROW_NUMBER

  Understand how the three ranking window functions treat ties, with one query that shows all three.

• SQL

  Pivot rows into columns with FILTER

  Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.

• SQL

  Percent of total with a window function

  Show each row's share of the grand total or of its group without a self-join.

• SQL

  Moving average with a window frame

  Compute a 7-day rolling average with AVG over a ROWS BETWEEN frame.

• SQL

  Query JSONB and index it with GIN

  Filter on JSONB fields with the containment operator and make it fast with a GIN index.

• SQL

  Fill in missing dates with generate_series

  Produce a row for every day in a range and left join your data so days with no activity show zero.

• SQL

  Automatically maintain an updated_at column

  A reusable PostgreSQL trigger function that sets updated_at to now() on every UPDATE.

• SQL Tested

  Upsert with INSERT ... ON CONFLICT (PostgreSQL)

  Insert a row or update it if the unique key already exists, atomically and safely under concurrency.

• SQL Tested

  Running total with a window function

  Compute cumulative sums per group without self-joins using SUM() OVER (PARTITION BY ... ORDER BY ...).