However, access alone does not guarantee empowerment. While more women may now own bank accounts, many still face barriers to using these services effectively, especially when it comes to accessing credit, starting businesses, and growing enterprises.

This project explores women’s financial inclusion from a data-driven perspective by analyzing global trends in women’s account ownership and female business ownership. Using publicly available data and interactive dashboards, the project seeks to understand whether improvements in financial access are translating into meaningful economic participation for women.

Problem Statement

Despite global efforts to close the gender gap in financial access, significant disparities remain across countries and regions. In many cases, women may have bank accounts but lack access to loans, credit facilities, or financial products that support entrepreneurship and wealth creation.

The key challenge addressed in this project is:

Does increased financial access for women lead to real economic empowerment and participation, particularly through business ownership?

Without answering this question, policymakers and financial institutions risk focusing only on surface-level inclusion metrics, such as account ownership, while overlooking deeper structural barriers that limit women’s economic opportunities.

Project Objective

The main objective of this project is to analyze and visualize global trends in women’s financial inclusion and economic participation using reliable, real-world data.

Specifically, the project aims to:

• Examine changes in women’s financial account ownership over time
• Identify countries with the largest improvements or declines in inclusion
• Analyze patterns in female business ownership across regions and income levels
• Compare financial access with actual economic participation
• Highlight gaps between access and empowerment
• Provide data-driven insights and recommendations for policymakers, development organizations, and financial institutions

The final output of this project is an interactive Power BI dashboard designed to help users explore trends, compare countries, and uncover actionable insights related to women’s financial inclusion and economic participation. Prior to dashboard development, Python was used for initial data exploration, cleaning, and visualization, enabling early pattern detection and validation of key indicators before translating the analysis into an interactive business intelligence tool.

Data Description

Data Used

This project uses women’s financial inclusion data covering multiple countries across several years. The dataset includes key indicators such as female and male account ownership, gender gaps in access to financial services, and women’s participation in business ownership. These indicators allow for meaningful comparisons across countries, regions, and time periods.

The dataset makes it possible to:

• Measure progress in women’s financial access over time
• Identify gender gaps between men and women
• Compare regional and country-level differences
• Examine whether financial access translates into real economic participation, such as business ownership

Because of its structured and time-based format, the dataset is highly suitable for data analysis and visualization. It supports trend analysis and enables the creation of interactive dashboards in Power BI, while Python was used for initial data exploration, cleaning, and visualization. This workflow allows the analysis to move from raw data to clear, actionable insights.

Data Source

The data used in this project was sourced from World Bank Open Data, accessed through the World Bank API and scraped using Python. This is a trusted, publicly available global dataset widely used in economic and social research.

This dataset was chosen because financial inclusion is closely linked to women’s economic empowerment. Access to financial services such as bank accounts, savings, and credit is often the first step toward independence, entrepreneurship, and long-term economic growth. Despite this, many women around the world continue to face structural and economic barriers to accessing financial services.

Time Period

• 2011 to 2024

This time range allows for long-term trend analysis and helps capture both periods of progress and decline in women’s financial inclusion across countries.

Key Variables

The dataset includes the following key variables:

• Country Name / Country Code
• Year
• Indicator Name, including: Women with financial accounts (% age 15+), Men with financial accounts (% age 15+), Firms with female ownership (%)
• Value (percentage)
• Region/Country

Data Limitations

While the dataset is comprehensive, several limitations were identified:

• Some countries had missing values for certain years
• Regional aggregates could distort country-level insights
• Business ownership data was not available for all countries

These limitations were carefully handled during data cleaning, filtering, and analysis to ensure that insights remained accurate and reliable.

Methodology

Data Preparation & Cleaning

Before analysis, the data required significant preparation to ensure accuracy.

Key steps included:

• Standardizing column names and formats
• Removing duplicate records
• Dropping rows with missing values
• Converting year values to numeric format
• Separating countries from regions to avoid misleading comparisons
• Normalizing indicator names for consistency

These steps ensured the dataset was reliable and analysis-ready.

Tools & Technologies Used

• Python (Requests, Pandas): Data scraping, cleaning and visualization
• World Bank API: Data source
• Power BI: Dashboard creation and interactive analysis
• Excel

Python was chosen for automation and flexibility, while Power BI enabled interactive storytelling and exploration.

Data Exploration & Preprocessing

During initial exploration, several patterns quickly became visible.

What I Discovered Early

• Women’s account ownership was generally increasing
• Large differences existed between countries
• Some regions masked poor-performing countries
• Business ownership values were much lower than account ownership

Findings

• Women’s financial inclusion has improved globally, with an average increase of about 9.2%
• Significant gender gaps still exist in many countries
• Progress is uneven, with some countries improving rapidly while others decline
• Female business ownership remains much lower than account ownership
• High female entrepreneurship appears in both developing and emerging economies

Insights

• Financial access does not automatically lead to empowerment
• Progress in inclusion is fragile and can reverse
• Income level alone does not determine women’s entrepreneurship
• Structural barriers like credit access and legal rights still limit women

The data clearly shows that access is only the first step.

Recommendations

Based on the analysis, the following actions are recommended:

1. Move beyond account ownership to focus on credit access and usage
2. Expand digital and mobile banking for underserved women
3. Improve legal and property rights to enable collateral-based lending
4. Increase financial literacy programs targeted at women
5. Support women entrepreneurs with SME financing and mentorship
6. Monitor countries showing declining inclusion to protect gains
7. Collect more gender-disaggregated financial data

Dashboard

The dashboard allows users to explore trends, compare countries, and understand the gap between financial access and economic participation.

Conclusion

This project shows that while women’s financial inclusion is improving globally, true empowerment remains incomplete.

Opening bank accounts is important, but it is not enough.

Real inclusion requires access to credit, supportive policies, and opportunities that allow women to fully participate in the economy. By combining data analysis with interactive dashboards, this project helps shift the conversation from how many women are included to how well they are empowered.

This post is three or four years late. Every time I tried to write or talk about it, I broke down in tears. But today, even as I sob, I have decided to write.

