Logistics & Marketplace Dynamics Fundamentals Questions

SELECT region,
       DATE(delivered_ts) as day,
       COUNT(*) as deliveries,
       AVG(EXTRACT(EPOCH FROM (delivered_ts - pickup_ts))/60) avg_lead_min,
       STDDEV_POP(EXTRACT(EPOCH FROM (delivered_ts - pickup_ts))/60) var_lead_min
FROM deliveries
WHERE delivered_ts >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY region, DATE(delivered_ts)
ORDER BY region, day;

WITH daily_var AS (... previous query ...)
SELECT d.region, d.day, d.var_lead_min, e.job_name, e.status, e.run_ts
FROM daily_var d
LEFT JOIN ETL_logs e
  ON DATE(e.run_ts) = d.day
  AND e.affected_entities LIKE '%' || d.region || '%'
WHERE d.day BETWEEN '2025-11-15' AND '2025-11-30'
ORDER BY d.region, d.day;

SELECT region, carrier_id,
       COUNT(*) deliveries,
       AVG(lead_time) avg_lead,
       STDDEV_POP(lead_time) std_lead,
       SUM(CASE WHEN lead_time > sla_minutes THEN 1 ELSE 0 END) sla_breaches
FROM (
  SELECT dl.*, EXTRACT(EPOCH FROM (delivered_ts - pickup_ts))/60 as lead_time, c.sla_minutes
  FROM deliveries dl
  LEFT JOIN carrier_sla c ON dl.carrier_id = c.carrier_id
) t
WHERE DATE(delivered_ts) BETWEEN '2025-11-15' AND '2025-11-30'
GROUP BY region, carrier_id
ORDER BY region, std_lead DESC;

SELECT d.region, d.day, AVG(d.lead_time) avg_lead, w.severe_flag, COUNT(r.event_type) events
FROM (
  SELECT region, DATE(delivered_ts) day,
         EXTRACT(EPOCH FROM (delivered_ts - pickup_ts))/60 as lead_time
  FROM deliveries
  WHERE delivered_ts BETWEEN '2025-11-15' AND '2025-11-30'
) d
LEFT JOIN weather_feed w ON w.region = d.region AND DATE(w.ts_hour) = d.day
LEFT JOIN regional_events r ON r.region = d.region AND d.day BETWEEN DATE(r.event_start) AND DATE(r.event_end)
GROUP BY d.region, d.day, w.severe_flag;

SELECT
  region,
  ROUND(100.0 * SUM(CASE WHEN delivered_at <= expected_delivery_at THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0), 2) AS on_time_rate,
  COUNT(*) AS total_deliveries
FROM deliveries
WHERE
  -- only consider completed deliveries in the last 30 days
  delivered_at IS NOT NULL
  AND expected_delivery_at IS NOT NULL
  AND status <> 'cancelled'    -- assumes there's a status column; if not, filter by business logic
  AND delivered_at >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY region
ORDER BY region;