hadyelfadaly/Maji-Ndogo-Water-Crisis-Analysis

Water crisis data analysis using MySQL — CTEs, window functions, views, and subqueries on a 25,000+ record dataset. # Maji Ndogo Water Crisis Analytics ## About the Project As I am currently advancing through the ALX Africa Data Engineering program, I developed this SQL-based analytics project to investigate and propose solutions for a simulated national water crisis in the fictional country of Maji Ndogo. This project involves a complete data lifecycle: from initial database exploration and data cleansing to complex aggregations, auditing surveyor integrity, and generating actionable infrastructure improvement plans. ## Technical Stack & Skills Demonstrated * **Database:** MySQL * **Techniques:** Common Table Expressions (CTEs), Complex `JOIN` operations, Window Functions, Data Cleansing (string manipulation, updating records), Views, and Subqueries. * **Core Competencies:** Data Auditing, Fraud Detection, Geospatial Aggregation, and Business Logic Implementation. * **`Data/`**: Contains the `md_water_services_data.sql` file. This is the raw SQL dump needed to recreate the database and reproduce this analysis locally. * **`Analysis/`**: Contains the four distinct analytical phases, represented by the SQL scripts: * **Entity Relationship Diagram (ERD):** A visual representation of the Maji Ndogo database schema, mapping the relationships between the `employee`, `location`, `visits`, and `water_source` tables. * **`1st Analysis.sql` - Database Exploration & Schema Understanding:** Initial queries to understand the architecture of the `employee`, `location`, `visits`, and `water_source` tables. This phase establishes the baseline data types and relationships. * **`2nd Analysis.sql` - Data Cleansing & Initial Aggregations:** Focuses on cleaning corrupted email data, calculating the total population served by different water sources (wells, shared taps, rivers), and understanding the macroscopic scale of the water crisis. * **`3rd Analysis.sql` - Data Auditing & Fraud Investigation:** Implements an audit pipeline comparing third-party auditor scores against internal surveyor scor …