Greatrichy/maji-ndogo-water-analysis

An end-to-end SQL data analytics and engineering project addressing the Maji Ndogo water crisis. # Project Maji Ndogo: Water Crisis Analytics & Infrastructure Routing ## 📌 Executive Summary This project is an end-to-end data engineering and analytics solution designed to address a severe water crisis in the fictional country of Maji Ndogo. Using advanced SQL, I analyzed a database of over 60,000 records to audit water infrastructure, uncover a government fraud ring, and automatically generate a prioritized, mathematically-driven deployment schedule for engineering repair teams. **Tools Used:** SQL (MySQL/SQLite) **Core Skills:** Common Table Expressions (CTEs), Window Functions, Complex Joins, Views, Data Cleaning, Flow Control (`CASE` statements). ```mermaid erDiagram LOCATION ||--o{ VISITS : "hosts" LOCATION ||--o| AUDITOR_REPORT : "audited in" WATER_SOURCE ||--o{ VISITS : "recorded in" WATER_SOURCE ||--o| WELL_POLLUTION : "tested in" WATER_SOURCE ||--o{ PROJECT_PROGRESS : "targeted for" EMPLOYEE ||--o{ VISITS : "conducts" VISITS ||--|| WATER_QUALITY : "receives" LOCATION { VARCHAR location_id PK VARCHAR province_name VARCHAR town_name VARCHAR address VARCHAR location_type } WATER_SOURCE { VARCHAR source_id PK VARCHAR type_of_water_source INT number_of_people_served } EMPLOYEE { VARCHAR assigned_employee_id PK VARCHAR employee_name VARCHAR phone_number VARCHAR email VARCHAR position } VISITS { INT record_id PK VARCHAR location_id FK VARCHAR source_id FK VARCHAR assigned_employee_id FK INT time_in_queue INT visit_count } WATER_QUALITY { INT record_id FK INT subjective_quality_score } WELL_POLLUTION { VARCHAR source_id FK VARCHAR results VARCHAR biological VARCHAR pollutant } PROJECT_PROGRESS { INT Project_id PK VARCHAR source_id FK VARCHAR Source_type VARCHAR Improvement VARCHAR Source_status } ``` --- ## 🛑 The Business Problem Maji Ndogo is facing a severe water crisis affecting 27.4 million people. Citizens are forced to rely on contaminated wells, broken home infrastructure, and raw river water. Furthermore, independent auditors suspected that re …