Drill 1 of 12 in Logistics questions
0

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…