Every time I hear of a woman who dies as a result of femicide, I think of you. I think of how underreported femicide is. Your name never appeared in the news or on social media. I don’t even know his name, I just know that I hate him so much. One day, when I am ready, I will update this post with his name. But I don’t trust this country to deliver justice. If only I had known then what I know now, I would have done something, written, protested, anything. I just know I would have tried.

The fight against femicide will always remain personal to me because you died from it. People don’t understand: you would still be alive if that murderer hadn’t taken your life. You were so lively, so full of joy, and he stole you from us just like that.

Every December 31st, I think of you. If we were in the village for Christmas, you would have insisted we go for crossover night. You were always the one organizing where to go. We would have slept at your place, eaten if we were hungry, and talked until daybreak, catching up on each other’s lives with little sleep. It was always fun. Someone once asked me why I don’t want to go for crossover night, and when I said it was because you died, he asked if you died on crossover night. I just laughed, because he couldn’t possibly understand.

I think of you whenever I see your brother. I don’t enjoy football, but whenever Chibueze is playing, I go to watch him. The last time I went, his team won. Nne, you would have been proud. At the end of the match, I hugged him and congratulated him, thinking of you all the while.

So much has changed since you left. At our recent Umunna get-together, we launched a book that carried the names of everyone from our kindred. Two of your brothers, Akachukwu and Chiagozie, were there, they’ve grown so much. I haven’t seen Chisom since you passed, and it reminds me how different everything feels now. I thought of how your dad would have made the event more lively. I hope both of you are resting in peace.

I still struggle to visit your home. I haven’t been there since you died. I’m afraid I’ll break down. Death feels so final. What do you mean, you’re gone forever? It doesn’t make sense.

We had one thing in common: we were the only girls surrounded by brothers. I was the quiet one (not anymore, lol), and you were the social, outgoing one. I loved hanging out with you. You were like the sister I never had.

I carry guilt about our last encounter. I remember Ifechukwu calling me to say you were in town (Awka) and needed a place to stay. You were waiting at the junction. I was angry at Ife for not giving me a heads-up. If only I had known that would be the last time I saw you. I would have begged you to stay longer. Maybe we would have talked about your boyfriend/fiancé (the one that ended up killing you, crazy!). I don’t know if you realized I was upset, but I regret it deeply. Nne, I am sorry. These days, I don’t care when family or friends show up unannounced, because what if I never see them again?

I feel guilty that when I try to picture your face, it’s no longer as clear as it used to be. I fear the memories are fading. That’s why I want to do a project in your honor. Your memory must live on.

This would have been my tribute to you, though I don’t even remember writing one before now. Your death was such a shock. On the day of your burial, I had my final year exams and couldn’t attend. I came the next day instead, and I will never forget how sad your mother was, chai, the pain was unbearable to witness.

Before the burial, the family carried out an autopsy. They said the boy was in jail, but like I’ve said before, I don’t trust this country to deliver justice. God, I wish I knew his name. My anger burns at the way the family found out you had died, the way he had planned to discard you, Ada Anayo, as if you were nothing. May God continue to bless the mortuary attendant for insisting he reached out to your family members, because how else would we have known ? Oh, God!… they said time heals, but I disagree, your death is and will ALWAYS remain a great loss, but I pray God continues to give us the strength to bear this heavy burden.

I often wonder where you would have been today. You were a star, destined for greatness. In our next life, come back as my cousin again or even better, my sister.

Chidinma Adaeze Ezeliora, may your soul continue to rest in perfect peace.

I called my mummy tonight because I was worried, tense, anxious, overthinking has finally given me a headache. I have an interview tomorrow, and for some reason I just needed to hear her voice. I don’t know why I always expect her to do something magical to calm me down, but she did. She reminded me of my name and where it even came from.

My name is Godsfavour Oluebubechukwu (work of a glorious God), and according to my mum, my aunty actually got a revelation before I was born that this was supposed to be my name. And she said that this name is what will continue to follow me. Tell me why I was now crying and emotional. 😭

She reminded me of how far I’ve come, how God has already gone ahead of me, and then she told me to stop worrying and drink water, that part really cracked me up.

And to my friends, thank you for the encouragement and for helping me prep. I recently had a bad interview, my first one since I started job-hunting, and I wasn’t prepared at all. I’ll write about that later.

Right now I’m trying to book a space at a café for tomorrow’s interview because my network at home is not reliable at all, and seriously hoping I finally get the ONE chance I need to start the career I have always wanted.

So if you’re reading this, please wish me well… and say a prayer for me. 💛

I’m going to end this by quoting Psalm 1 Vs 3:

“He is like a tree planted by streams of water, yielding its fruit in season, and his leaf does not wither. In all that he does, he prospers.”

And now I’m personalizing it:

“Godsfavour Oluebubechukwu is like a tree planted by streams of water, yielding her fruit in season, and her leaf does not wither. In all that she does, she prospers.”

See you guys next time✨

But learning these days has been hard. I’m struggling to study, and the only thing I’m genuinely excited to read right now is fiction (I promised myself I’d read 50 books this year, and I’m currently on book 37). Still, I remind myself that I’m not bigger than the program, the show must go on.

I recently found a new way to learn: by teaching. I tried creating content on YouTube, but right now it’s overwhelming. With videos, I have to edit, reshoot scenes, learn so many aspects of video production… and honestly, I’m not in the right frame of mind for all that. Writing feels so much better. With writing, I can breathe. So for now, I’ll be sticking to it.

I’m learning new concepts, and as I’m writing this, I’ve released two articles. I promise to make each article better than the last. I’ll return to creating video content in 2026. My favorite thing about writing is that it allows me to research, learn, practice, and then explain things in a way I understand. In the process, I get to create something I wish I had seen on the internet.

I’m going to try to write about my year before I close my laptop next month for the holidays, because I love coming back to read these reflections a few months later. I really hope I remember everything that has happened in my career this year, because honestly, this year feels like five years in one, lol.

This article is basically just to say that I’m learning by teaching — and I’ll be writing more. Stay tuned.

Introduction

Window functions are a powerful tool in PostgreSQL that let you perform calculations across multiple rows while still keeping all your rows visible. Think of it like having a calculator that can look at other rows while working on the current row.

