erickgathinji/Restoring-Water-Access-SQL-Part-4

Final SQL project tackling Maji Ndogo’s water crisis. Integrates prior analyses into actionable repair strategies, using data assembly, pivot analysis, and CTEs to identify provincial/town issues. Builds a Project_progress table to guide field teams with clear, data-driven implementation steps. # Maji Ndogo Water Crisis Project: From Analysis to Action This outlines the process, findings, and actionable plan derived from the analysis of water access data in Maji Ndogo. The project aims to convert raw data into informed decisions and practical solutions to improve water access for the community. ## Introduction and Project Context The project was initiated by **President Aziza Naledi** with the goal of addressing Maji Ndogo's water crisis and ensuring that data is translated into actionable knowledge. The team's prior work uncovered corruption among field workers, which President Naledi addressed, emphasizing a no-tolerance policy for self-serving actions. This initiative focuses on shaping data into meaningful views, providing essential information for decision-makers to plan budgets, identify urgent areas, and create clear job lists for engineers. ## Data Sources and Assembly The analysis draws upon several interconnected tables within the `md_water_services` database. To facilitate analysis, data from these tables was carefully joined and a consolidated view was created. ### Tables Used: * **`location`**: Contains information about the geographical location of water sources, including `province_name`, `town_name`, `location_id`, and `location_type`. * **`water_source`**: Provides details about the `type_of_water_source` and `number_of_people_served` by each source, linked by `source_id`. * **`visits`**: This central table connects `location_id` to `source_id`, and includes `visit_count` and `time_in_queue`. A crucial step in data assembly was to **filter for `visits.visit_count = 1` to eliminate duplicate records** for the same source/location. * **`well_pollution`**: Contains `results` about water quality, but **only for well-type water sources**. It is joined using a `LEFT JOIN` to ensure all water sources are included, with pollution results appearing as `NULL` for non-well sources. ### Data Assembly Steps: 1. **Initial Join**: `locati …