maji ndogo sql

This project progressively challenged me and offered less guidance as I progressed, pushing me to think critically. By the end, I didn't just have a deeper understanding of SQL but also a blueprint for how to approach problems in future missions. I completed the project over the five weeks of the SQL module. In Maji Ndogo and across the world, access to clean and reliable water sources is a fundamental aspect of daily life. The data collected on water sources, water collection, and crime in Maji Ndogo provided me with a deeper understanding of the daily life of its inhabitants and the critical role water plays in their lives.

Communities rely heavily on a few primary water sources, most of which are located quite a distance from their homes. Many residents, particularly women and children, spend a significant portion of their day collecting water. This task often involves long walks under the sweltering sun, carrying heavy containers, and waiting in long lines at the water sources. The physical and time demands of this chore can be gruelling, especially for those who must balance it with work and household responsibilities.

Moreover, the reliance on these limited water sources leads to water collection points becoming crowded and sometimes even tense as people compete for access to this precious resource. Such situations can potentially lead to disputes, and in some cases, escalate into criminal activities.

In the first phase with SQL, I worked in 4 parts and then collected them all into one powerful notebook file:

Part 1: In this first part of the integrated project, I dove into Maji Ndogo's expansive database containing 60,000 records spread across various tables. As I navigated this trove of data, I used basic queries to familiarise myself with the content of each table. I also refined some data using DML.

Part2: In this second part of the integrated project, I geared up for a deep analytical dive into Maji Ndogo's water scenario. Harnessed the power of a wide range of functions, including intricate window functions, to tease out insights from the data tables.

Part3: In this third part of the integrated project, I pulled data from many different tables and apply some statistical analyses to examine the consequences of an audit report that cross-references a random sample of records.

Part 4: In this final part of the project, I finalised the data analysis using the full suite of SQL tools. I gained my final insights, I used these to classify water sources, and prepare relevant data for the engineering teams.

Then I took the project to the next level with Power BI so let’s see what I did exactly:

In the second phase with Power Bi, I worked in 4 parts and then collected them all into one powerful file:

Part 1: In this first part of the integrated project, I was introduced to updated data concerning the gender composition of queues at shared water taps in Maji Ndogo, and some new crime-related data. I created basic visuals, gradually diving into crime-related data, and some gender parity data related to water, illustrating the correlation between women queueing and being victims of crime. The primary goal is to visualise the national survey data results, with the aim of reporting them in the future.

Part 2: In this second part of the integrated project, I focused on data models. I imported tables separately, cleaned the data, and set up a working relational data model in PowerBI. I also recreated my visuals with the new data model and saw how the new model affected my visuals. My goal was to refine the visuals, customising text, colours, and fonts to make the visuals clear and simple.

Part3: In this third part of the integrated project, I finalised the national survey report. I used DAX to create measures and columns to enrich our data to ensure accurate and useful data representation on the dashboard.

Part4: In this final part of the project, I used all the skills acquired in the course to build a public dashboard. My mission was to communicate with transparency: Where did the money go? I tracked the total budget against project completion, monitored teams' performance, and compared budgeted versus actual costs to flag potential corruption, promoting transparency and accountability in addressing Maji Ndogo’s water crisis.