What Are Window Functions?

Imagine you have a spreadsheet with sales data. With regular functions, if you want to calculate totals by region, you’d group everything together and lose the individual row details. Window functions are different, they let you add calculations to each row while still being able to “peek” at other related rows.

For example, you can show each sale AND the total for that region in the same row, without losing any data.

Let’s create a simple sales table to practice with:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    employee VARCHAR(50),
    region VARCHAR(50),
    amount INT
);
INSERT INTO sales (employee, region, amount) VALUES
('Alice', 'West', 500),
('Bob', 'West', 300),
('Charlie', 'East', 700),
('Diana', 'East', 400),
('Eve', 'West', 600);

This gives us a table that looks like this:

Basic Window Function Syntax

Here’s the basic structure:

function_name() OVER (
    PARTITION BY column1
    ORDER BY column2
)

Let’s break it down:

• function_name(): The calculation you want to do (like SUM, AVG, ROW_NUMBER)
• OVER: This magic word tells PostgreSQL “this is a window function”
• PARTITION BY: Divides your data into groups (like creating separate mini-tables)
• ORDER BY: Puts rows in order within each group

Example 1: Numbering Rows with ROW_NUMBER()

Let’s give each employee a number within their region:

SELECT 
    employee,
    region,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) as row_num
FROM sales;

What’s happening here:

• We’re looking at each row of sales
• PARTITION BY region splits our data into two groups: West and East
• ORDER BY amount DESC sorts by amount from highest to lowest within each region
• ROW_NUMBER() assigns 1, 2, 3... to each row in its group

Result:

See how the numbering starts over for each region? That’s the PARTITION BY at work!

Example 2: Finding Totals with SUM()

Let’s add a column showing the total sales for each region:

SELECT 
    employee,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) as region_total
FROM sales;

What’s happening here:

• PARTITION BY region again splits data by region
• SUM(amount) adds up all amounts in that region
• The result appears on EVERY row for that region

Result:

Notice how all West employees see 1400 (500+300+600) and all East employees see 1100 (700+400). The rows aren’t collapsed, everyone gets their own row!

Example 3: Creating a Running Total

Let’s create a running total that accumulates as we go through each region:

SELECT 
    employee,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY amount) as running_total
FROM sales;

What’s happening here:

• PARTITION BY region creates separate groups
• ORDER BY amount processes rows from smallest to largest sale
• SUM(amount) adds up amounts from the start up to the current row

Result:

See how the running_total grows? Bob starts at 300, then Alice adds her 500 to get 800, then Eve adds 600 to reach 1400.

Example 4: Ranking with RANK()

Let’s rank employees by their sales within each region:

SELECT 
    employee,
    region,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) as rank
FROM sales;

What’s happening here:

• We’re ranking from highest to lowest sales (DESC means descending)
• If two people had the same amount, they’d get the same rank
• The next rank would skip a number (if two people tie for 1st, the next is 3rd)

Result:

Example 5: Comparing with Previous Row Using LAG()

Let’s see how each employee’s sales compare to the previous employee in their region:

SELECT 
    employee,
    region,
    amount,
    LAG(amount) OVER (PARTITION BY region ORDER BY amount) as previous_amount,
    amount - LAG(amount) OVER (PARTITION BY region ORDER BY amount) as difference
FROM sales;

What’s happening here:

• LAG(amount) looks at the previous row's amount
• We order by amount so “previous” means the next smaller sale
• We calculate the difference between current and previous

Result:

Bob and Diana show NULL because they’re first in their regions , there’s no previous row to look at!

Example 6: Calculating Percentages

Let’s see what percentage each sale is of their region’s total:

SELECT 
    employee,
    region,
    amount,
    ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 2) as percent_of_region
FROM sales;

What’s happening here:

• SUM(amount) OVER (PARTITION BY region) gets the region total
• We divide each amount by that total and multiply by 100
• ROUND(..., 2) rounds to 2 decimal places

Result:

Eve contributed almost 43% of West region sales!

Example 7: Finding the Top Performer in Each Region

Let’s tag the highest-selling employee in each region:

SELECT 
    employee,
    region,
    amount,
    FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) as top_performer,
    FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount DESC) as top_amount
FROM sales;

What’s happening here:

• ORDER BY amount DESC puts the highest sale first
• FIRST_VALUE(employee) grabs the employee name from that first row
• Every row in the region sees the same top performer

Result:

Example 8: Looking Ahead with LEAD()

Let’s see the next person’s sales amount (the person with the next higher sale):

SELECT 
    employee,
    region,
    amount,
    LEAD(amount) OVER (PARTITION BY region ORDER BY amount) as next_higher_amount
FROM sales;

What’s happening here:

• LEAD(amount) looks at the NEXT row's amount
• Ordered by amount, so we see who’s ahead of us in sales

Result:

Eve and Charlie are the top in their regions, so there’s no one ahead of them (NULL).

Example 9: Average Sales in Each Region

Let’s see how each employee compares to their region’s average:

SELECT 
    employee,
    region,
    amount,
    ROUND(AVG(amount) OVER (PARTITION BY region), 2) as region_avg,
    amount - ROUND(AVG(amount) OVER (PARTITION BY region), 2) as diff_from_avg
FROM sales;

What’s happening here:

• AVG(amount) OVER (PARTITION BY region) calculates average for each region
• We subtract to see if each employee is above or below average

Result:

Bob is 166.67 below the West average, while Eve is 133.33 above it!

Key Takeaways

• The Big Difference: Regular GROUP BY collapses rows. Window functions keep all rows and add calculations.
• PARTITION BY: Think of this as creating invisible groups. Calculations happen within each group separately.
• ORDER BY: Controls the order that rows are processed. This matters for running totals, LAG/LEAD, and rankings.
• No ORDER BY: If you skip ORDER BY, the window function looks at ALL rows in the partition at once (like Example 2 with SUM).

Common Mistakes to Avoid

1. Forgetting PARTITION BY: Without it, the window is the entire table
2. Using window functions in WHERE: You can’t. Use a subquery instead
3. Expecting fewer rows: Window functions don’t reduce rows , you still get one result per input row

