Data Analyst Interview Questions & Answers: Set 11, Logistics & Supply Chain

SQL

1. 1.Find shipments that took longer than the average delivery time for their route.

   Hide answer

   Answer: Compute each route's average delivery time with a window function, then keep shipments above it. Assumed table: shipments(shipment_id, route_id, pickup_ts, delivered_ts). The window lets me compare every row with its own route average without a separate join. Trap: you cannot put a window function directly in WHERE, so I calculate it in a CTE first. I exclude undelivered shipments because they have no delivery time yet.

   WITH t AS (
     SELECT shipment_id, route_id,
            delivered_ts - pickup_ts AS delivery_time,
            AVG(delivered_ts - pickup_ts)
              OVER (PARTITION BY route_id) AS route_avg
     FROM shipments
     WHERE delivered_ts IS NOT NULL
   )
   SELECT shipment_id, route_id, delivery_time, route_avg
   FROM t
   WHERE delivery_time > route_avg
   ORDER BY route_id, delivery_time DESC;

2. 2.Calculate the on-time delivery percentage per courier partner per month.

   Hide answer

   Answer: Group by courier and month, and divide on-time deliveries by total deliveries. Assumed table: shipments(courier_id, promised_date, delivered_date). On-time means delivered_date <= promised_date. Two traps: integer division (so I multiply by 100.0), and ignoring parcels that are still undelivered past their promise date. Those are late too, so in a real report I would include them as late or show them separately.

   SELECT courier_id,
          DATE_TRUNC('month', delivered_date) AS month,
          COUNT(*) AS delivered,
          ROUND(100.0 * SUM(CASE WHEN delivered_date <= promised_date
                                 THEN 1 ELSE 0 END) / COUNT(*), 1)
            AS on_time_pct
   FROM shipments
   WHERE delivered_date IS NOT NULL
   GROUP BY courier_id, DATE_TRUNC('month', delivered_date)
   ORDER BY courier_id, month;