AmanuelAnalytics/Maji-Ndogo-Water-Access-Infrastructure-Analysis

SQL data analysis & cleanup project for water access, quality, and infrastructure in Maji Ndogo # Maji Ndogo Water Access & Infrastructure Analysis ## About the Data This project is based on the `md_water_services` database, a comprehensive dataset that captures the state of water access, quality, and infrastructure across multiple towns and provinces. It includes over **60,000** unique water source records and draws from structured field visits, surveyor assessments, and citizen-reported feedback. > **Note:** This dataset is **fully synthetic and anonymized**, created for educational and analytical purposes only. It does **not** represent real individuals or locations. ### Key Tables in the Dataset - **`location`** – Geographic and administrative information (town, province, area type: Urban/Rural) - **`water_source`** – Type of water source (e.g., well, in-home tap, shared tap, river) and population served - **`water_quality`** – Surveyor-assigned and auditor-verified water quality ratings - **`visits`** – Log of site visits, including visitor identity, visit frequency, and queue duration - **`auditor_report`** – Contains citizen feedback and independently assessed water quality - **`employee`** – Field workers tasked with data collection - **`well_pollution`** – Types of pollution (e.g., chemical, biological) found in wells - **`global_water_access`** – Country-level statistics for comparative context > 📌 **Note:** Each `tap_in_home` record represents a **cluster of ~150 households**, not a single unit. --- ## Identifying and Resolving Critical Data Errors ### 🔧 Broken Table Relationships - Corrected a **many-to-one** relationship between `visits` and `water_quality` to enforce a **one-to-one** link, preserving the integrity of survey design. ### 🧪 Misclassified Water Quality Labels - **Issue:** Some sources marked as "Clean" had dangerous biological contamination. - **Fixes:** - Renamed misleading `Description` values (e.g., `"Clean Bacteria: E. coli"` → `"Bacteria: E. coli"`) - Reclassified `Results` as `"Contaminated: Biological"` where contam …