EnergyLT Pro — Standalone

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