The time coverage of movie-ratings

Before any time series work on movie-ratings: what does rated_at actually cover?

2023-01-01 00:22:37 to 2025-06-19 23:13:27. That is 901 calendar days, of which 901 have at least one row — so the series is dense. Average 10.0 rows per populated day.

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

A dense series means a plain group by day is safe. That is worth confirming rather than assuming — the moment a day drops out, every window function that counts rows instead of days starts comparing the wrong pair, and a seven-day lag silently becomes an eight-day one.

The other number worth having before you start: 10.0 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 timestamp rather than a date, every daily aggregate embeds a time zone decision. date_trunc('day', rated_at) truncates in whatever zone the session is set to, so the same query returns different daily totals for two people in different offices. Truncating explicitly in UTC and converting for display is the version that does not produce meetings.

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.