Practice Tips

Start simple! Try these steps:

1. Write a regular SELECT to see your data
2. Add a simple window function like ROW_NUMBER()
3. Experiment with PARTITION BY to see how it divides your data
4. Try different ORDER BY options to see how results change
5. Combine window functions to create more complex analyses

Practice Questions

Use the same sales table above.
Try each question on your own first before checking any solution.

1. Add a column that shows each employee’s row number (no partition). Hint: ROW_NUMBER() OVER (ORDER BY amount)
2. Show each sale along with the total sales for the entire table. Hint: SUM(amount) OVER ()
3. Show each employee along with the average sale across the entire table. Hint: AVG(amount) OVER ()
4. Number of employees within each region from highest to lowest sale amount. Hint: Partition by region
5. Create a running total of sales within each region, ordered by amount.
6. Show each employee and the previous sale amount in the same region. Hint: LAG(amount)
7. Show each employee and the next highest sale in their region. Hint: LEAD(amount)
8. Find all employees whose sales are above their region’s average. Hint: Use a subquery or CTE because window functions can’t be in WHERE.)
9. For each region, calculate the difference between each employee’s sale and the region’s top sale. Hint: FIRST_VALUE(amount)
10. Get the top 2 employees by sales within each region. Hint: ROW_NUMBER() then filter

Window functions become intuitive with practice. Use the sales table above, modify them, and see what happens. That hands-on experience is the best teacher!

Solutions

If you have difficulty, check out my solutions here. Each solution includes the SQL query and a short breakdown.

Share Your Work

I’d love to see how you solved these! Post your solutions on social media and tag me — I’ll cheer you on and give feedback.

• Twitter/X: here
• LinkedIn: here

Feel free to share your queries, screenshots, or even what you struggled with. I’m excited to see your progress!

Introduction

Common Table Expressions (CTEs) are one of the most powerful and readable tools in SQL, especially PostgreSQL. They help you break down complex logic into smaller, easier steps.

In this guide, I will:

• Explain CTEs
• Demonstrate multi-step CTEs
• Combine CTEs with window functions (ROW_NUMBER, running totals, LAG/LEAD)
• Use CTEs to clean messy data
• Explore recursive CTEs
• Share practice questions (with solutions)

Setting Up the Tables in PostgreSQL

Before we start querying, let’s create some sample tables.
Open your PostgreSQL terminal (psql) and run:

-- Create customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50),
    referrer_id INT
);

-- Insert sample customers
INSERT INTO customers (name, city, referrer_id) VALUES
('Alice', 'Lagos', NULL),
('Bob', 'Abuja', 1),
('Chika', 'Port Harcourt', 2),
('David', 'Enugu', NULL);

-- Create orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE,
    amount NUMERIC
);

-- Insert sample orders
INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2025-01-05', 250),
(2, '2025-01-07', 120),
(1, '2025-02-10', 300),
(3, '2025-02-15', 150),
(4, '2025-03-01', 400);

Quick Explanation

• SERIAL PRIMARY KEY → auto-increments IDs for you
• VARCHAR(50) → text up to 50 characters
• referrer_id → points to another customer (used later for recursion)
• REFERENCES → makes sure customer_id exists in customers
• NUMERIC → great for money, avoids rounding issues

Data Story

• Alice placed two orders (250, 300)
• Bob placed one order (120)
• Chika placed one order (150)
• David placed one order (400)

Now we’re ready to write some SQL.

What Is a CTE?

A CTE (Common Table Expression) is a temporary result you create inside a query.

Think of it as saying:

“Let me create this mini-table for a moment… then use it to write cleaner SQL.”

Basic Structure

WITH cte_name AS (
    SELECT ...
)
SELECT *
FROM cte_name;

What “create this table for a moment” really means

• It’s not a real table
• It only exists during that one query
• The moment the query finishes → the CTE disappears

It makes your SQL cleaner, easier to read, and easier to debug.

Why Use CTEs?

CTEs help you:

• Avoid messy nested queries
• Break logic into steps
• Reuse calculations
• Name each step clearly
• Write readable SQL

It’s basically writing SQL in step-by-step English.

A Simple Example: Get All Orders Above $100

Without a CTE:

SELECT order_id, customer_id, amount
FROM orders
WHERE amount > 100;

With a CTE:

WITH high_orders AS (
    SELECT order_id, customer_id, amount
    FROM orders
    WHERE amount > 100
)
SELECT *
FROM high_orders;

Now you can build on top of high_orders without repeating filters.

Analogy

A CTE is like writing a quick note:

📝 “Here are all the orders above $100.”

You carry that note into the next step.
When the query ends → the note is thrown away.

Multi-Step Logic With Multiple CTEs

Sometimes one step isn’t enough. You want to calculate something first, then filter from that result.

Scenario

We want to:

1. Calculate total spending per customer
2. Get only customers who spent more than $500

Query

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
big_spenders AS (
    SELECT customer_id, total_spent
    FROM customer_totals
    WHERE total_spent > 500
)
SELECT *
FROM big_spenders;

Breakdown

customer_totals

• Groups orders
• Calculates total_spent

big_spenders

• Filters totals above 500

Final SELECT

• Shows the final clean result

Analogy

Like cooking:

• Chop vegetables → customer_totals
• Pick only the fresh ones → big_spenders
• Serve the dish → final SELECT

CTEs + Window Functions

Window functions let you look at rows in relation to each other. They don’t reduce rows like GROUP BY. They add extra information.

Window Functions and What They Do

• ROW_NUMBER() → numbering rows
• PARTITION BY → group rows
• ORDER BY → sort inside each group
• SUM() OVER → running totals
• LAG() / LEAD() → previous / next values

Think of them as giving each row context:

• ROW_NUMBER → “you’re 1st, 2nd, 3rd…”
• LAG → “here’s the previous row”
• LEAD → “here’s the next row”

Example 1: Number Each Customer’s Orders

WITH ordered AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date
           ) AS rn
    FROM orders
)
SELECT *
FROM ordered;

Example 2: Running Total Per Customer

