Muradamen/Integrated-Project-Maji-Ndogo-Water-Services-Part-2-

This is Part 2 of the Maji Ndogo water crisis analysis, focusing on advanced SQL techniques to uncover anomalies, identify high-risk water sources, and evaluate employee performance. The goal is to improve decision-making in water resource management for rural and urban communities. # Maji Ndogo – Part 2: Advanced SQL Analysis ## 📌 Overview This is **Part 2** of the Maji Ndogo water crisis project, focusing on **advanced SQL techniques** to detect anomalies, identify high-risk water sources, and evaluate employee performance. The ultimate goal is to improve **decision-making** in water resource management for both **rural and urban communities**. ## 🎯 Objectives * Detect Data Inconsistencies between auditor and employee water source scores. * Identify Problem Areas using location-based and quality-based water source analysis. * Evaluate Employee Performance using ranking and aggregation functions. * Investigate the Water Crisis Scope through data trends and anomalies. * Generate Actionable Insights to inform effective water management strategies. ## 📂 Dataset Description This analysis integrates multiple tables from the `md_water_services` database: | Table | Description | | ------------------- | --------------------------------------------------------- | | auditor\_report | Auditor evaluations and official water source scores. | | visits | Employee visit logs with assigned sources and timestamps. | | water\_quality | Employee-reported water quality scores. | | employee | Employee records and assignment details. | | well\_pollution | Pollution level tests for various water wells. | ## 🛠 Skills & SQL Concepts Covered * Common Table Expressions (CTEs) – for clean, modular SQL queries. * Window Functions – `RANK()`, `DENSE_RANK()`, `LAG()`, `AVG()` for ranking and trends. * Joins– `INNER JOIN`, `LEFT JOIN` for combining multiple datasets. * Anomaly Detection – filtering significant score discrepancies. * Correlated Subqueries – retrieving context-specific values per row. ## 📊 Key Analyses & Insights 1. Employee Performance Ranking Ranked employees based on the number of scoring mistakes, revealing those who require furth …