Water Funds Accountability

Problem Statement

Millions of people in Kenya lack access to safe drinking water. But beyond the infrastructure gap, there's another problem: funds allocated for water projects don't always reach their intended purpose. This project investigated financial irregularities in the Maji Ndogo water infrastructure dataset — looking for patterns that could indicate fraud, misallocation, or corruption in public spending.

Approach

Working with the md_water_services database in MySQL Workbench, I designed a series of SQL queries to cross-reference spending records, visit logs, and location data. The goal was to surface anomalies that wouldn't be visible in a simple report — patterns that only emerge when you look across multiple tables together. Findings were then summarised in Excel 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

The core query used a multi-table JOIN with window functions to analyse water source distribution and detect irregularities across provinces:

-- 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 JOIN query with window functions in MySQL Workbench — MySQL Workbench — JOIN query with window functions across water_source, location & visits tables

Excel pivot table summary of water source data — 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
Translating technical findings for non-technical audiences