FadwaFouad/Maji-Ndogo-Sources-Analysis

* # Maji Ndogo Water Sources Analysis ## 📌 Project Overview Maji Ndogo is facing a critical water crisis, impacting thousands of residents across various provinces and towns. This project analyzes a database of **60,000 records** to uncover the root causes of water insecurity, identify which areas are suffering the most, and evaluate the specific issues plaguing different water sources (e.g., rivers, wells, shared taps). The ultimate goal of this analysis is to provide local government officials and field engineers with data-driven, actionable insights to optimize resource allocation, reduce queue waiting times, and systematically deploy infrastructure repairs where they are needed most. --- ## 🎯 Business Problems & Key Questions To effectively address the crisis, this analysis focuses on answering two major structural questions: 1. **Geographic Distribution:** Are there specific provinces or towns where certain water sources are more abundant or heavily relied upon? 2. **Targeted Interventions:** What are the specific failure points for each water source type, and how can we prioritize solutions for the most vulnerable provinces and towns? --- ## Dashboards --- --- ## 🛠️ Tech Stack & Tools * **Database Management & Querying:** MySQL Workbench (Complex joins, views, window functions, and temporary tables) * **Data Exploration & Documenting:** Jupyter Notebook * **Data Visualization & Analytics:** Microsoft Excel (Pivot Tables & Charts) --- ## 📈 Methodology & Technical Implementation ### 1. Data Consolidating & View Creation To prepare the raw data for analysis, a master view was created (`sources_analysis_view.sql`) by writing complex SQL `JOIN` statements. This linked separate infrastructure, geographical, and survey tables into a single, analysis-ready structure. ### 2. Regional Source Aggregation Using conditional logic (`CASE WHEN... THEN... ELSE`), data was aggregated in `percentage_sources_per_province.sql` and `town_aggregated_water_access.sql …