DimphoData/Charting-The-Course-For-Maji-Ndogo-s-Future

A data-driven engineering roadmap for the Maji Ndogo water crisis, using SQL forensics to identify infrastructure needs and automate a 25,000+ row project backlog. Maji Ndogo: From Analysis to Action A Data-Driven Engineering Roadmap to Solve a National Water Crisis - Project Overview - After uncovering systemic data falsification and corruption within the Maji Ndogo water department, this final phase focuses on translating forensic data into a massive infrastructure deployment plan. - By unifying disparate data sources, including location data, water source types, and pollution results, I developed an automated "Job List" for engineering teams. This roadmap ensures that President Naledi’s budget is allocated to the sites with the highest need based on verified evidence. - Technical Stack & Skills Language: SQL (MySQL) - Advanced Techniques: - Views & Joins: Unified 4+ tables into a single source of truth. - CTEs & Pivot Tables: Aggregated water access percentages at the town and province levels. - Conditional Logic: Used complex CASE statements to automate engineering work orders. - Data Integrity: Implemented CHECK constraints and OR REPLACE logic for robust database management. - The Investigation Workflow 1. Unified Data Assembly I created a combined_analysis_table View to act as the primary engine for the project. This view bridged the gap between location names and technical water quality results, which were previously stored in separate silos. 2. Regional Gap Analysis By building a pivot table using Common Table Expressions (CTEs), I identified distinct regional crises: - Sokoto: High reliance on river water (Action: Prioritize well-drilling). - Amanzi: High percentage of broken taps (Action: Prioritize infrastructure repair teams). 3. Automated Engineering Backlog I designed a logic-based engine to assign specific technical solutions to every water source in the country: - Biological Contamination: → Install UV Filters - Chemical Contamination: → Install RO Filters - High-Queue Shared Taps: → Calculated and deployed additional taps based on wait times - River Sources: → Drilled new wells - Impact & Res …