WITH totals AS (
    SELECT customer_id, order_id, amount,
           SUM(amount) OVER (
             PARTITION BY customer_id
             ORDER BY order_date
           ) AS running_total
    FROM orders
)
SELECT * FROM totals;

Example 3: Previous and Next Order Amount

WITH deltas AS (
    SELECT customer_id, order_id, order_date, amount,
           LAG(amount) OVER (
             PARTITION BY customer_id ORDER BY order_date
           ) AS prev_amount,
           LEAD(amount) OVER (
             PARTITION BY customer_id ORDER BY order_date
           ) AS next_amount
    FROM orders
)
SELECT * FROM deltas;

Recursive CTEs: Referral Chains

Recursive CTEs let SQL “loop” until no more data can be added.

Example: Who Referred Whom?

WITH RECURSIVE referral_chain AS (
    -- Start with customers who have no referrer
    SELECT customer_id, name, referrer_id
    FROM customers
    WHERE referrer_id IS NULL
UNION ALL
    -- Add people referred by those already in the chain
    SELECT c.customer_id, c.name, c.referrer_id
    FROM customers c
    JOIN referral_chain rc ON c.referrer_id = rc.customer_id
)
SELECT * FROM referral_chain;

CTEs for Cleaning Data

CTEs help you clean data step-by-step without writing one giant query.

Example 1: Replace NULL amounts with 0

WITH cleaned AS (
    SELECT order_id,
           COALESCE(amount, 0) AS safe_amount
    FROM orders
)
SELECT * FROM cleaned;

Example 2: Clean Customer Names

WITH cleaned AS (
    SELECT name,
           TRIM(LOWER(name)) AS clean_name
    FROM customers
)
SELECT * FROM cleaned;

Let’s look at a real-world question:

Top 5 Customers Last Month

WITH monthly AS (
    SELECT customer_id, amount
    FROM orders
    WHERE EXTRACT(MONTH FROM order_date) = 2
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM monthly
    GROUP BY customer_id
)
SELECT *
FROM customer_totals
ORDER BY total_spent DESC
LIMIT 5;

Practice Questions

Try these before checking the solutions:

1. Calculate each customer’s total spending, then return customers who spent more than $300.
2. Find each customer’s most recent order.
3. Compute a running total of spending per customer.
4. Find the customer who spent the most in February.
5. Clean customer names by:

• making them lowercase
• removing spaces
• replacing NULL with "unknown"

Final Tips

• Start with simple queries, then wrap them in a CTE
• Use multiple CTEs to break complex logic into steps
• Learn window functions , they work beautifully with CTEs
• Use COALESCE to handle NULLs
• Use recursive CTEs for hierarchy problems

Solutions

If you have difficulty, check out my solutions here. Each solution includes the SQL query and a short breakdown.

Share Your Work

I’d love to see how you solved these! Post your solutions on social media and tag me — I’ll cheer you on and give feedback.

• Twitter/X: here
• LinkedIn: here

Feel free to share your queries, screenshots, or even what you struggled with. I’m excited to see your progress!

Overview

This SQL task builds upon my Tranzit-Ride Operations Dashboard project. It focuses on exploring and analyzing data from the drivers, trips, and riders tables using SQL. The goal was to uncover meaningful insights about ride activity patterns, driver performance, payment behavior, and overall operational efficiency.

Through a series of structured SQL queries, I identified trends that support data-driven decision-making and provide a deeper understanding of how the platform operates day to day.

Project Objective

This project aims to:

• Analyze trip operations and completion trends
• Evaluate driver performance based on ratings, vehicle conditions, and distance covered
• Examine payment patterns and rider behavior
• Provide a visual and analytical overview through a dashboard and SQL queries

Tools Used

• SQL Server — for querying and aggregating trip, driver, and rider data
• Power BI — for dashboard visualization
• Power Query - for data cleaning and preparation
• GitHub — for version control and project documentation

Dataset Description

The project uses three main tables:

• trz.trips → contains trip details such as request time, distance, status, fare, payment method, and locations.
• trz.drivers → includes driver information, ratings, vehicle details, and home zones.
• trz.riders → contains rider details, tiers, and preferred payment methods.

📊 View the Dashboard Here

SQL Analysis

To complement the dashboard visuals, I wrote SQL queries to perform deeper operational analysis.
Some key queries include:

1. Total distance covered by each driver — to identify top-performing drivers
2. Drivers with perfect ratings and lower vehicle conditions — to find service quality insights
3. Trip cancellations and payment method trends — to understand user preferences
4. Classification of vehicles based on year and condition using CASE statements
5. Combining trips, drivers, and riders tables to get a unified operational dataset

📘 View the Full SQL Queries on GitHub here

Key Insights

• Most rides were completed successfully, as seen from the trip status queries showing a high number of “completed” rides compared to “cancelled.”
• Card payments have become the most frequently used method, confirmed by the GROUP BY payment_method and ORDER BY queries showing card leading other payment types.
• Drivers with higher vehicle condition scores had higher average ratings, as identified from queries comparing vehicle_condition_score and driver_rating.
• A few drivers covered the most total distance (SUM(distance_km)), showing potential route specialization or high trip frequency among top performers.
• Trips where drivers’ home areas matched their drop-off areas had higher completion rates, as observed from the join between the drivers and trips tables.
• Vehicles with newer model years generally had better condition scores and driver ratings, confirmed by classification queries using CASE statements on vehicle_year and vehicle_condition_score.
• The COUNT() and GROUP BY queries revealed that most drivers signed up in early months, showing strong onboarding at the start of the year.
• Aggregation results from the riders table showed that the majority of riders belong to the Standard tier, with fewer upgrading to Gold or Platinum.
• The platform’s revenue and commission structure, calculated through SUM(total_fare_naira), indicated stable earnings and efficiency across completed rides.
• Overall, SQL analysis highlighted strong operational consistency, with room to optimize cancellations, weekend demand, and wallet payment adoption.

Documentation

I also created a short write-up explaining the data cleaning process, KPIs, and dashboard insights.
📄 Read the full dashboard documentation here

Conclusion

