Mtettey29/Maji_Ndogo_SQL

An end-to-end SQL data analytics project tackling the simulated Maji Ndogo national water crisis. Features extensive data cleaning, complex table joins, database architecture (ERD), anomaly detection, and actionable reporting. # 💧 Maji Ndogo: National Water Crisis Analytics ## Project Overview This repository contains the SQL scripts and analysis for the **Maji Ndogo Water Project**, an end-to-end data analytics initiative aimed at solving a simulated national water crisis. Working with a database of over 60,000 records, the goal was to clean the data, uncover hidden patterns, investigate discrepancies, and ultimately provide actionable insights to deploy engineering teams and fix the country's water infrastructure. ## 🛠️ Tech Stack & Tools * **Database Management:** MySQL * **Languages:** SQL (DDL, DML, DQL) * **Techniques:** Data Cleaning, Aggregations, Window Functions, Table Joins, Subqueries, CTEs, Views, and Database Normalization. ## 📂 Repository Files * **`md_water_services.sql`**: The foundational data exploration script. This file documents the initial steps taken to understand the raw data structure, beginning with isolating unique records (`SELECT DISTINCT`) in the `water_source` table to identify the various types of water infrastructure across the country. ## 🗄️ Database Architecture The analysis relies on a highly relational database structure. Key tables include: * `employee`: Details of field workers and engineers. * `water_source`: Types of sources (taps, wells, rivers, etc.) and their capacities. * `visits`: Logs of employee visits to water sources, including queue times. * `well_pollution`: Biological and chemical test results for well water. * `auditor_report`: Independent findings to verify field worker data. * `project_progress`: A tracked table built to manage the ongoing repair operations. ## 🔍 The Investigation Phases ### Phase 1: Data Exploration & Cleaning * **Objective:** Understand the raw survey data and correct initial logging errors. * **Actions:** Explored tables using basic `SELECT` queries, corrected misspelled email addresses, and used `LIKE` operators and string functions to fix poorly formatted pollution records (e.g., standardizing "Clean Bact …