posted_on runs 2023-01-01 to 2025-09-27

Coverage note for data-job-postings, which is the thing to check first and the thing everyone checks last.

2023-01-01 to 2025-09-27. That is 1,001 calendar days, of which 966 have at least one row — so 35 days are missing entirely. Average 3.6 rows per populated day.

select date_trunc('day', posted_on) as day, count(*) as rows
from data_job_postings
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: 3.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.