Interview Question Bank · Date subtraction · AVG skips NULLs, COUNT(*) doesn't
2. Average transit days by route
EasyLogisticsQuestion #158
Concept: Date subtraction · AVG skips NULLs, COUNT(*) doesn'tshow
Subtracting one date from another in Postgres gives a whole number of days, so casting two timestamps to ::date and subtracting them counts calendar days. Subtracting the timestamps themselves gives an interval like '1 day 20:00', which can't be averaged into a plain number of days without extra work. AVG ignores NULLs, but COUNT(*) does not, so filter out unfinished rows if you report a count next to an average.
Example (different data):
SELECT branch, COUNT(*) AS closed_loans, ROUND(AVG(returned_at::date - borrowed_at::date), 1) AS avg_days FROM library_loans WHERE returned_at IS NOT NULL GROUP BY branch;
Your task
Larkspur Freight's network planner wants to know which lanes are slowest. A route has a direction: Phoenix to Denver is a different lane from Denver to Phoenix.
For delivered shipments, return origin, destination, deliveries and avg_transit_days (the average of delivered_at's date minus shipped_at's date, in calendar days, rounded to 1 decimal). Sort by avg_transit_days descending, then origin, then destination.
Starting Postgres in your browser…
Checking your access…