What's in the Book

with computed_data as (
  select cast(date as date) as date,
         to_char(date, 'Dy') as day,
         coalesce(dollars, 0) as dollars,
         lag(dollars, 1)
           over(
             partition by extract('isodow' from date)
                 order by date
           ) as last_week_dollars
    from generate_series(
           date :'start' - interval '1 week',
           date :'start' + interval '1 month'
                         - interval '1 day',
           interval '1 day'
         ) as calendar(date)
         left join factbook using(date)
)
  select date, day,
         to_char(coalesce(dollars, 0),
                 'L99G999G999G999') as dollars,
         case when dollars is not null
               and dollars <> 0
              then round(100.0
                         * (dollars - last_week_dollars)
                         / dollars, 2)
          end as "WoW %"
    from computed_data
   where date >= date :'start'
order by date;

Generate a full month calendar from a date parameter, fill in missing days, and compute week-over-week percentage changes — entirely in SQL.

-- name: list-albums-by-artist
-- Returns album titles and total duration
-- for a given artist, ordered alphabetically.
  select album.title as album,
         sum(milliseconds)
           * interval '1 ms' as duration
    from album
         join artist using(artistid)
         left join track using(albumid)
   where artist.name = :name
group by album
order by album;

Named, version-controlled SQL files with parameters, style conventions, and unit tests — SQL treated as a first-class citizen alongside your application code.

with points as (
   select year as season, driverid,
          constructorid,
          sum(points) as points
     from results join races using(raceid)
 group by grouping sets(
            (year, driverid),
            (year, constructorid))
   having sum(points) > 0
),
tops as (
   select season,
          max(points) filter(where driverid is null)
            as ctops,
          max(points) filter(where constructorid is null)
            as dtops
     from points
 group by season
),
champs as (
   select tops.season,
          d.driverid, d.points,
          c.constructorid, c.points
     from tops
          join points d
            on d.season = tops.season
           and d.constructorid is null
           and d.points = tops.dtops
          join points c
            on c.season = tops.season
           and c.driverid is null
           and c.points = tops.ctops
)
  select season,
         format('%s %s', forename, surname)
           as "Driver's Champion",
         constructors.name as "Constructor"
    from champs
         join drivers using(driverid)
         join constructors using(constructorid)
order by season;

One query, all F1 world champions since 1950 — driver and constructor — using GROUPING SETS and chained CTEs.

create table rates (
  currency text,
  validity daterange,
  rate     numeric,
  exclude using gist (
    currency with =,
    validity with &&
  )
);

insert into rates(currency, validity, rate)
     select currency,
            daterange(date,
              lead(date) over(
                partition by currency
                    order by date),
              '[)') as validity,
            rate
       from raw.rates
   order by date;

A currency rates table where overlapping validity periods are impossible — enforced by an exclusion constraint on a range type, not application code.

create view v.season_points as
  select year as season, driver,
         constructor, points
    from seasons
         left join lateral (
            select drivers.surname as driver,
                   constructors.name as constructor,
                   sum(points) as points
              from results
                   join races using(raceid)
                   join drivers using(driverid)
                   join constructors using(constructorid)
             where races.year = seasons.year
          group by grouping sets(
                     drivers.surname,
                     constructors.name)
            order by drivers.surname is not null,
                     points desc
         ) as points on true
order by year, driver is null, points desc;

create materialized view cache.season_points as
  select * from v.season_points;

create index on cache.season_points(season);

A three-layer design: raw tables → a logical view → a materialized cache, with a LATERAL join computing grouping sets per season. Fast reads, clean separation.

merge into driver_points dp
using (
      select d.driverid, d.surname,
             coalesce(sum(res.points), 0) as total
        from drivers d
             left join results res using(driverid)
       group by d.driverid, d.surname
) src
on dp.driverid = src.driverid
when matched then
    update set total = src.total,
               surname = src.surname
when not matched by target then
    insert (driverid, surname, total)
    values (src.driverid, src.surname, src.total)
when not matched by source then
    delete;

One MERGE statement: insert new drivers, update existing totals, remove deleted rows — atomically, safe under concurrent writes.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

WITH
-- Semantic: nearest neighbours by cosine distance
vec AS (
    SELECT id, title, artist,
           row_number() OVER (
             ORDER BY embedding <=> $1) AS rank
      FROM track_embedding LIMIT 50
),
-- Lexical: trigram similarity on title
trgm AS (
    SELECT id, title, artist,
           row_number() OVER (
             ORDER BY similarity(title, $2) DESC) AS rank
      FROM track_embedding
     WHERE title % $2 LIMIT 50
),
-- Reciprocal Rank Fusion
fused AS (
    SELECT coalesce(v.id, t.id) AS id,
           coalesce(v.title, t.title) AS title,
           coalesce(v.artist, t.artist) AS artist,
           coalesce(1.0 / (v.rank + 60), 0) +
           coalesce(1.0 / (t.rank + 60), 0) AS score
      FROM vec v FULL JOIN trgm t USING (id)
)
SELECT id, title, artist,
       round(score::numeric, 5) AS score
  FROM fused ORDER BY score DESC LIMIT 10;

Hybrid search: combine pgvector's cosine distance with pg_trgm's fuzzy matching via Reciprocal Rank Fusion — results that match both meaning and spelling.