The Tranzit Ride Operations Dashboard and SQL Analysis together provide a clear operational view for ride-sharing platforms.
By combining SQL-based querying and data visualization, this project highlights how structured analysis supports better decision-making, improves driver management, and enhances rider experience.

Resources

• 📘 Full SQL Queries on GitHub
• 📊 Interactive Dashboard
• 📄 Dashboard Documentation

Introduction

Does going to university in a different country affect your mental health? A Japanese international university surveyed its students in 2018 and published a study the following year that was approved by several ethical and regulatory boards.

The study found that:

• International students have a higher risk of mental health difficulties than the general population.
• Social connectedness (belonging to a social group) and acculturative stress (stress associated with joining a new culture) are predictive of depression.

This project is part of the Data Camp Career Track: Associate Data Analyst in SQL. It applies PostgreSQL skills to analyze the dataset from the study and investigate whether the length of stay in Japan contributes to students’ mental health outcomes.

Project Description

Studying abroad can be both exciting and challenging. Cultural adjustment, social belonging, and academic pressure may impact students’ mental well-being.

In this Data Camp-guided project, SQL is applied to:

• Explore the dataset.
• Perform exploratory data analysis (EDA).
• Identify correlations between social connectedness, acculturative stress, and depression.
• Examine the effect of length of stay on student well-being.

This project contributes to building my analytical skills as part of the Associate Data Analyst in SQL track, reinforcing concepts of querying, filtering, grouping, and aggregating data to uncover insights.

Dataset Description

The dataset consists of student survey responses collected from international students.

Project Instructions

Goal: Explore how length of stay (stay) impacts average mental health diagnostic scores of international students.

Required Columns:

• stay – length of stay in Japan.
• count_int – number of international students per stay.
• average_phq – average PHQ-9 score (todep).
• average_scs – average SCS score (tosc).
• average_as – average ASISS score (toas).

Data Preprocessing

Since the project was completed on the Data Camp platform, the dataset was preloaded in a table named students. There was no need to import or clean raw data, instead, the preprocessing focused on exploring the dataset and writing SQL queries to analyze it.

Step 1: Explore the Dataset

To view the data and confirm the available columns:

SELECT * 
FROM students;

This provided an overview of the table and confirmed the relevant columns.

Step 2: SQL Query Approach

The query was designed based on the project instructions:

1. Select the stay column – to group results by length of stay.
2. Count international students (COUNT(*) AS count_int) with WHERE inter_dom = 'Inter'.
3. Aggregate averages of diagnostic scores, rounded to two decimals: todep → average_phq (PHQ-9 test for depression), tosc → average_scs (SCS test for social connectedness), toas → average_as (ASISS test for acculturative stress)
4. Group results by stay – to calculate results per length of stay.
5. Order results by stay DESC – so the longest stays appear first.

Step 3: Final SQL Query

SELECT stay, 
       COUNT(*) AS count_int,
       ROUND(AVG(todep), 2) AS average_phq, 
       ROUND(AVG(tosc), 2) AS average_scs, 
       ROUND(AVG(toas), 2) AS average_as
FROM students
WHERE inter_dom = 'Inter'
GROUP BY stay
ORDER BY stay DESC;

This query produced the required a table, showing how the length of stay impacts the average PHQ-9, SCS, and ASISS scores of international students.

Result

The query returned the following table, showing how the length of stay relates to the mental health outcomes of international students:

Findings

• Students with low social connectedness reported higher depression scores.
• Acculturative stress showed a strong positive correlation with depression.
• Length of stay influenced adaptation: longer stays were linked to lower depression scores.
• Students who accessed support services generally had better mental health outcomes.

Recommendations

• Encourage social integration programs for international students.
• Provide stress management workshops to reduce acculturative stress.
• Strengthen awareness and access to student support services.
• Pay special attention to students in their first year abroad, as they face the highest adaptation challenges.

Conclusion

This SQL project, completed as part of the DataCamp Career Track: Associate Data Analyst in SQL, reinforces the key role of data manipulation and analysis in uncovering meaningful insights.

The findings support the original study’s conclusions:

• Social connectedness and acculturative stress are strong predictors of depression.
• Length of stay impacts adaptation and mental health outcomes.

The project highlights how SQL can transform raw survey data into insights that inform student support policies and improve the academic experience of international students.

References

• Data Camp’s Associate Data Analyst in SQL track — Link
• View project here— Link

Introduction

Tranzit-Ride is a fictional ride-sharing company created to illustrate how operational data can be leveraged to evaluate performance, monitor trends, and guide strategic decision-making. This report consolidates findings from the Tranzit-Ride operations dashboard, highlighting demand patterns, financial performance, driver and rider behaviours, and trip dynamics.

By integrating insights from multiple perspectives: trips, revenue, drivers, and riders, this report identifies opportunities for business growth, efficiency improvements, and customer satisfaction. The ultimate goal is to demonstrate how data-driven strategies can strengthen both profitability and long-term sustainability in ride-sharing operations.

Background

The ride-sharing industry thrives on scale, efficiency, and customer trust. Platforms like Tranzit-Ride must balance rider convenience, driver earnings, and platform profitability while managing challenges such as seasonal demand fluctuations, cancellations, uneven earnings distribution, and low digital payment adoption.

To address these challenges, Tranzit-Ride’s operations data was analysed to uncover trends and actionable insights. The analysis focuses on four main areas:

1. Trips & Demand Patterns — trip volumes, cancellations, and peak periods.
2. Revenue & Payments — revenue sources, commission structure, and payment preferences.
3. Driver Analysis — earnings distribution, fleet composition, and completion rates.
4. Rider Analysis — rider segmentation, spending behaviour, and engagement levels.

Tools used

• SQL Server — used to query and extract raw data directly from the company’s operational database, enabling accurate analysis and reporting.
• Excel — supported data exploration, and data dictionary creation.
• Power BI — connected to SQL Server for modelling and interactive dashboard creation.
• Power BI Service → Enabled publishing and sharing dashboards with stakeholders, ensuring real-time access and collaborative insights across the organization.

The process involved:

