SQL Pro Portfolio Trilogy

## The scenario You are embedded in the Trust's Business Intelligence team, reporting to the Head of Analytics. The Medical Director has commissioned a quarterly Clinical Performance Dashboard with six panes: full department hierarchy with live patient census, a parameterised performance report by department, length-of-stay percentiles by specialty, cumulative monthly admission/discharge trends, top diagnoses with readmission-risk scoring, and 30-day readmission gap detection. Each pane is a separate deliverable. You design the SQL, document the trade-offs, and defend every design decision. ## Deliverables **Pane 1.** Foundation View (`vw_ClinicalMasterView`) referenced by every downstream pane, plus a recursive CTE over the Department hierarchy with live patient census at each node. **Pane 2.** Parameterised stored procedure `usp_DepartmentPerformanceReport` returning three result sets (admission summary, appointment summary, waiting-list summary) from a named CTE chain. **Pane 3.** Window-function trio — NTILE(4) LOS quartiles partitioned by specialty, RANK/DENSE_RANK for longest stays per department, ROW_NUMBER for deterministic latest-admission-per-patient. **Pane 4.** Idempotent temp-table pipeline producing 24-month cumulative admission and discharge running totals with a date-spine. **Pane 5.** Top-5 ICD-10 diagnoses per department with readmission-risk scoring — delivered twice (subquery version vs CTE version) for side-by-side comparison. **Pane 6.** 30-day readmission gap detection using LAG over (PatientID ORDER BY AdmissionDate). **Pane 7.** Performance rewrite of a legacy DNA-report query identifying five issues (SELECT *, comma-join, non-sargable date filter, DISTINCT misuse, correlated MAX subquery) with annotated fixes. ## Acceptance criteria (summary) Every CTE named for what it computes — no t1/t2/x · window functions partitioned deliberately · recursive CTE has explicit anchor and recursive members with terminating `OPTION (MAXRECURSION N)` · stored procedure parameters carry defaults and inline comments · temp-table pipeline idempotent (DROP TABLE IF EXISTS) · date filters sargable · performance rewrite addresses all five named issues · the foundation View is referenced (not re-implemented) by at least three downstream panes. Defence is a 45-minute code review. The assessor picks two panes and walks them line-by-line — every CTE name, every PARTITION clause, every JOIN type must be defensible. Full brief, ERD, and the legacy query you must rewrite appear inside the lesson once enrolled.

## The scenario You are embedded in the BI team at a UK domestic energy supplier, reporting to the Head of Commercial Analytics. The CCO is launching a quarterly Customer Insight Dashboard backed by your SQL: customer portfolio + referral tree, parameterised regional performance report, consumption segmentation by tariff, cumulative revenue and consumption pipeline, high-value customers at switching risk, silent-meter detection, and a performance rewrite of a legacy billing query. ## Deliverables **Pane 1.** Customer Portfolio View + Referral Tree via recursive CTE over `Customer.ReferredByCustomerID`. **Pane 2.** Parameterised `usp_RegionConsumptionReport` returning three result sets from a named CTE chain. **Pane 3.** Window-function trio — NTILE(4) usage quartiles partitioned by TariffType, RANK/DENSE_RANK for top consumers per region, ROW_NUMBER for deterministic latest-bill-per-account. **Pane 4.** Idempotent temp-table pipeline computing 24-month cumulative revenue, consumption, and a NetCashPosition running balance. **Pane 5.** Retention priority list — customers in the top consumption quartile AND with outstanding debt > £50 AND at least one open support ticket — delivered twice (subquery vs CTE) for comparison. **Pane 6.** Silent-meter detection using LAG over (MeterID ORDER BY ReadingDate) to flag gaps > 90 days. **Pane 7.** Performance rewrite of a legacy nightly billing query identifying five issues with annotated fixes. ## Acceptance criteria (summary) Same engineering rigour as PatientLT Pro — deliberate CTE names, deliberate PARTITION choices, idempotent pipelines, sargable filters, all five performance-rewrite issues addressed, the 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.

## 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.