Drill 2 of 12 in Logistics questions
0

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…