• Data Extraction & Cleaning — I connected to the SQL Server database where the Tranzit-Ride data was stored. Using SQL queries, I pulled out the main tables (Trips, Drivers, Riders, etc). I also used the queries to check the data quality and remove fields that were not useful. After that, I created a simple data dictionary in Excel to explain what each field meant for the business. I cleaned the dataset by removing duplicates, giving columns clear names, and making sure each column had the right data type for use in Power BI.
• Data Modelling — In Power BI, I built a Star Schema model to organize the data. Fact Table: trz trips — trip details like revenue, payment method, driver, rider, and status. Dimension Tables: Date — to track time-based insights like daily, weekly, and seasonal patterns. trz drivers — information on drivers such as vehicle type, ratings, and earnings. trz riders — information on riders such as tier, spending, and trip frequency. These tables were connected using Trip ID, Driver ID, and Rider ID, making it easy to analyse across different categories.

• DAX Measures — I created a table for my custom DAX measures _Measuresin Power BI to drive key metrics, such as Total revenue, average revenue per trip, Average trips per rider, Driver Earnings, Commission %, Cancellation Rate, and Wallet Adoption %. These measures supported deeper insights and comparisons across time, segments, and performance metrics.
• Visualization and Deployment: Designed the mockup dashboards in Excel to outline KPIs and visuals before implementation.
• Built interactive dashboards in Power BI with slicers for month, day, payment method, and rider tier.
• Published the final reports to Power BI Service, enabling access to stakeholders for real-time performance monitoring.

Dashboards

Access the interactive dashboard here: https://app.powerbi.com/view?r=eyJrIjoiZTllYjExMzctNDQ1NC00ZDdhLWI1MjEtMWNiNTczMTNlNDZjIiwidCI6IjE5NmRlODRmLTZhNDctNGYxOC04Y2E2LTAzMGU3ODA3ZDY4MiJ9

Observations

• 120K total trips; 102K (85%) completed, 18K (15%) cancelled.
• Demand peaked in August (12.4K trips) and dropped sharply in September (7K).
• Highest demand on Tuesdays–Fridays (17K–19K trips); lowest on Sundays (13.8K).
• Peak ride hours are 8 AM and 6 PM, aligned with commute demand.
• Total revenue = ₦650.5M, with ₦121M commission (~18.5%).
• Avg. revenue per trip = ₦5.42K; per rider ≈ ₦81.3K.
• Revenue the highest on Fridays (₦103M), lowest on Sundays (₦71M).
• Payments: 58% Card, 35% Cash, 7% Wallet.
• 3K active drivers, avg. 48 trips each.
• Avg. driver earnings = ₦4.14K; total driver earnings = ₦497M.
• Driver ratings ≈ 5, reflecting strong satisfaction.
• A few drivers (D2824, D3828) earned >₦0.5M each, showing concentration of earnings.
• Fleet dominated by Sedans (77%), followed by Mini (12.5%) and SUV (10%).
• Driver completion rate = 85–89%.
• 8K riders: 6.5K Standard, 1.2K Gold, 200 Platinum.
• Average 15 trips per rider.
• A few top riders spent ₦0.42M–₦0.45M each, contributing disproportionately to revenue.

Key Insights

• Strong demand mid-year, followed by a post-summer decline.
• Weekends underperform compared to weekdays.
• Trip demand is commute-driven, concentrated at 8 AM and 6 PM.
• Platform revenue is volume-driven; commission rates are stable.
• September’s sharp revenue dip points to seasonal or operational inefficiencies.
• Card adoption is strong, but wallet usage remains very low.
• Driver earnings are uneven, concentrated among top performers.
• Heavy reliance on Sedans limits service diversity.
• Completion rates are good, but cancellations are still significant.
• Most riders remain in Standard tier; very few upgrade to premium tiers.
• A small group of riders accounts for a large share of revenue, signalling loyalty potential.
• Average rider engagement (15 trips) is moderate, with room for growth.

Recommendations

• Launch targeted promotions in low-demand months (Sep–Oct).
• Introduce weekend incentives (discounts, loyalty points) to improve weekend performance.
• Partner with corporate firms to secure consistent commuter bookings.
• Investigate September’s earnings dip by reviewing trip volumes and cancellation causes.
• Promote wallet adoption with bonuses, cashback, and loyalty credits.
• Explore dynamic pricing strategies during peak demand months.
• Analyse top drivers’ strategies and replicate them across the wider driver base.
• Implement driver mentorship programs to reduce earnings inequality.
• Offer bonuses to drivers with low cancellation rates.
• Expand fleet diversity with more SUVs and Minis to serve premium and budget markets.
• Run tier upgrade campaigns with perks for Gold and Platinum riders.
• Develop loyalty programs for high-spend riders.
• Launch personalized reactivation campaigns for inactive or low-frequency riders.
• Incentivize digital payments with rewards for card and wallet usage.

Conclusion

This project successfully consolidated and analysed operational data from Tranzit-Ride to uncover patterns in trips, revenue, driver activity, rider engagement, and payment behaviours. Through detailed observations, key insights, and targeted recommendations, the analysis provided a clear picture of both the strengths and challenges facing the platform.

The value of this analysis lies in its ability to transform raw operational data into actionable business strategies. By identifying seasonal demand fluctuations, underutilization of promotions, uneven revenue distribution among drivers, low wallet adoption, and the dominance of Standard-tier riders, the report highlights practical opportunities to increase efficiency, improve rider and driver satisfaction, and boost profitability.

The broader implications extend beyond Tranzit-Ride: this approach demonstrates how data analytics can guide decision-making in the ride-sharing industry. Findings such as the need for demand smoothing, fleet diversification, and loyalty programs for high-value riders are applicable to similar mobility platforms aiming to optimize growth and sustainability.

Looking ahead, this work can be extended by incorporating predictive modelling to forecast demand more accurately, segmenting riders and drivers for more personalized strategies, and tracking the impact of implemented recommendations over time. With continuous monitoring and refinement, the insights derived here can serve as a foundation for building a more resilient, customer-focused, and profitable ride-sharing platform.

Let’s stay connected

• https://linktr.ee/ezelioragodsfavour

Introduction

