Interview Question Bank · COUNT FILTER · timestamp vs date · excluding NULLs
1. On-time delivery rate by carrier
EasyLogisticsQuestion #157
Concept: COUNT FILTER · timestamp vs date · excluding NULLsshow
An on-time rate is on-time deliveries divided by deliveries, so first decide what belongs in the denominator: a shipment still in transit has no outcome yet. When a promise is a date and the delivery is a timestamp, compare like with like: a timestamp compared to a date is compared to midnight at the start of that day. Cast the timestamp with ::date first.
Example (different data):
SELECT clinic, COUNT(*) AS seen, COUNT(*) FILTER (WHERE seen_at::date <= booked_for) AS seen_on_day FROM appointments WHERE seen_at IS NOT NULL GROUP BY clinic;
Your task
Larkspur Freight, a third-party logistics company, is renegotiating its carrier contracts and wants each carrier's on-time record for March 2025. A delivery any time on the promised day counts as on time.
Using delivered shipments only (delivered_at is not NULL), return carrier (name), delivered, on_time (delivered on or before the promised_by date) and on_time_pct (on_time as a % of delivered, rounded to 1 decimal). Sort by on_time_pct descending, then carrier.
Starting Postgres in your browser…
Checking your access…