Five minutes with resolved_at in support-tickets

Someone asked what is in resolved_at on support-tickets, and the honest answer took one query.

4,000 rows, 225 nulls (5.6%), 3,775 distinct values. Resolution time. Blank if still open.

It runs from 2023-01-01 07:46:35 to 2024-02-04 22:44:31, covering 400 distinct days.

It is a timestamp, not a date, which means every comparison against a bare date is a comparison against midnight. resolved_at <= date '2023-06-30' silently excludes almost all of 30 June. Half-open ranges — >= start and < end — avoid the whole class of off-by-one-day bugs and read no worse.

225 rows have no value at all here, which is 5.6% of the table. That is enough to move an aggregate and small enough that nobody notices it doing so — every average above is computed over 3,775 rows, not 4,000, and the two denominators produce different answers.

select
  count(*)                      as rows,
  count(resolved_at)            as present,
  count(*) - count(resolved_at) as nulls,
  count(distinct resolved_at)   as distinct_values
from support_tickets;

Check the range before filtering on it. Half the "the dashboard is empty" reports we have seen are a date filter outside the data's actual range, and the query is not wrong so nothing errors.

Where this bites: 400 populated days is what any window function over this column has to work with. A seven-day lag counts rows, not days — so if a day is missing, lag(7) quietly compares against eight days ago and the week-over-week number is wrong in a way that looks plausible.

The grain is one row per ticket, which is the context every one of those numbers depends on. None of them survive a change of grain, which is why "profile the column" and "profile the table" are the same job. Full schema, and the CSV, on the dataset page.