Water Funds Accountability

Problem Statement

Maji Ndogo is a fictional region used as a simulated dataset by ExploreAI Academy, modelled on real water access challenges across Sub-Saharan Africa. The dataset draws place names from across the continent, including areas in Nigeria, Kenya, and South Africa, to represent diverse rural and urban water infrastructure scenarios.

Millions of people across Sub-Saharan Africa lack access to safe drinking water. But beyond the infrastructure gap, there is another problem: funds allocated for water projects do not always reach their intended purpose. This project investigated financial irregularities within the Maji Ndogo dataset, looking for patterns that could indicate fraud, misallocation, or corruption in public water spending.

Approach

Working with the md_water_services database in MySQL Workbench, I designed SQL queries to cross-reference spending records, visit logs, and location data. Findings were summarised in an Excel pivot table and visualised in Power BI.

Joined water_source, location, and visits tables to build a full picture per site
Used window functions to rank sources by visit frequency and flag outliers
Identified employees with statistically unusual activity patterns
Summarised findings in an Excel pivot table for stakeholder reporting
Built a Power BI dashboard to communicate patterns visually

SQL Analysis

-- Water Funds Accountability Analysis
-- Skills: JOINs, Window Functions, Aggregations
SELECT
  l.province_name,
  l.location_type,
  ws.type_of_water_source,
  COUNT(*) AS total_sources,
  SUM(ws.number_of_people_served) AS people_served,
  ROUND(AVG(v.time_in_queue), 0) AS avg_wait_time_mins,
  ROUND(
    SUM(ws.number_of_people_served) * 100.0 /
    SUM(SUM(ws.number_of_people_served))
    OVER (PARTITION BY l.province_name), 2
  ) AS pct_of_province
FROM visits v
JOIN water_source ws ON v.source_id = ws.source_id
JOIN location l ON v.location_id = l.location_id
GROUP BY l.province_name, l.location_type, ws.type_of_water_source
ORDER BY l.province_name, people_served DESC;
        

Screenshots

SQL analysis in MySQL Workbench — MySQL Workbench — JOIN query with window functions across water_source, location & visits tables

Excel pivot table for water project — Excel Pivot Table — Water source distribution summary by province and location type

Power BI accountability dashboard — Power BI Dashboard — Water funds accountability overview across Maji Ndogo provinces

Results

Identified sources with disproportionately high visit counts relative to population served
Flagged employee records with irregular audit scores across multiple sites
Summarised provincial water access patterns in an Excel pivot table
Built a Power BI dashboard making findings accessible to non-technical stakeholders
Demonstrated how SQL window functions can be used as a fraud detection tool in public sector data

Skills Demonstrated

Multi-table SQL JOINs across relational database
Window functions: SUM() OVER, PARTITION BY, ORDER BY
Anomaly detection through statistical comparison
Excel pivot tables for stakeholder reporting
Power BI dashboard design and data storytelling