What pickup_at actually contains in ride-hail-trips

Working through ride-hail-trips again. pickup_at is the column people trip over, so here is what it actually looks like.

8,000 rows, no nulls, 7,998 distinct values. Trip start, UTC, second resolution.

It runs from 2023-01-01 02:27:59 to 2023-12-31 23:49:18, covering 365 distinct days.

It is a timestamp, not a date, which means every comparison against a bare date is a comparison against midnight. pickup_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.

select
  count(*)                    as rows,
  count(pickup_at)            as present,
  count(*) - count(pickup_at) as nulls,
  count(distinct pickup_at)   as distinct_values
from ride_hail_trips;

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: 365 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 completed trip, 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.