Weekly ticket change

The support lead reviews ticket volume every Monday. For each week they want the count, last week’s count, and the difference.

A week starts on Monday. A ticket belongs to the week containing its opened_at, so a ticket opened Sunday at 23:59 belongs to the week that started six days earlier. Report the week as the Monday’s date (2025-01-01 is a Wednesday, so the first week is 2024-12-30).

Return one row per week that has at least one ticket:

column	type	meaning
week_start	date	the Monday that starts the week
tickets	integer	tickets opened that week
prev_tickets	integer	tickets from the previous row; NULL for the first week
change	integer	tickets - prev_tickets; NULL for the first week

The shipped data has no empty weeks, so the previous row is always the previous calendar week.

Order matters: oldest week first.

Tables: tickets.

Approach

Count tickets per Monday-starting week, then look one row back with LAG.

WITH weekly AS (
  SELECT CAST(date_trunc('week', opened_at) AS DATE) AS week_start,
         COUNT(*) AS tickets
  FROM tickets
  GROUP BY 1
)
SELECT week_start,
       tickets,
       LAG(tickets) OVER (ORDER BY week_start) AS prev_tickets,
       tickets - LAG(tickets) OVER (ORDER BY week_start) AS change
FROM weekly
ORDER BY week_start;

Why it works

date_trunc('week', ...) uses ISO weeks, which start on Monday, so Sunday night stays in the week that began six days earlier. Grouping on that value gives one row per week.

LAG reads from the row before in the window’s order. It doesn’t know about calendars: it looks one row back, not seven days back. That’s correct here because no week is empty. If a week could be empty you’d first build a calendar of every week (a date spine) and left join the counts onto it.

The ORDER BY inside OVER only decides what “previous” means. The outer ORDER BY decides the output order. You need both.

Under the hood

The two LAG calls share one window definition, so DuckDB sorts the weekly rows once and evaluates both over the same sorted buffer. LAG is an offset read: row i reads row i - 1 of the sorted partition. There’s no frame to maintain, so it’s the cheapest window function there is.