SQL Applied Portfolio Trilogy

## The scenario You are a Data Quality Analyst embedded in the Trust's Information Governance team. NHS England has notified the Trust that a data quality review begins in eight weeks, focusing on patient-pathway integrity (are admissions properly closed?), RTT compliance (are 18-week targets being tracked accurately?), and clinical coding completeness (are diagnoses coded to ICD-10?). Each finding becomes a remediation ticket. Each ticket needs an owner. Your SQL is the evidence. ## Dataset The full PatientLT schema — Patient, Hospital, Department (self-referencing), Staff (self-referencing), Admission, Appointment, Diagnosis, WaitingList, Trust. Realistic data-quality flaws are deliberately planted to mirror real NHS inspection findings: open admissions with no discharge, admissions in deactivated departments, RTT breaches, DNA appointments with no reschedule, NULL ICD-10 codes. ## Deliverables Twelve outputs across six investigation themes, plus one reusable View: **A. Joins & enrichment** — active patient directory, never-admitted patients, attendance summary by department. **B. Orphan detection** — admissions in deactivated departments, appointments linked to terminated staff. **C. Duplicate detection** — multiple open admissions per patient, duplicate registrations by name+DOB. **D. Missing relationships** — waiting-list patients with no follow-up appointment, admissions with no diagnosis, DNAs with no reschedule. **E. Reconciliation** — RTT breach report, attendance-vs-diagnosis quadrant reconciliation. **F. View** — `vw_PatientAuditCard` with per-patient quality flags + three queries against it. Every query is preceded by the four-line Investigation Framework header (Entity / Relationship / Question / Pattern). ## Acceptance criteria (summary) Investigation Framework header present and accurate · correct JOIN type (no INNER where LEFT is required) · deliberate NULL handling (no silent row drops) · row counts within expected ranges (the brief specifies ranges per output for sanity-checking) · view F1 reusable and deterministic. Defence is a 30-minute live code review focusing on JOIN reasoning, cardinality, and how query design reflects the business question. Full brief, ERD, and expected row-count ranges appear inside the lesson once enrolled.

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

## The scenario You are a Network Data Analyst at a UK national parcel carrier. The Operations Director needs a Network Data Quality Audit before the next quarterly review — focusing on depot-hub assignment integrity, driver-vehicle continuity, shipment-event chain completeness, and SLA breach attribution. ## Dataset The full LogisticsLT schema — Region, Hub, Depot (self-referencing), Vehicle, Driver, Customer, Route, Shipment, ShipmentEvent, DeliveryAttempt. Realistic flaws are planted: orphan depots on inactive hubs, drivers assigned to retired vehicles, shipment events out of sequence, delivery attempts with no resolution. ## Deliverables Twelve outputs across six themes: **A. Joins & enrichment** — active route directory with driver + vehicle, hub-region rollup, depot self-join. **B. Orphan detection** — depots on deactivated hubs, routes on retired vehicles. **C. Duplicate detection** — duplicate active shipments by tracking ID, duplicate driver assignments on the same shift. **D. Missing relationships** — shipments with no scan events, delivery attempts with no resolution, drivers with no active route. **E. Reconciliation** — SLA breach attribution FULL OUTER JOIN, event-sequence completeness check. **F. View** — `vw_ShipmentAuditCard` per-shipment quality flags + three queries against it. Every query carries the four-line Investigation Framework header. ## Acceptance criteria (summary) Same rigour as the other Applied capstones — Framework header on every query, correct JOIN type chosen for the cardinality, NULL handling deliberate, expected row-count ranges met, view F1 reusable. Full brief, ERD, and expected row-count ranges appear inside the lesson once enrolled.