Daily open ticket backlog

The support lead wants a backlog chart: for each day, how many tickets were still open when the day ended. A ticket is open on a day if it was opened that day or earlier, and it hadn’t been closed by the end of that day.

Use dates, not times:

• A ticket counts on day when opened_at’s date is on or before day, and either closed_at is NULL or closed_at’s date is after day.
• A ticket opened and closed on the same day never counts.
• A ticket closed at exactly midnight belongs to the day that starts at that midnight, so it still counts as open on the day before.

Return one row for every calendar day from the earliest opened_at date to the latest opened_at date, inclusive:

column	type	meaning
day	date	the calendar day
open_tickets	integer	tickets open at the end of that day; 0 if none

Sort by day, oldest first. Order matters.

Tables: tickets.

Approach

Generate every day in range, then join each ticket to every day it was open, and count per day.

WITH bounds AS (
  SELECT MIN(CAST(opened_at AS DATE)) AS first_day,
         MAX(CAST(opened_at AS DATE)) AS last_day
  FROM tickets
),
spine AS (
  SELECT CAST(d AS DATE) AS day
  FROM bounds,
       generate_series(first_day, last_day, INTERVAL 1 DAY) AS g(d)
)
SELECT s.day,
       COUNT(t.ticket_id) AS open_tickets
FROM spine s
LEFT JOIN tickets t
  ON  CAST(t.opened_at AS DATE) <= s.day
  AND (t.closed_at IS NULL OR CAST(t.closed_at AS DATE) > s.day)
GROUP BY s.day
ORDER BY s.day;

Why it works

A ticket is a range of days. The spine is a list of points. The join condition asks “is this point inside this range?”, so each ticket fans out to one row per day it was open. Counting rows per day then gives the backlog.

The close test is strict (>), so the close date itself isn’t counted, and a same-day ticket covers no days at all. A midnight close has the next day’s date, so it still counts on the day before. Open tickets have no close, so the IS NULL branch keeps them on every day from their open date onward.

The LEFT JOIN and COUNT(t.ticket_id) keep any day with an empty backlog as 0.

Under the hood

There’s no equality in the join condition, so a hash join can’t be used. DuckDB plans this as a range join (IEJoin or a piecewise merge join), which sorts both sides on the compared columns instead of checking every pair. The OR ... IS NULL makes the condition harder to plan, and at small sizes it may fall back to a nested loop: 120 days × 800 tickets is 96,000 checks, which is nothing. At a million tickets you’d compute daily opens and closes instead and take a running sum, which is a single sorted pass.