EnergyLT Applied — Standalone

## The scenario You are an Operations Data Analyst at a UK domestic energy supplier. The Director of Operations needs a Customer & Billing Data Quality Audit ahead of the next Ofgem reporting cycle — focusing on referral-chain integrity, billing-vs-payment reconciliation, contract continuity (no gaps in cover), and customer-care follow-up. ## Dataset The full EnergyLT schema — Region, Tariff, Property, Meter, Customer (self-referencing via referral), Contract, MeterReading, Bill, Payment, SupportTicket. Realistic flaws are planted: meters on deactivated tariffs, bills with no payment past 60 days, contract gaps, support tickets with no resolution. ## Deliverables Twelve outputs across six themes: **A. Joins & enrichment** — active customer directory with property + current contract, region-level customer counts, referral self-join. **B. Orphan detection** — meters on deactivated tariffs, contracts on closed customer accounts. **C. Duplicate detection** — duplicate customer registrations, duplicate active meters per property. **D. Missing relationships** — bills with no payment, customers with no support follow-up, contracts with no successor on expiry. **E. Reconciliation** — debt-ageing FULL OUTER JOIN, consumption-vs-billing variance. **F. View** — `vw_CustomerAuditCard` per-customer quality flags + three queries against it. Every query carries the four-line Investigation Framework header. ## Acceptance criteria (summary) Same Investigation Framework rigour as the other Applied capstones — correct JOIN type, deliberate NULL handling, business-friendly aliases, expected row-count ranges met, view F1 genuinely reusable. Full brief, ERD, and expected row-count ranges appear inside the lesson once enrolled.