The time coverage of saas-subscriptions

Before any time series work on saas-subscriptions: what does signed_up_on actually cover?

2023-01-04 to 2025-12-01. That is 1,063 calendar days, of which 947 have at least one row — so 116 days are missing entirely. Average 2.6 rows per populated day.

select date_trunc('day', signed_up_on) as day, count(*) as rows
from saas_subscriptions
group by 1
order by 1;

Missing days are the argument for a date spine: join the calendar to the data rather than the other way round, so a zero-activity day appears as a zero instead of vanishing. Without it, a line chart draws a straight segment across the gap and a stakeholder reads it as "flat" rather than "no data".

The other number worth having before you start: 2.6 rows per populated day tells you what granularity the data can actually support. Aggregating to something finer than the data is dense enough to fill produces a chart of noise, and there is no warning — the query returns, the line is drawn, and the wobble gets interpreted.

Because this is a date rather than a timestamp, daily aggregates are unambiguous — no time zone, no truncation, no boundary argument. That is a small thing that removes a whole category of bug, and it is worth preferring a date column whenever the time component is not genuinely used.

A date range is a fact about the data, not about the question. Filters outside it return empty results, filters half-inside it return partial ones, and neither raises anything. Check the range, then write the filter — and use a half-open interval when you do, because between on a timestamp includes exactly one instant of the final day.