A crime has taken place, and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on January 15, 2018, and that it took place in SQL City.

Your task? Start by retrieving the corresponding crime scene report from the police department’s database and follow the trail of clues until the case is solved.

This SQL Murder Mystery was originally designed in SQLite, but for this project, I recreated the challenge using PostgreSQL. Documenting this journey not only showcases how SQL can be used to solve real-world-style problems but also demonstrates structured querying for investigation and insights.

Importing the database

I first created a new database in PostgreSQL called police_investigation to hold the case data:

CREATE DATABASE police_investigation;

After importing the dataset, I connected to it:

\c police_investigation;

Exploring the Database

To explore the available tables within the public schema, I ran the following query:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

Result—key tables in police_investigation

Table Names

crime_scene_report- Records of incidents and crimes

person- Personal details of people in SQL City

interview- Transcripts of interviews with suspects and witnesses

get_fit_now_member- Gym membership data

get_fit_now_check_in — Gym attendance/check-in logs

drivers_license — License details such as age, height, and plate information

income — Financial records

facebook_event_checkin — Event attendance records

These tables form the foundation of the investigation. With the schema in place, I could now begin querying the database to uncover the details of the murder case.

Step 1: Crime Scene Report

First, I retrieved the crime scene report for the murder.

SELECT * 
FROM crime_scene_report
WHERE Date = 20180115 
  AND City = 'SQL City' 
  AND crime_type = 'murder';

Result: The report mentioned two witnesses:

• One lived at the last house on Northwestern Dr.
• The other was Annabel, living on Franklin Ave.

Step 2: Identifying the Witnesses

-- Witness 1: last house on Northwestern Dr
SELECT *
FROM Person
WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC;

-- Witness 2: Annabel on Franklin Ave
SELECT *
FROM Person
WHERE name LIKE 'Annabel%'
  AND address_street_name = 'Franklin Ave';

Result:

• Witness 1 → Morty Schapiro (ID: 14887)
• Witness 2 → Annabel Miller (ID: 16371)

Step 3: Interviewing Witnesses

SELECT *
FROM interview
WHERE person_id IN (14887, 16371);

Clues:

• Morty: Saw a man with a Get Fit Now Gym gold bag (membership starts with 48Z) and a car with H42W in the plate.
• Annabel: Saw the same man at the gym on Jan 9, 2018.

Step 4: Finding Suspects at the Gym

SELECT *
FROM get_fit_now_member
WHERE id LIKE '%48Z%'
  AND membership_status = 'gold';

Result: Two suspects.

• Joe Germuska → ID: 48Z7A, person_id: 28819
• Jeremy Bowers → ID: 48Z55, person_id: 67318

Step 5: Narrowing with Gym Check-ins

SELECT *
FROM get_fit_now_check_in
WHERE check_in_date = 20180109
  AND membership_id IN ('48Z7A', '48Z55');

Result: Both suspects checked in on Jan 9, so more clues were needed.

Step 6: Matching license plates

SELECT p.name, dl.age, dl.height, dl.hair_color, dl.gender,
       dl.plate_number, dl.car_make, dl.car_model
FROM drivers_license dl
LEFT JOIN person p
  ON dl.id = p.license_id
WHERE plate_number LIKE '%H42W%';

Result: Jeremy Bowers matched the witness description.

✅ Murderer identified: Jeremy Bowers.

Step 7: The real mastermind

Finally, I interrogated Jeremy.

SELECT *
FROM interview
WHERE person_id = 67318;

Jeremy confessed:

• He was hired by a wealthy woman
• Height: 65"–67"
• Red hair, drives a Tesla Model S
• Attended the SQL Symphony Concert 3 times in Dec 2017

This trail led to the true mastermind:
🎯 Miranda Priestly.

Step 8: Identifying the Mastermind

SELECT dl.*, p.*
FROM drivers_license dl
LEFT JOIN person p ON dl.id = p.license_id
WHERE height BETWEEN 65 AND 67
  AND hair_color = 'red'
  AND gender = 'female'
  AND car_make = 'Tesla'
  AND car_model = 'Model S';

Suspects: Red Korb, Regina George, Miranda Priestly

SELECT *
FROM facebook_event_checkin
WHERE event_name = 'SQL Symphony Concert'
  AND person_id IN ('78881','90700','99716');

✅ Only Miranda Priestly attended the concert 3 times.

SELECT i.*, p.name
FROM income i
JOIN person p ON i.ssn = p.ssn
WHERE id = 99716;

Annual income → $310,000 💰

🎯 Mastermind confirmed: Miranda Priestly

Insights from the Case

• Witnesses were Morty Schapiro and Annabel Miller.
• The murderer was identified as Jeremy Bowers.
• Jeremy confessed he was hired by a wealthy woman.
• Using license, car, and event data, the mastermind was revealed: Miranda Priestly.
• Miranda Priestly’s income records confirmed her profile as the one who financed the crime.

What I learned from this project

• Practiced multi-table JOINs to combine information from different sources.
• Strengthened filtering skills using WHERE, LIKE, and ranges.
• Applied stepwise logic to gradually narrow down suspects.
• Learned the importance of cross-checking data (e.g., gym check-ins, car plates, and event attendance).
• Experienced how SQL can be used for storytelling and real-world investigations.

Conclusion

The SQL Murder Mystery turned out to be more than just a coding puzzle — it was a perfect blend of storytelling and technical problem-solving. By moving through each clue step by step, I was able to apply SQL concepts in a practical, engaging way: filtering, joining, and analysing across multiple tables to uncover the truth.

This challenge reinforced the importance of structured problem-solving in data analysis: start broad, narrow down with filters, and validate with multiple data points until you reach a solid conclusion.

In the end, what began as a lost crime scene report unfolded into a full investigation that revealed not just the murderer, but the true mastermind behind the crime.

SQL isn’t just about databases, it’s about uncovering insights, solving problems, and sometimes, even catching criminals.

👉 Try the challenge here: SQL Murder Mystery

👉 Link to solution on GitHub — https://github.com/favy-codez/SQL-Murder-Mystery-with-PostgreSQL

Case closed. 🕵️