SQL Core Portfolio Trilogy

## The scenario You are a Junior Data Analyst on placement with the Data & Analytics team at a regional NHS Foundation Trust. The Associate Director of Information has asked you to produce a one-page Patient Snapshot for the upcoming Trust Board meeting — headline registration counts, age band distribution, GP practice coverage, data completeness flags, and the ten longest-registered patients still active. The Associate Director does not write SQL. She needs clean, labelled output she can paste straight into a briefing pack. Every column header must be report-ready; every row must be interpretable by a non-technical Board member. ## Dataset PatientLT is a synthetic NHS Trust dataset hosted on the CareerSwerve Azure SQL training database. For SQL Core you work exclusively with the `PatientLT.Patient` table — same discipline the course taught: master one table completely before joining. ## Deliverables Eight outputs (A–H), each demonstrating a specific SQL Core skill, submitted as a single annotated `.sql` file: **A.** Registrations by gender (NULL as explicit "Not recorded" category) · **B.** Registrations by year · **C.** Age band distribution with logical (not alphabetical) sort · **D.** Top 10 longest-registered active patients · **E.** GP practice patient distribution · **F.** New registrations in the most recent 12 months · **G.** Active vs historical UNION summary · **H.** Single-row data completeness audit. ## Acceptance criteria (summary) Queries run cleanly against PatientLT · result sets match specified columns and sort order · NULLs handled deliberately (CONCAT / ISNULL / IS NULL) · column aliases report-friendly · date filters use GETDATE/DATEADD/DATEDIFF (no hard-coded dates) · CASE-driven categorisation uses logical sort order · every query carries a 3-line purpose/author/date header comment. Defence is a 20-minute live walk-through with a CareerSwerve assessor. The full brief, ERD, and connection details appear inside the lesson once enrolled.

## The scenario You have joined the Operations Analytics team at a UK domestic energy supplier. The Head of Metering needs a monthly Meter Read Snapshot for the operations review — actual-vs-estimated split, monthly consumption trend, daily-volume outliers, failed reads, and a data completeness dashboard. ## Dataset EnergyLT is a synthetic UK domestic-supply dataset hosted on the CareerSwerve Azure SQL training database. For SQL Core you work exclusively with the `EnergyLT.MeterReading` table — the highest-volume table in the schema and the right place to learn single-table discipline at scale. ## Deliverables Eight outputs (A–H) submitted as a single annotated `.sql` file: **A.** Read type breakdown (Actual / Estimated / Customer / NULL) · **B.** Monthly consumption trend for actual reads only · **C.** Consumption band distribution · **D.** Top 10 highest single-day consumption readings · **E.** Failed read audit (NULL ReadingValue by month) · **F.** Readings in the most recent 30 days · **G.** Actual vs estimated UNION comparison · **H.** Single-row data completeness dashboard. ## Acceptance criteria (summary) Same rigour as PatientLT Core — clean execution, deliberate NULL handling, sargable date filters, logical CASE ordering, report-friendly column aliases, every query commented. The defence emphasises understanding what "estimated read" means commercially and why the actual/estimated split matters for revenue recognition. Full brief and connection details appear inside the lesson once enrolled.

## The scenario You have joined the Network Performance team at a UK national parcel carrier. The Operations Director needs a weekly Shipment Volume Snapshot — status mix, weight-band distribution, service-type breakdown, SLA breach counts, heaviest active shipments, daily creation trend, in-transit vs delivered, and an unassigned-shipment audit. ## Dataset LogisticsLT is a synthetic UK parcel-carrier dataset hosted on the CareerSwerve Azure SQL training database. For SQL Core you work exclusively with the `LogisticsLT.Shipment` table — the hub entity for the rest of the schema, opened up in SQL Applied. ## Deliverables Eight outputs (A–H) submitted as a single annotated `.sql` file: **A.** Shipment volume by status · **B.** Weight band classification · **C.** Volume by service type and status (two-column GROUP BY) · **D.** SLA breach summary (DATEDIFF + CASE) · **E.** Top 10 heaviest active shipments · **F.** Daily creation trend for the last 30 days · **G.** In-transit vs delivered UNION comparison · **H.** Single-row unassigned-shipment audit. ## Acceptance criteria (summary) Same rigour as the other Core capstones — clean execution, deliberate NULL handling, sargable date filters, logical CASE ordering, business-friendly column headers. Defence focuses on the operational meaning of "SLA breach" and why two-column GROUP BY matters when service-type performance varies by status. Full brief and connection details appear inside the lesson once enrolled.