LogisticsLT Pro — Standalone

## The scenario You are embedded in the BI team at a UK national parcel carrier, reporting to the Head of Network Analytics. The Operations Director has commissioned a quarterly Network Performance Dashboard backed by your SQL: master shipment view + depot hierarchy, parameterised depot performance report, SLA and weight distribution analytics, cumulative daily volume pipeline, top performers + SLA positioning, stuck-parcel detection, and a performance rewrite. ## Deliverables **Pane 1.** Network Master View + Depot hierarchy via recursive CTE over the self-referencing Depot table. **Pane 2.** Parameterised `usp_DepotPerformanceReport` with three result sets from a named CTE chain. **Pane 3.** Window-function trio — NTILE(4) weight-band quartiles, RANK/DENSE_RANK for top routes per depot, ROW_NUMBER for deterministic latest-event-per-shipment. **Pane 4.** Idempotent temp-table pipeline computing 60-day cumulative shipments-in vs deliveries-out with NetPending running balance. **Pane 5.** Top-performing routes + SLA positioning — subquery vs CTE comparison. **Pane 6.** Stuck-parcel detection using LAG over (ShipmentID ORDER BY EventDateTime) flagging shipments idle > 48 hours. **Pane 7.** Performance rewrite of a legacy nightly DNA-equivalent report (delivery-failure scan) with annotated fixes. ## Acceptance criteria (summary) Same engineering rigour as the other Pro capstones — deliberate CTE names, deliberate PARTITION choices, idempotent pipelines, sargable filters, all five performance-rewrite issues addressed, foundation View referenced by at least three downstream panes. Defence is a 45-minute code review. Full brief, ERD, and the legacy query appear inside the lesson once enrolled.