DEV Community: Nelly Mogere

Need to calculate running totals or rank rows without GROUP BY headaches? Enter SQL Window Functions. Let’s break down how they work with practical, real-world examples.

Nelly Mogere — Thu, 13 Aug 2026 09:34:28 +0000

Nelly Mogere

Aug 13

SQL Window Functions: Rank, Compare, and Analyze Without Losing Rows

9 min read

SQL Window Functions: Rank, Compare, and Analyze Without Losing Rows

Nelly Mogere — Thu, 13 Aug 2026 09:20:17 +0000

A practical PostgreSQL tutorial using a safari trips dataset.

A GROUP BY query can tell us how much a rider spent. But the moment we group the data, the individual trips disappear. What if we need both on the same row?

This is the problem window functions solve. A window function performs a calculation across rows related to the current row, but unlike a regular aggregate, it does not group those rows into one output row. Each row keeps its identity while gaining information from the rows around it.

Before we start writing project queries, let us first build a clear picture of the window-function categories, the syntax they share, and the question each category answers. Once that foundation is in place, the safari trips examples will make much more sense.

The project dataset

The project uses PostgreSQL and a safari schema with three tables:

drivers stores driver names, car models, join dates, and status.
riders stores rider names, cities, and membership tiers.
trips stores trip dates, routes, distances, fares, ratings, and payment methods.

The supplied dataset contains 10 drivers, 12 riders, and 40 trips. We will use these rows to rank drivers, place totals beside trips, compare fares, calculate a rolling average, and divide riders into spend groups.

First, what makes a function a window function?

Consider a normal aggregate:

SELECT
    rider_id,
    SUM(fare) AS total_spend
FROM safari.trips
GROUP BY rider_id;

This returns one row per rider. That is useful when we only need the summary, but the individual trips are no longer part of the result.

Now add an OVER clause:

SELECT
    rider_id,
    trip_id,
    fare,
    SUM(fare) OVER (PARTITION BY rider_id) AS rider_total_spend
FROM safari.trips
ORDER BY rider_id, trip_id;

The calculation still uses SUM(fare), but OVER turns it into a window calculation. Instead of collapsing the trips, SQL returns every trip and places the rider's total beside it.

That difference is the foundation for everything that follows.

The anatomy of a window expression

A window expression generally follows this shape:

window_function(expression) OVER (
    PARTITION BY grouping_column
    ORDER BY ordering_column
    ROWS BETWEEN frame_start AND frame_end
)

Not every function needs every clause. Each part answers a separate question:

Part	Question it answers
Window function	What calculation should SQL perform?
`OVER`	Should this calculation operate as a window function?
`PARTITION BY`	Which rows belong in the same group for this calculation?
Window `ORDER BY`	In what sequence should SQL evaluate the rows?
Window frame	Which rows around the current row should the calculation include?

`OVER`

OVER marks the calculation as a window function. Without it, SUM(), COUNT(), and AVG() are regular aggregate functions.

`PARTITION BY`

PARTITION BY divides the result into independent windows. A calculation partitioned by rider_id starts again for each rider. A ranking partitioned by city starts again for each city.

If PARTITION BY is omitted, the entire result is treated as one window.

Window `ORDER BY`

The ORDER BY inside OVER defines the sequence used by the calculation. Ranking functions need it to determine position. LAG() and LEAD() need it to know which row is previous or next. An aggregate can use it to build a running calculation.

This is different from the final ORDER BY, which only controls how the returned rows are displayed.

The window frame

A frame narrows an ordered window to rows around the current row. For example:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

This frame includes the current row and the two rows before it. In our project, it creates a three-trip rolling average.

The main categories of window functions

1. Aggregate window functions

Regular aggregate functions such as SUM(), COUNT(), and AVG() can also be used as window functions.

Without a window ORDER BY, an aggregate returns the value for the full partition:

SUM(fare) OVER (PARTITION BY rider_id)

Every trip for the same rider receives that rider's total spend.

When an ORDER BY is added, the calculation follows a sequence. A frame can then control whether the function uses all earlier rows or only a selected number of nearby rows.

Aggregate window functions answer questions such as:

What is the rider's total spend beside each trip?
What is the average fare across a driver's trips?
What is the rolling average of the current fare and the previous two fares?

2. Ranking window functions

Ranking functions assign positions to ordered rows. The project uses four of them:

Function	Purpose
`ROW_NUMBER()`	Gives every row a unique sequential number.
`RANK()`	Gives ties the same rank and leaves gaps after the tied rows.
`DENSE_RANK()`	Gives ties the same rank without leaving gaps.
`NTILE(n)`	Divides ordered rows into `n` approximately equal buckets.

For example, if three rows tie at position 3:

ROW_NUMBER() still gives them different numbers.
RANK() gives all three rank 3, then the next row jumps to rank 6.
DENSE_RANK() gives all three rank 3, then the next row receives rank

The function you choose depends on the question. ROW_NUMBER() is useful when exactly one row must be selected from each group. RANK() works for leaderboards where gaps after ties are meaningful. DENSE_RANK() keeps tied positions fair without creating gaps. NTILE() creates ordered groups such as quartiles.

3. Offset or row-comparison functions

LAG() and LEAD() pull a value from another row in the ordered window:

LAG() looks backward to a previous row.
LEAD() looks forward to a following row.

These functions answer questions such as:

What was the previous trip's fare?
How much did the fare change from the previous trip?
What value comes next in the ordered sequence?

The first row returned by LAG() has no previous row, so the result is NULL. In the same way, LEAD() returns NULL at the end when no following row exists.

4. Framed window calculations

A frame is not a separate function, but it creates an important branch of window analysis. It defines how much of an ordered partition the current calculation can see.

The project uses:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Combined with AVG(fare), this calculates the average of the current trip and the previous two trips. The frame grows naturally at the start because fewer than three rows are available, then moves forward one row at a time.

With the categories clear, we can now apply them to the safari dataset.

Query 1: Compare the ranking functions

We will begin with the trips for driver 2 because repeated rider ratings make the effect of ties visible:

SELECT
    trip_id,
    rider_rating,
    ROW_NUMBER() OVER (ORDER BY rider_rating DESC) AS row_number_value,
    RANK() OVER (ORDER BY rider_rating DESC) AS rank_value,
    DENSE_RANK() OVER (ORDER BY rider_rating DESC) AS dense_rank_value
FROM safari.trips
WHERE driver_id = 2
ORDER BY rider_rating DESC, trip_id;

All three functions use the same ordered rows. The only difference is how they assign positions when ratings tie. This makes the query a useful reference before choosing a ranking function for a real task.

Query 2: Rank drivers by total revenue

A window function works on the rows produced by its query. Because the ranking needs one revenue row per driver, we first calculate driver revenue in a CTE:

WITH driver_revenue AS (
    SELECT
        driver_id,
        SUM(fare) AS total_revenue
    FROM safari.trips
    GROUP BY driver_id
)
SELECT
    d.driver_name,
    dr.total_revenue,
    RANK() OVER (ORDER BY dr.total_revenue DESC) AS revenue_rank
FROM driver_revenue AS dr
JOIN safari.drivers AS d
    ON d.driver_id = dr.driver_id
ORDER BY revenue_rank, d.driver_name;

The query has two clear stages:

Group the trips to produce one total-revenue row per driver.
Rank those driver rows from highest to lowest revenue.

There is no PARTITION BY because we want one leaderboard across all drivers.

Query 3: Keep every trip and add the rider's total spend

Now we return to the problem from the introduction. We need each trip's fare and the rider's total spend on the same row:

SELECT
    rider_id,
    trip_id,
    fare,
    SUM(fare) OVER (PARTITION BY rider_id) AS rider_total_spend
FROM safari.trips
WHERE rider_id = 2
ORDER BY trip_id;

PARTITION BY rider_id places trips from the same rider in the same calculation window. There is no window ORDER BY because we want the total for the full rider partition, not a running total.

The WHERE clause limits the displayed example to rider 2. The window result still demonstrates the key benefit: the trip rows remain separate.

Query 4: Find each driver's highest-fare trip

This task needs exactly one trip for every driver. ROW_NUMBER() is a good fit because it gives each trip a unique position inside its driver partition:

WITH ranked_trips AS (
    SELECT
        trip_id,
        driver_id,
        fare,
        trip_date,
        ROW_NUMBER() OVER (
            PARTITION BY driver_id
            ORDER BY fare DESC, trip_date, trip_id
        ) AS fare_position
    FROM safari.trips
)
SELECT
    d.driver_name,
    rt.trip_id,
    rt.fare,
    rt.trip_date
FROM ranked_trips AS rt
JOIN safari.drivers AS d
    ON d.driver_id = rt.driver_id
WHERE rt.fare_position = 1
ORDER BY rt.fare DESC, d.driver_name;

PARTITION BY driver_id restarts the numbering for each driver. Ordering by fare DESC places the highest fare first. The extra date and trip ID columns provide a consistent order if fares tie.

The window value is calculated in the CTE and filtered in the outer query. That separation is important because the result of a window function is not filtered in the same query level where it is calculated.

Query 5: Rank riders inside their city

So far, the revenue leaderboard used one window across all drivers. This query shows how PARTITION BY changes the comparison group:

WITH rider_total_spend AS (
    SELECT
        rider_id,
        SUM(fare) AS total_spend
    FROM safari.trips
    GROUP BY rider_id
)
SELECT
    r.rider_name,
    r.city,
    rts.total_spend,
    RANK() OVER (
        PARTITION BY r.city
        ORDER BY rts.total_spend DESC
    ) AS city_rank
FROM rider_total_spend AS rts
JOIN safari.riders AS r
    ON r.rider_id = rts.rider_id
ORDER BY r.city, city_rank, r.rider_name;

The CTE first calculates one total-spend row for every rider who has trips. The outer query then ranks those riders, but the ranking restarts whenever the city changes.

This is the same ranking pattern as the driver leaderboard. The difference is the partition.

Query 6: Compare each fare with the previous trip

Ranking functions care about position. Offset functions use that position to retrieve another row's value.

For driver 8, we order trips by date and use LAG() to retrieve the previous fare:

SELECT
    d.driver_name,
    t.trip_id,
    t.trip_date,
    t.fare,
    LAG(t.fare) OVER (
        PARTITION BY t.driver_id
        ORDER BY t.trip_date, t.trip_id
    ) AS previous_fare,
    t.fare - LAG(t.fare) OVER (
        PARTITION BY t.driver_id
        ORDER BY t.trip_date, t.trip_id
    ) AS fare_change
FROM safari.trips AS t
JOIN safari.drivers AS d
    ON d.driver_id = t.driver_id
WHERE t.driver_id = 8
ORDER BY t.trip_date, t.trip_id;

The first trip has no earlier row, so previous_fare and fare_change are NULL. Every later row can subtract the previous fare from the current fare.

The window ORDER BY defines what “previous” means. Without a meaningful sequence, a previous-row comparison would not answer a clear question.

Query 7: Calculate a three-trip rolling average

The next query combines an aggregate window function with an ordered frame:

SELECT
    d.driver_name,
    t.trip_id,
    t.trip_date,
    t.fare,
    ROUND(
        AVG(t.fare) OVER (
            PARTITION BY t.driver_id
            ORDER BY t.trip_date, t.trip_id
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        1
    ) AS rolling_average_3
FROM safari.trips AS t
JOIN safari.drivers AS d
    ON d.driver_id = t.driver_id
WHERE t.driver_id = 2
ORDER BY t.trip_date, t.trip_id;

The partition keeps the calculation inside the selected driver's trip history. The window order arranges the trips. The frame then tells AVG() to use only the current trip and the previous two trips.

This is different from:

AVG(fare) OVER (PARTITION BY driver_id)

That version calculates one average over the driver's full partition. The frame turns it into a moving calculation.

Query 8: Divide riders into spend quartiles

Finally, NTILE(4) assigns riders to four ordered spend buckets:

WITH rider_spend AS (
    SELECT
        rider_id,
        SUM(fare) AS total_spend
    FROM safari.trips
    GROUP BY rider_id
)
SELECT
    r.rider_name,
    rs.total_spend,
    NTILE(4) OVER (ORDER BY rs.total_spend DESC) AS spend_quartile
FROM rider_spend AS rs
JOIN safari.riders AS r
    ON r.rider_id = rs.rider_id
ORDER BY spend_quartile, rs.total_spend DESC, r.rider_name;

The CTE produces one total-spend row per rider who has taken a trip. NTILE(4) then divides those ordered rows into four approximately equal groups, with the highest spenders first.

Because this is a small dataset, the buckets should not be interpreted as exact statistical percentiles. They are ordered groups based on the available rows.

A practical way to choose a window function

When a question looks like a window-function problem, I work through it in this order:

Define the output row. Should one row represent a trip, a rider, or a driver?
Identify any grouped step. If the value being ranked is a total, calculate that total first in a CTE.
Choose the category. Do you need an aggregate, ranking, offset, or bucket calculation?
Choose the partition. Should the calculation restart for every rider, driver, or city?
Choose the order. What determines rank, previous row, or movement through the data?
Choose the frame if needed. Should the function see the full partition or only nearby rows?
Filter in an outer query. If you need the top row from each partition, calculate the window value first.
Sort the final output. Add a final ORDER BY so the result is easy to inspect.

This approach keeps the SQL tied to the question. Instead of starting with a function name, start with the output grain, comparison group, and sequence.

Final thoughts

Window functions are easier to understand when they are treated as a family of tools rather than one feature.

Aggregate window functions add totals, counts, and averages without collapsing rows.
Ranking functions assign positions and handle ties in different ways.
Offset functions compare the current row with earlier or later rows.
Frames create running and rolling calculations inside an ordered partition.

In the safari project, those branches work together to preserve trip details, rank drivers and riders, compare fares, calculate rolling averages, and build spend quartiles.

The next time GROUP BY gives you the right calculation but removes the detail you still need, look at the OVER clause. The calculation may belong beside the original rows rather than in a collapsed summary.

Write Clearer SQL: When to Use CTEs or Subqueries

Nelly Mogere — Tue, 04 Aug 2026 22:41:40 +0000

CTEs and subqueries can often produce the same result. The important difference is not what they return, but how clearly they communicate the steps of your query.

That distinction matters when a query grows beyond one filter or calculation. A compact subquery can keep simple logic close to where it is used. A Common Table Expression (CTE) can give each stage of a longer query a name, making the whole statement easier to read, test, and maintain.

This article uses PostgreSQL-style syntax, but the main ideas apply to most relational databases.

Start with the mental model

A subquery is a query placed inside another SQL statement. It is wrapped in parentheses, and its result is used by the outer query.

A CTE is a named result set defined at the beginning of a statement with WITH. It exists only while that statement runs; it is not a permanent table.

Think of the difference this way:

A subquery is an expression inside a larger sentence.
A CTE is a named paragraph that the final query can reference.

Neither is automatically better. The surrounding problem determines which structure communicates the logic more clearly.

The main types of CTEs

CTEs are usually divided into two main types based on how they behave:

Non-recursive CTE: Runs its query once and returns an intermediate result to the statement that follows. Use it to organize filters, joins, aggregations, or multi-step calculations. Most CTEs you write will be non-recursive.
Recursive CTE: Includes a recursive member that references the CTE itself. It repeatedly adds related rows until no more rows match, making it suitable for employee hierarchies, category trees, folder structures, and similar data.

You may also hear the terms standalone CTE and nested CTE. These describe structure rather than separate behavioral types:

A standalone CTE is defined once and used directly by the main SELECT, INSERT, UPDATE, or DELETE statement.
A nested or chained CTE is commonly used to describe multiple CTEs in one WITH clause where a later CTE reads from an earlier one. This creates a sequence of named steps. It is not the same as placing one WITH clause inside another, and some databases restrict where nested WITH clauses may appear.

The first CTE example later in this article is standalone and non-recursive. The multi-stage salary example is chained and non-recursive, while the organization example is recursive.

Example 1: A standalone, non-recursive CTE

When a calculation feeds several parts of a query, a CTE makes the stages visible:

-- Step 1: create one average-salary row per department.
WITH department_averages AS (
    SELECT department, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
)
-- Step 2: compare each employee with the named intermediate result.
SELECT
    e.employee_name,
    e.department,
    e.salary,
    d.average_salary
FROM employees AS e
JOIN department_averages AS d
    ON d.department = e.department
WHERE e.salary > d.average_salary;

Read it from top to bottom:

Calculate the average salary for every department.
Join those averages to the employees.
Keep employees whose salary is above their department's average.

The CTE does not merely shorten the query. Its name, department_averages, explains what the intermediate result means. That becomes more valuable when you add more aggregations, joins, or business rules.

Example 2: Chained non-recursive CTEs

You can also define multiple CTEs in one WITH clause. Each CTE should represent one understandable step, and later CTEs can build on earlier ones:

-- Step 1: summarize salary data by department.
WITH department_totals AS (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
),
-- Step 2: calculate a benchmark from the summarized rows.
average_department_total AS (
    SELECT AVG(total_salary) AS average_total
    FROM department_totals
)
-- Step 3: return departments above that benchmark.
SELECT d.department, d.total_salary
FROM department_totals AS d
CROSS JOIN average_department_total AS a
WHERE d.total_salary > a.average_total;

The second CTE consumes the result of the first, and the final query gives the complete calculation a clear direction. Still, avoid splitting a query into tiny pieces just to use more CTEs; names help only when they clarify meaningful stages.

Example 3: A recursive CTE

A recursive CTE can reference itself. This makes it useful for hierarchical data such as employee-manager relationships, categories, and folder trees.

Assume manager_id points to another row in employees:

WITH RECURSIVE organization AS (
    -- Anchor member: begin with employees who have no manager.
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: find the next level in the hierarchy.
    SELECT e.employee_id, e.employee_name, e.manager_id, o.level + 1
    FROM employees AS e
    JOIN organization AS o
        ON e.manager_id = o.employee_id
)
-- Read the completed hierarchy after recursion stops.
SELECT employee_name, manager_id, level
FROM organization
ORDER BY level, employee_id;

The first query is the anchor member: it selects the top-level manager. The second is the recursive member: it finds employees managed by rows already discovered. Recursion continues until no new rows match.

PostgreSQL uses WITH RECURSIVE. SQL Server uses WITH and supports the MAXRECURSION query hint to limit recursive iterations. Because recursion controls differ by database, check your database documentation and ensure the recursive member always moves toward a stopping condition.

The CTE examples show how named query steps work in practice. Once those patterns are clear, we can compare them with subqueries and see when a smaller, local expression is the better choice.

Now compare subqueries

Suppose an employees table contains employee_id, employee_name, department, and salary. To find employees who earn more than the company-wide average, a scalar subquery is a natural fit:

-- The outer query returns the employees we want to inspect.
SELECT employee_name, salary
FROM employees
WHERE salary > (
    -- This scalar subquery returns one value: the company average.
    SELECT AVG(salary)
    FROM employees
);

The inner query returns one value: the average salary. The outer query compares each employee's salary with that value. Because the calculation is short, used once, and directly related to the filter, giving it a separate name would add little value.

Subqueries can appear in WHERE, SELECT, FROM, and HAVING. They may return one value, one row, multiple rows, or a table-like result. The operator must match that shape: use = for one value, for example, and IN or EXISTS when working with a set of rows.

EXISTS is especially useful when you only need to know whether a related row is present. It stops being about values from the inner query; the question becomes true or false:

-- Return departments that have at least one highly paid employee.
SELECT d.department_name
FROM departments AS d
WHERE EXISTS (
    -- SELECT 1 signals that the returned columns do not matter.
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
      AND e.salary > 100000
);

The inner query is correlated through department_id. For each department, the database checks whether a matching employee exists. This is often clearer than joining tables and then removing duplicate departments.

A correlated subquery is different because it references the current row of the outer query:

-- Evaluate every employee in the outer query.
SELECT e.employee_name, e.department, e.salary
FROM employees AS e
WHERE e.salary > (
    -- Recalculate the average for the outer employee's department.
    SELECT AVG(d.salary)
    FROM employees AS d
    WHERE d.department = e.department
);

This asks whether each employee earns more than the average for their own department. It is expressive, but correlated subqueries can require repeated work. On large tables, compare the execution plan with an equivalent join or CTE instead of assuming which form is faster.

CTE or subquery? Use this checklist

Situation	Prefer
A short calculation used once	Subquery
A simple membership or existence check	Subquery with `IN` or `EXISTS`
Several nested levels are becoming hard to follow	CTE
The query has clear, sequential transformation steps	CTE
The same named result is referenced more than once	CTE, then inspect the plan
Hierarchical or graph-like traversal is required	Recursive CTE

Common mistakes to avoid

Using = with a multi-row result. A scalar comparison expects one value. If the subquery can return several rows, use an appropriate set operator such as IN, ANY, ALL, or EXISTS.

Ignoring NULL with NOT IN. If the inner result contains NULL, NOT IN can evaluate to unknown and return no rows. A correlated NOT EXISTS is often safer when expressing "no matching row."

Assuming a CTE stores data. A CTE exists only for one statement. Use a temporary table when results must survive across statements or need their own indexes.

Writing recursion without a stopping path. The recursive member must eventually stop finding rows. Cycles in hierarchical data can otherwise cause repeated work or hit a database recursion limit.

Readability is the first decision, not the last. If the subquery is understandable at a glance, keep it. If readers must count parentheses or mentally execute several nested levels, name the stages with CTEs.

Performance is database- and query-specific. A CTE is not automatically materialized, cached, or faster than a subquery. Optimizers may inline, materialize, or transform expressions differently. Use EXPLAIN or your database's execution-plan tool with realistic data before making performance claims.

The practical rule is simple: keep local logic local with a subquery, and use a CTE when naming the steps makes the query easier to reason about. Clear SQL is easier to review today and safer to change later.

SQL Intermediate Project: Build a Greenwood Academy Database with PostgreSQL

Nelly Mogere — Wed, 22 Jul 2026 16:44:47 +0000

Intermediate SQL Portfolio Project: Master Key Skills

If you already know basic SQL and want a project that feels more practical, this Greenwood Academy database is a good next step. It starts with simple tables, then moves into real database work: inserting data, updating records, filtering results, using operators, counting records, formatting dates, writing CASE WHEN logic, and using a subquery for a more advanced question.

This project is labelled as intermediate SQL because it goes beyond only creating tables and selecting rows. It shows how SQL can help organize school data and turn it into information that teachers, administrators, students, and technical teams can understand.

What this project is about

Greenwood Academy is a fictional school database. The database stores students, subjects, and exam results.

Instead of working with random examples, the project follows a story. A school needs to keep student details, know which subjects are taught, record exam marks, correct mistakes, remove cancelled results, and create simple reports.

The database has three main tables:

Table	What it stores	Example use
`students`	Student names, gender, date of birth, class, and city	Find Form 3 students from Nairobi
`subjects`	Subject names, departments, teachers, and credit hours	Find all Science subjects
`exam_results`	Student results, marks, grades, and exam dates	Find results above 70 marks

The main idea is simple: first create a clean structure, then add data, then ask useful questions.

1. Creating the schema

The project starts by creating a schema called greenwood_academy. A schema helps group related database objects together.

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

This keeps the school database objects organized in one place.

2. Creating the students table

The first table stores student information.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(1),
    date_of_birth DATE,
    class VARCHAR(10),
    city VARCHAR(50)
);

The most important column here is student_id. It is the primary key, which means every student gets a unique identifier.

For a non-technical reader, this is like giving every learner a school admission number. Even if two students have the same name, the database can still tell them apart.

3. Creating the subjects table

The second table stores the subjects offered by Greenwood Academy.

CREATE TABLE subjects (
    subject_id SERIAL PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    teacher_name VARCHAR(100),
    credits INT
);

The UNIQUE rule on subject_name prevents the same subject from being entered more than once with the exact same name.

4. Creating the exam results table

The third table connects students to subjects through exam performance.

CREATE TABLE exam_results (
    result_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    marks INT NOT NULL,
    exam_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE CASCADE
);

This is where the project becomes relational. The exam_results table does not store the full student name or full subject details. It stores student_id and subject_id, then uses foreign keys to connect back to the correct records.

That means one student can have many exam results, and one subject can appear in many exam results.

5. Altering tables after creation

A database design often changes as the project grows. In this project, I added a phone number column, renamed a subject column, removed the phone number column, and added a grade column.

ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;
ALTER TABLE students DROP COLUMN phone_number;
ALTER TABLE exam_results ADD COLUMN grade VARCHAR(1);

This part is important because real databases are not always designed perfectly on the first attempt. ALTER TABLE lets you adjust the structure while continuing to build.

6. Inserting student data

After creating the tables, I inserted sample students into the database.

INSERT INTO students (
    first_name,
    last_name,
    gender,
    date_of_birth,
    class,
    city
)
VALUES
    ('Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    ('Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
    ('Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
    ('David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi');

Sample output from the students table:

student_id	first_name	last_name	class	city
1	Amina	Wanjiku	Form 3	Nairobi
2	Brian	Ochieng	Form 4	Mombasa
3	Cynthia	Mutua	Form 3	Kisumu
4	David	Kamau	Form 4	Nairobi

7. Inserting subjects and exam results

The subjects table stores what the school teaches.

INSERT INTO subjects (
    subject_name,
    department,
    teacher_name,
    credit_hours
)
VALUES
    ('Mathematics', 'Sciences', 'Mr. Njoroge', 4),
    ('English', 'Languages', 'Ms. Adhiambo', 3),
    ('Biology', 'Sciences', 'Ms. Otieno', 4),
    ('History', 'Humanities', 'Mr. Waweru', 3);

The exam results table then records marks for specific students and subjects.

INSERT INTO exam_results (
    student_id,
    subject_id,
    marks,
    exam_date,
    grade
)
VALUES
    (1, 1, 78, '2024-03-15', 'B'),
    (1, 2, 85, '2024-03-16', 'A'),
    (2, 1, 92, '2024-03-15', 'A'),
    (2, 3, 55, '2024-03-17', 'C');

This is where the three tables start working together.

8. Updating and deleting records

In a real system, data changes. A student may move, a mark may be corrected, or an exam result may be cancelled.

-- Esther Akinyi moved from Nakuru to Nairobi
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

-- Correct marks for result_id 5 from 49 to 59
UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

-- Remove a cancelled exam result
DELETE FROM exam_results
WHERE result_id = 9;

This section shows that SQL is not only for reading data. It is also used to maintain accurate records.

9. Filtering students and subjects

Filtering is one of the most useful SQL skills. It allows you to ask focused questions.

-- Find all students in Form 4
SELECT *
FROM students
WHERE class = 'Form 4';

Expected output:

first_name	last_name	class	city
Brian	Ochieng	Form 4	Mombasa
David	Kamau	Form 4	Nairobi
Hassan	Abdi	Form 4	Mombasa

Another example finds all subjects in the Sciences department.

-- Find all subjects in the Sciences department
SELECT *
FROM subjects
WHERE department = 'Sciences';

Expected output:

subject_name	department	teacher_name
Mathematics	Sciences	Mr. Njoroge
Biology	Sciences	Ms. Otieno
Physics	Sciences	Mr. Kamande
Chemistry	Sciences	Ms. Muthoni
Computer Studies	Sciences	Mr. Oduya

10. Combining conditions with AND and OR

SQL becomes more useful when conditions are combined.

-- Find Form 3 students from Nairobi
SELECT *
FROM students
WHERE class = 'Form 3'
  AND city = 'Nairobi';

Expected output:

first_name	last_name	class	city
Amina	Wanjiku	Form 3	Nairobi
Grace	Mwangi	Form 3	Nairobi
James	Kariuki	Form 3	Nairobi

The AND keyword means both conditions must be true.

The OR keyword is used when either condition can be true.

-- Find students who are in Form 2 or Form 4
SELECT *
FROM students
WHERE class = 'Form 2'
   OR class = 'Form 4';

11. Using BETWEEN, IN, NOT IN, and LIKE

Intermediate SQL includes operators that make queries cleaner.

Operator	Meaning	Example use
`BETWEEN`	Match values inside a range	Marks from 50 to 80
`IN`	Match any value in a list	Students from Nairobi, Mombasa, or Kisumu
`NOT IN`	Exclude values in a list	Students not in Form 2 or Form 3
`LIKE`	Match a text pattern	Subjects containing Studies

-- Find exam results where marks are between 50 and 80
SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

-- Find students who live in selected cities
SELECT *
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

-- Find subjects whose name contains Studies
SELECT *
FROM subjects
WHERE subject_name LIKE '%Studies%';

These operators reduce repetition and make the question easier to read.

12. Working with string functions

String functions help format text without changing the original stored data.

-- Show city names in uppercase and lowercase
SELECT
    city,
    UPPER(city) AS upper_city,
    LOWER(city) AS lower_city
FROM students;

Expected output:

city	upper_city	lower_city
Nairobi	NAIROBI	nairobi
Mombasa	MOMBASA	mombasa
Kisumu	KISUMU	kisumu

Another useful string function is CONCAT, which joins text values together.

-- Combine subject name and teacher name
SELECT CONCAT(subject_name, ' - ', teacher_name) AS subject_teacher
FROM subjects;

Expected output:

subject_teacher
Mathematics - Mr. Njoroge
English - Ms. Adhiambo
Biology - Ms. Otieno

13. Working with dates

Dates are very important in school data because students have birth dates and exams have exam dates.

-- Extract year, month, and day from each student's date of birth
SELECT
    first_name,
    date_of_birth,
    EXTRACT(YEAR FROM date_of_birth) AS birth_year,
    EXTRACT(MONTH FROM date_of_birth) AS birth_month,
    EXTRACT(DAY FROM date_of_birth) AS birth_day
FROM students;

Expected output:

first_name	date_of_birth	birth_year	birth_month	birth_day
Amina	2008-03-12	2008	3	12
Brian	2007-07-25	2007	7	25

The project also formats exam dates into a friendlier format.

-- Format each exam date as a friendly date
SELECT
    result_id,
    exam_date,
    TO_CHAR(exam_date, 'Day, DDth Month YYYY') AS friendly_date
FROM exam_results;

14. Important gotcha: hidden spaces in TO_CHAR day names

This was one of the most important small lessons I noticed while working with dates.

When TO_CHAR(exam_date, 'Day') is used, PostgreSQL pads the day name with extra spaces so all day names line up with the longest day name, Wednesday. That means the output may look correct on the screen, but the value can contain hidden trailing spaces.

Format used	What it looks like	What may actually be returned
`TO_CHAR(exam_date, 'Day')`	`Saturday`	`'Saturday '` with hidden padding
`TO_CHAR(exam_date, 'FMDay')`	`Saturday`	`'Saturday'` without padding

This matters because hidden spaces can break conditional logic. A value that looks like Saturday may not equal the text 'Saturday' if the database actually returned 'Saturday '.

-- This can fail because 'Day' may return padded text such as 'Saturday '
SELECT
    result_id,
    TO_CHAR(exam_date, 'Day') AS exam_day,
    CASE
        WHEN TO_CHAR(exam_date, 'Day') IN ('Saturday', 'Sunday') THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM exam_results;

The safer version from this project uses FMDay. The FM modifier means fill mode, and it removes the blank padding from the formatted result.

-- Safer version: FMDay removes hidden padding from the day name
SELECT
    result_id,
    TO_CHAR(exam_date, 'FMDay') AS exam_day,
    CASE
        WHEN TO_CHAR(exam_date, 'FMDay') IN ('Saturday', 'Sunday') THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM exam_results;

Another safe option is to wrap the formatted value with TRIM().

-- Alternative fix: TRIM removes leading and trailing spaces
SELECT
    result_id,
    TRIM(TO_CHAR(exam_date, 'Day')) AS exam_day,
    CASE
        WHEN TRIM(TO_CHAR(exam_date, 'Day')) IN ('Saturday', 'Sunday') THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM exam_results;

The lesson is simple: when using TO_CHAR() for day names in conditions, use FMDay or TRIM(). Otherwise, the query may look correct but still classify weekends as weekdays because of invisible spaces.

15. Counting records

Counting helps turn raw records into summaries.

-- Count how many students are currently in Form 3
SELECT
    class,
    COUNT(student_id) AS total_students
FROM students
WHERE class = 'Form 3'
GROUP BY class;

Expected output:

class	total_students
Form 3	4

Another count checks exam results with marks of 70 or above.

-- Count exam results with marks of 70 or above
SELECT COUNT(result_id) AS total_results
FROM exam_results
WHERE marks >= 70;

This is where the database starts to behave like a reporting tool.

16. Using CASE WHEN for readable labels

CASE WHEN is useful when raw values need to become meaningful labels.

-- Label each exam result with a grade description
SELECT
    result_id,
    marks,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS result_grade
FROM exam_results;

Expected output:

result_id	marks	result_grade
1	78	Merit
2	85	Distinction
3	92	Distinction
4	55	Pass
5	59	Pass

17. Using CASE WHEN with class labels

The project also labels students as Senior or Junior based on their class.

-- Label students as Senior or Junior
SELECT
    first_name,
    last_name,
    class,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        WHEN class IN ('Form 2', 'Form 1') THEN 'Junior'
    END AS class_label
FROM students;

Expected output:

first_name	last_name	class	class_label
Amina	Wanjiku	Form 3	Senior
Brian	Ochieng	Form 4	Senior
Esther	Akinyi	Form 2	Junior

This shows how SQL can add meaning to stored values.

18. Finding upcoming birthdays with a subquery

The most advanced query in this project finds students whose birthdays are coming up in the next 30 days.

The challenge is that date_of_birth includes the original year of birth. To compare birthdays with the current date, the query first calculates each student's birthday in the current year, then filters from that calculated result.

-- Find students whose birthday is coming up in the next 30 days
SELECT
    student_id,
    first_name,
    last_name,
    date_of_birth,
    birthday_this_year - CURRENT_DATE AS days_until_birthday
FROM (
    SELECT
        *,
        (
            date_of_birth
            + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM date_of_birth))
            * INTERVAL '1 year'
        )::date AS birthday_this_year
    FROM students
) sub
WHERE birthday_this_year BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';

This is a strong SQL example because it solves a problem in two stages:

Stage	What happens
Inner query	Calculates each student's birthday in the current year
Outer query	Filters only birthdays between today and the next 30 days

The lesson here is that SQL queries can be built step by step. When a question is too complex, create the missing calculated value first, then filter from it.

What I learned from this project

This project helped me see SQL as more than a language for writing commands. It is a way to organize a real-world situation into clear tables and useful questions.

I practiced:

Creating schemas and tables
Using primary keys and foreign keys
Inserting sample data
Updating and deleting records
Filtering with WHERE, AND, and OR
Using BETWEEN, IN, NOT IN, and LIKE
Working with string and date functions
Counting records with COUNT
Grouping records with GROUP BY
Creating labels with CASE WHEN
Solving a multi-step question with a subquery

Final thoughts

Greenwood Academy is a small database project, but it follows the same thinking used in larger systems. First, you decide what information matters. Then you store it in the right tables. After that, SQL helps you ask better questions and produce answers that people can use.

That is what makes this an intermediate SQL project. It is not only about syntax. It is about using SQL to turn raw data into structured, readable, and meaningful information.

SQL Beginner Project: Build a Real-World Supermarket Database with PostgreSQL

Nelly Mogere — Sat, 18 Jul 2026 23:10:22 +0000

SQL Beginner Project: Build a Real-World Supermarket Database with PostgreSQL

If you are learning SQL and want a project that feels practical, this Sunrise Supermarket database is a great place to start. Instead of only reading theory, this project shows how to design tables, insert real sample data, clean and update records, filter business data, summarize results, and connect tables with joins.

In this article, I will walk through the SQL concepts I practiced while building a small supermarket database from scratch.

What is SQL?

SQL stands for Structured Query Language. It is the language used to communicate with relational databases. With SQL, you can create database structures, store data, update records, delete records, and ask questions about the data using queries.

SQL is widely used in software development, data analysis, backend engineering, business intelligence, and reporting. For example, a supermarket can use SQL to manage customers, products, orders, stock levels, and sales reports.

SQL Areas Covered in This Project

This project covers the following beginner-friendly SQL topics:

Database creation and schema selection
Creating tables with primary keys and foreign keys
Using constraints such as UNIQUE, CHECK, and DEFAULT
Inserting data into related tables
Altering table structure with ALTER TABLE
Updating and deleting records
Filtering data with WHERE, IN, BETWEEN, and LIKE
Sorting and limiting results with ORDER BY and LIMIT
Grouping data with GROUP BY and HAVING
Joining multiple tables with INNER JOIN and LEFT JOIN

Project Scenario: Sunrise Supermarket

Sunrise Supermarket wants to move its records into a proper database. The database needs four connected tables:

customers
products
orders
order_items

These tables help answer questions like:

Which customers placed orders?
Which products cost more than a certain amount?
Which orders are still pending?
What products were ordered and in what quantity?
How can customer, order, and product data be joined together?

1. Creating the Database Schema

A schema helps organize database objects such as tables. In this project, I created a schema called sunrise and set it as the active search path.

-- Create the schema for the supermarket database
CREATE SCHEMA sunrise;

-- Use the sunrise schema for the rest of the project
SET search_path TO sunrise;

2. Creating the Customers Table

The customers table stores customer details. Each customer has a unique customer_id, and the email and phone number should not be repeated.

-- Create the customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(50),
    email VARCHAR(50) UNIQUE,
    phone_number VARCHAR(50),
    county VARCHAR(50)
);

-- Add a unique constraint to phone_number
ALTER TABLE customers
    ALTER COLUMN phone_number TYPE VARCHAR(50),
    ADD CONSTRAINT unique_phone UNIQUE (phone_number);

3. Inserting Customers

After creating the table, I inserted four customer records.

-- Insert customer records
INSERT INTO customers (full_name, email, phone_number, county)
VALUES
    ('Grace Wambui', 'grace.wambui@gmail.com', '0711223344', 'Nairobi'),
    ('Kevin Mutiso', 'kevin.mutiso@gmail.com', '0722334455', 'Nakuru'),
    ('Faith Chebet', 'faith.chebte@gmail.com', '0733445566', 'Eldoret'),
    ('Ibrahim Noor', 'ibrahim.noor@gmail.com', '0744556677', 'Mombasa');

-- View all customers
SELECT * FROM customers;

Expected output:

customer_id	full_name	email	phone_number	county
1	Grace Wambui	grace.wambui@gmail.com	0711223344	Nairobi
2	Kevin Mutiso	kevin.mutiso@gmail.com	0722334455	Nakuru
3	Faith Chebet	faith.chebte@gmail.com	0733445566	Eldoret
4	Ibrahim Noor	ibrahim.noor@gmail.com	0744556677	Mombasa

4. Creating the Products Table

The products table stores supermarket products. The unit_price must be greater than zero, and stock starts at 0 if no value is provided.

-- Create the products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(50),
    unit_price NUMERIC(10,2) CHECK (unit_price > 0),
    stock INT DEFAULT 0
);

5. Inserting Products

-- Insert product records
INSERT INTO products (product_name, category, unit_price, stock)
VALUES
    ('Maize Flour 2kg', 'Groceries', 180, 50),
    ('Cooking Oil 1L', 'Groceries', 320, 30),
    ('Bathing Soap', 'Toiletries', 85, 100),
    ('Notebook A4', 'Stationery', 60, 200);

-- View all products
SELECT * FROM products;

Expected output:

product_id	product_name	category	unit_price	stock
1	Maize Flour 2kg	Groceries	180.00	50
2	Cooking Oil 1L	Groceries	320.00	30
3	Bathing Soap	Toiletries	85.00	100
4	Notebook A4	Stationery	60.00	200

6. Creating Orders and Order Items

The orders table connects each order to a customer. The order_items table connects each order to the products bought.

-- Create the orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(50) DEFAULT 'Pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

-- Create the order_items table
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT CHECK (quantity > 0),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

7. Inserting Orders and Order Items

Because orders references customers, customers must already exist before orders are inserted. Because order_items references both orders and products, both tables must already contain data.

-- Insert order records
INSERT INTO orders (customer_id, order_date, status)
VALUES
    (1, '2024-03-01', 'Delivered'),
    (2, '2024-03-02', 'Pending'),
    (1, '2024-03-03', 'Delivered'),
    (3, '2024-03-04', 'Cancelled');

-- Insert order item records
INSERT INTO order_items (order_id, product_id, quantity)
VALUES
    (1, 1, 2),
    (1, 3, 1),
    (2, 2, 1),
    (3, 4, 5);

8. Altering Table Structure

After creating the first version of the tables, I made three structure changes:

Renamed stock to stock_quantity
Added loyalty_points to customers
Increased product_name to VARCHAR(150)

-- Rename stock column to stock_quantity
ALTER TABLE products RENAME COLUMN stock TO stock_quantity;

-- Add loyalty_points to customers with a default value of 0
ALTER TABLE customers ADD COLUMN loyalty_points INT DEFAULT 0;

-- Allow longer product names
ALTER TABLE products ALTER COLUMN product_name TYPE VARCHAR(150);

9. Updating and Deleting Data

The project also required changing an order status and deleting a cancelled order.

-- Update order_id 2 so its status becomes Delivered
UPDATE orders
SET status = 'Delivered'
WHERE order_id = 2;

-- Delete the cancelled order
DELETE FROM orders
WHERE order_id = 4;

10. Filtering Products and Customers

Filtering is one of the most common SQL skills. Here are examples using comparison operators, IN, BETWEEN, and LIKE.

Products priced above 100

-- Show every product priced above 100
SELECT product_name, unit_price
FROM products
WHERE unit_price > 100;

Expected output:

product_name	unit_price
Maize Flour 2kg	180.00
Cooking Oil 1L	320.00

Customers not based in Nairobi

-- Show every customer not based in Nairobi
SELECT full_name, county
FROM customers
WHERE county <> 'Nairobi';

Expected output:

full_name	county
Kevin Mutiso	Nakuru
Faith Chebet	Eldoret
Ibrahim Noor	Mombasa

Products priced between 60 and 200

-- Show products priced between 60 and 200, inclusive
SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 60 AND 200
ORDER BY unit_price;

Expected output:

product_name	unit_price
Notebook A4	60.00
Bathing Soap	85.00
Maize Flour 2kg	180.00

Customers in selected counties

-- Show customers who live in Nairobi, Nakuru, or Mombasa
SELECT full_name, county
FROM customers
WHERE county IN ('Nairobi', 'Nakuru', 'Mombasa');

Expected output:

full_name	county
Grace Wambui	Nairobi
Kevin Mutiso	Nakuru
Ibrahim Noor	Mombasa

Products with Oil in the name

-- Show products whose name contains Oil
SELECT product_name
FROM products
WHERE product_name LIKE '%Oil%';

Expected output:

product_name
Cooking Oil 1L

11. Sorting and Limiting Results

Sorting helps organize query results, while LIMIT controls how many rows are returned.

-- Show orders that are still Pending, sorted by earliest order_date first
SELECT status, order_date
FROM orders
WHERE status = 'Pending'
ORDER BY order_date ASC;

Expected output after updating order 2 to Delivered:

status	order_date

-- Show the two most expensive products
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 2;

Expected output:

product_name	unit_price
Cooking Oil 1L	320.00
Maize Flour 2kg	180.00

12. Grouping and Aggregates

Aggregate functions summarize data. In this project, I used grouping to count how many orders each customer placed.

-- Count how many orders each customer has placed
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;

Expected output after deleting order 4:

customer_id	total_orders
1	2
2	1

-- Show only customers who placed more than one order
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

Expected output:

customer_id	total_orders
1	2

13. Joining Tables

Joins are used to combine related information from multiple tables.

Inner join customers with orders

-- Show each customer's name next to their order_id and status
SELECT c.full_name, o.order_id, o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Expected output:

full_name	order_id	status
Grace Wambui	1	Delivered
Kevin Mutiso	2	Delivered
Grace Wambui	3	Delivered

Left join orders with order items

-- Show every order with its order items
SELECT *
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id;

Join order items with products

-- Show each order item with the product name and quantity
SELECT oi.order_item_id, p.product_name, oi.quantity
FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.product_id;

Expected output:

order_item_id	product_name	quantity
1	Maize Flour 2kg	2
2	Bathing Soap	1
3	Cooking Oil 1L	1
4	Notebook A4	5

Join all four tables

This query combines customers, orders, order items, and products to show who ordered what.

-- Join all four tables to show one row per item ordered
SELECT c.full_name, o.order_id, p.product_name, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

Expected output:

full_name	order_id	product_name	quantity
Grace Wambui	1	Maize Flour 2kg	2
Grace Wambui	1	Bathing Soap	1
Kevin Mutiso	2	Cooking Oil 1L	1
Grace Wambui	3	Notebook A4	5

14. Total Quantity Ordered Per Product

The final challenge was to group the joined data and calculate total quantity ordered per product.

-- Show total quantity ordered per product across all customers
SELECT p.product_name, SUM(oi.quantity) AS total_quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name;

Expected output:

product_name	total_quantity
Maize Flour 2kg	2
Bathing Soap	1
Cooking Oil 1L	1
Notebook A4	5

Key Lessons from This SQL Project

This project helped me practice how relational databases work in a real-world example. The biggest lessons were:

Tables should be created in the correct order when foreign keys are involved.
Constraints help protect data quality.
ALTER TABLE is useful when database requirements change.
Filtering helps answer specific business questions.
Aggregates and grouping turn raw records into summaries.
Joins are powerful because they connect data from multiple related tables.

Final Thoughts

Building the Sunrise Supermarket database was a helpful beginner SQL project because it covered both database design and practical querying. By the end, I had practiced creating tables, inserting data, updating records, filtering results, summarizing data, and joining tables together.

If you are learning SQL, I recommend building a small project like this because it helps you understand how SQL is used in real business situations.

Analysing 8 Years of Femicide in Kenya with Excel - A Data Story

Nelly Mogere — Sat, 04 Jul 2026 01:48:46 +0000

Content Warning: This article discusses femicide, the murder of women and girls. The data covers real cases of gender-based violence. If this is a sensitive topic for you, please take care.

Femicide isn't just a statistic. Every row in the dataset I worked with was someone's daughter, sister, wife, or friend.
When I came across the Femicide in Kenya (2016-2023) dataset on Kaggle, I knew I wanted to do more than scroll through it. I wanted to let the data speak, clearly and loudly. So I built a full analysis pipeline in Excel: cleaning, enriching, analysing, and visualising 507 documented cases over eight years.

This is the story of how I did it, what I found, and why it matters.

Where the Data Came From

The dataset lives on Kaggle and was compiled from multiple verified public sources:

Investigative journalism and news media
Activist platforms and gender-based violence tracking organisations
Social media reports and community alerts

Each row represents one unique femicide case. The columns cover:

Field	What it tells us
Date	When it was reported
Victim Name / Age	Who was targeted
County / Town	Where it happened
Suspect Relationship	Who did it
Type of Femicide	Intimate or Non-intimate
Mode of Killing	How it was done
Murder Scene	Where specifically
Case / Court Status	What happened legally

Before I could analyse any of this, though, the data needed serious cleaning.

Cleaning the Data in Excel Power Query

I loaded the raw file into Excel's Power Query Editor, my go-to for structured, repeatable data transformations. Here's the exact cleaning process I followed, step by step.

Step 1: Remove Duplicates

The first thing I always do. Removed all duplicate rows upfront so every case was counted once and only once.

Step 2: Standardise the Author Name Column

The author names had inconsistent casing, some all caps, some all lowercase. I applied the PROPER function to fix this, so JOHN DOE and john doe both become John Doe.

Step 3: Clean the Medium (News Source) Column

Replaced all null and empty values with "Not Provided".
Removed the (Kenya) suffix that was appended to source names. Since every entry is already Kenya-specific, this was just visual clutter making filters messy.

Step 4: Fix the Type of Murder Column

This one was subtle but important. The column had both femicide and Femicide as separate values - Excel treats them as distinct, which would split my pivot counts in two. I standardised everything to Femicide.

Step 5: Clean the Victim Name Column

Some entries had "unnamed" as the victim's name, others were blank. Both were replaced with "Not Provided" for consistency.

Step 6: Fix the Suspect Relationship Column

Two entries had trailing noise:

Stranger had "/Unknown relationship" appended - redundant. Removed it.
Friend had "/known to the victim" appended - also redundant. Removed it.

These kinds of notes make sense in a notes field, not in a category column you'll filter on.

Step 7: Fill Remaining Nulls Across Text Columns

A blanket step - replaced all remaining null and empty cells in text columns with "Not Provided". This ensures no blank filter entries appear in pivot slicers.

Step 8: Apply PROPER to More Columns

Repeated the PROPER text function across other columns (county names, relationship types, etc.) to prevent capitalisation variants from appearing as duplicate filter options.

Data Enrichment - Adding a Region Column

Once the data was clean, I needed to go one level higher than county for geographic analysis. Kenya has 47 counties - grouping them into the 8 administrative regions would give cleaner, more readable charts.

I built a separate Mapping sheet with each county mapped to its region, then used this XLOOKUP to create a new Region column:

=XLOOKUP(U2, Mapping!$A$2:$A$48, Mapping!$B$2:$B$48, "Unknown County")

This had a bonus effect: it doubled as a spell-checker for county names. Any misspelled county would return "Unknown County" - which flagged it for correction in the source data. A clean, self-validating approach.

Building the Dashboard

With clean, enriched data, I built Pivot Tables for every question I wanted to answer, then assembled everything into a single interactive Excel dashboard with slicers for filtering by county, region, murder scene, relationship type, and more.

Here's what the final dashboard looked like:

The Numbers at a Glance

KPI	Value
Total Cases	507
Most Affected County	Nairobi
Counties Affected	45 out of 47
Intimate Partner Cases	83%
Most Vulnerable Age Range	26-36 years
Minor Victims	20
Most Common Method	Stabbing
Most Common Scene	Home

What the Data Revealed

1. The Home Is the Most Dangerous Place

This was the finding that hit hardest. The majority of femicide victims were killed at home, not on the streets, not in unknown locations, at home.

We talk about home as a place of safety. The data dismantles that assumption.

2. 83% of Cases Involved Intimate Partners

Boyfriends. Husbands. Ex-partners.

8 in 10 femicide cases in this dataset were committed by someone the victim was in, or had been in, a relationship with. Intimate partner violence isn't just a contributor to femicide in Kenya. It is, overwhelmingly, the cause.

3. The Most Targeted Age Group Is 26-36

These are women in the prime of their adult lives, building careers, raising families, living independently. The data shows they are the most at-risk group.

Twenty victims were minors. Twenty children.

4. Stabbing and Strangulation Dominate

The two most recorded methods of killing were stabbing and strangulation. Both require physical proximity. This is not random or opportunistic violence - it is violence by someone close to the victim, in spaces familiar to her.

5. This Is a National Crisis, Not a Nairobi Story

Yes, Nairobi had the highest case count. But 45 of Kenya's 47 counties recorded at least one femicide case in this dataset. The geographic spread is a clear signal: this is a nationwide systemic problem.

6. The Justice System Is Failing These Women

When I looked at the court verdict breakdown, the majority of cases never reached a guilty verdict. Many were ongoing. Some suspects were never brought to trial at all. The impunity is visible in the data.

7. Arguments Over Food

I'll be honest, this stopped me in my tracks. Going through case descriptions, several murders were triggered by domestic arguments. One recurring trigger: food.

Arguments over food. Women losing their lives over arguments about food.

That's not just a data point. That's a reflection of what happens when control, anger, and access to violence exist in the same home.

Why I Built This

I'm a data analyst. My tools are spreadsheets, formulas, and charts. But tools don't choose what they illuminate, we do.

I chose to work on this dataset because the story it tells is important, and because too often data on gender-based violence gets buried in PDFs or left uncleaned on data platforms. I wanted to take it out of the raw and make it readable, visual, and undeniable.

If you're a researcher, journalist, policymaker, or just someone who cares, I hope this analysis gives you something useful.

Tools I Used

Microsoft Excel - Power Query for cleaning, XLOOKUP for enrichment, Pivot Tables for aggregation, Charts for visualisation, Dashboard for presentation

That's it. No Python. No SQL. No Power BI. Just Excel used thoughtfully and thoroughly.

Access the Files

The full project, including the raw dataset, cleaned file, and dashboard, is on GitHub:

(https://github.com/pinkleather221/kenya-femicide-analysis.git)

Final Note

Behind every row in this dataset is a name. A face. A life that was ended by violence that could have been prevented.

If this analysis can contribute, even a small amount, to better policy, stronger advocacy, or clearer public understanding of the femicide crisis in Kenya, then it was worth building.

If you found this useful or have questions about the methodology, drop a comment below. And if you're working on similar social impact data projects, I'd love to connect.

From Dirty Data to Business Intelligence: Building an End-to-End Power BI Analytics Solution with PostgreSQL, Power Query, DAX, and Star Schema

Nelly Mogere — Fri, 03 Jul 2026 12:44:39 +0000

How I transformed a messy education dataset into an interactive Business Intelligence solution using PostgreSQL, Power Query, Data Modeling, DAX, and Power BI.

Introduction

Every dashboard tells a story.

But before the charts, KPIs, and colorful visuals come into existence, there's usually something much less exciting a spreadsheet full of inconsistent, duplicated, incomplete, and poorly structured data.
That was exactly the challenge my team received during our Power BI training.
Instead of being handed a perfectly structured dataset ready for visualization, we were given what many data professionals encounter in real organizations: a single flat dataset filled with inconsistencies that needed to be transformed into a reliable source of business intelligence.
Our assignment wasn't simply to create attractive charts.
Our assignment wasn't simply to create attractive charts.

We were expected to complete the entire Business Intelligence workflow, including:

Extracting data from a PostgreSQL database
Establishing a secure database connection using SSL certificates
Cleaning messy data using Power Query
Designing a dimensional data model
Creating relationships between tables
Writing DAX measures
Building interactive dashboards
Producing meaningful business insights

Although this was an educational project, the workflow closely mirrors what Business Intelligence developers and Data Analysts do every day.

In this article, I'll walk through that journey from start to finish.

Understanding the Business Problem

The dataset represented an education management system containing information about:

Students
Schools
Teachers
Subjects
Academic performance
Payments
Locations

At first glance, everything appeared to exist inside one large table. While this may seem convenient, storing everything in a single table quickly creates problems.

Some of the issues included:

Duplicate information
Repeated school names and teacher details
Inconsistent county names and spelling
Mixed capitalization
Missing values
Incorrect data types and invalid numerical values

These are exactly the kinds of data quality issues that reduce trust in business reports. Our brief required us to clean the data, normalize it into logical tables, build relationships, and answer business questions through an interactive Power BI dashboard.

Why We Used PostgreSQL Instead of Importing a CSV

Many Power BI tutorials begin by importing an Excel or CSV file. While this works for demonstrations, most organizations do not store operational data inside spreadsheets.

Instead, business data is typically stored inside relational databases such as PostgreSQL, SQL Server, MySQL, or Oracle.

For this project, our dataset was hosted inside a PostgreSQL database, allowing us to simulate a real production environment where Power BI retrieves data directly from a database rather than from manually exported files.

This approach offered several advantages:

A centralized source of truth
Better data integrity
Easier scalability
Simplified data refreshes
A more realistic enterprise workflow

Configuring PostgreSQL for Secure Connections

One of the most interesting parts of this project happened before opening Power BI.

To establish a secure connection to PostgreSQL, I first had to install and configure the required SSL certificates. Many cloud-hosted PostgreSQL instances enforce encrypted connections to protect data while it travels across the network. Without the correct certificates, Power BI cannot establish a trusted connection.

The process involved:

Installing the PostgreSQL SSL certificate
Configuring the database connection
Verifying encrypted communication
Testing the connection before importing data

Although this step often receives little attention in beginner tutorials, it reflects what happens in production systems where database security is a priority.

Key takeaway: Understanding secure connectivity is just as important as understanding visualization, because no dashboard can exist without reliable access to trusted data.

Connecting PostgreSQL to Power BI

With the secure connection established, I opened Power BI Desktop and selected Get Data > PostgreSQL Database.

After providing the server name, database name, and authentication credentials, Power BI connected directly to PostgreSQL and displayed the available tables through the Navigator window.

Rather than importing every available object, I selected only the tables required for analysis. This is a small but important optimization, because importing unnecessary tables increases model complexity and refresh times.

First Look at the Dataset

The initial inspection confirmed what the assignment description had warned about. The dataset contained numerous quality issues, including duplicate records, null values, incorrect spellings, mixed casing, inconsistent location names, invalid numerical values, and poorly formatted dates.

Important lesson: At this point, jumping directly into building charts would have produced misleading results. A dashboard is only as reliable as the data behind it.

For this reason, data preparation became the next priority.

Cleaning the Data with Power Query

Power Query is one of Power BI's most powerful features. Instead of modifying the source database, Power Query allows analysts to transform incoming data while keeping the original source untouched. This creates a repeatable Extract, Transform, and Load (ETL) process that can be refreshed whenever new data becomes available.

Our cleaning process involved several key transformations:

Standardizing Text

Some records used uppercase values, others used lowercase, and some contained abbreviations or spelling mistakes. For example, a county could appear in several different forms, causing Power BI to treat them as different locations. We standardized these values to ensure consistency across the entire dataset.

Correcting Data Types

Columns representing numbers had occasionally been interpreted as text, and date columns contained inconsistent formats. Power Query made it possible to convert each column into its correct data type before analysis.

Why this matters: Proper data types are essential because DAX calculations depend on them. A number stored as text cannot be summed or averaged.

Handling Missing Values

Several fields contained null values. Rather than blindly deleting records, we evaluated each case individually. Depending on the context, we either replaced missing values, kept them for transparency, or excluded unusable records where appropriate. Documenting these decisions is an important part of responsible data preparation because every transformation influences the final analysis.

Removing Duplicates

Repeated records inflate KPIs and distort business insights. Duplicate students, schools, or teachers could easily lead to incorrect counts. Power Query enabled us to identify and remove duplicate records while preserving unique entities.

Renaming Columns

Clear naming conventions improve model readability. Instead of cryptic or inconsistent column names, we renamed fields using descriptive, business-friendly names. This makes DAX formulas easier to understand and simplifies collaboration.

Creating a Repeatable Transformation Pipeline

One of Power Query's greatest strengths is that every transformation becomes an Applied Step. Instead of manually repeating the cleaning process each time new data arrives, Power BI simply replays the transformation steps automatically during refresh. This makes the cleaning process reproducible, auditable, and significantly less error-prone.

Why We Did Not Keep Everything in One Flat Table

Once the data was clean, an important question emerged: should everything remain inside one giant table?

Technically, it could. Practically, it should not.

Flat tables work for small datasets, but as projects grow they become difficult to maintain. Repeated information wastes storage, complicates updates, and slows analytical queries. Instead of keeping one massive table, we normalized the data into a dimensional model that better represents how business entities relate to one another.

This decision laid the foundation for the next and arguably most important phase of the project: building a Star Schema.

Building a Scalable Data Model with a Star Schema

At this point, our data had been cleaned, standardized, and prepared for analysis. However, one major challenge still remained: the dataset still represented an operational system rather than an analytical one.

Operational databases are designed to store information efficiently. Business Intelligence systems are designed to analyze information efficiently. Those are two very different objectives.

If we had continued using one massive flat table, we would have encountered several problems:

The same school information repeated hundreds of times
Teacher information duplicated for every student
Location information stored repeatedly
Larger file sizes and slower report performance
More complex DAX calculations
Higher maintenance costs whenever data changed

Instead, we redesigned the model using one of the most widely adopted dimensional modeling techniques in Business Intelligence: the Star Schema.

Why Star Schema?

Before building the model, we briefly considered the three most common approaches:

Flat Table: Stores everything in one place. While it appears simple at first, it becomes increasingly difficult to maintain because every entity is repeated multiple times. Imagine a school with 800 students. The school's name would appear 800 separate times in the table.

Snowflake Schema: Introduces additional normalization by breaking hierarchical data into separate tables (Country > Region > County > Sub-county). This reduces redundancy further but also increases relationship complexity. For our assignment, this introduced unnecessary overhead.

Star Schema: Strikes an excellent balance between simplicity, performance, and scalability. The design consists of one central Fact Table surrounded by multiple Dimension Tables. Microsoft also recommends Star Schema as the preferred modeling approach for Power BI because it simplifies filtering, improves query performance, and makes DAX calculations easier to write.

Designing the Fact Table

The fact table became the heart of our analytical model. Unlike dimension tables, which describe entities, the fact table stores measurable events such as:

Student scores
Fee payments
Attendance records
Performance metrics

Every row in a fact table represents an event that can be analyzed. For example:

Notice that the fact table references other entities rather than storing all their descriptive information repeatedly. Instead of storing the teacher's county, school address, and contact details every time, it simply stores a key pointing to the Teacher table. This dramatically reduces redundancy.

Building the Dimension Tables

Once the fact table was identified, we separated descriptive information into dedicated dimension tables:

Student Dimension contained: Student ID, Student Name, Gender, Age, School, County

Teacher Dimension contained: Teacher ID, Teacher Name, Assigned School, Subject

School Dimension contained: School ID, School Name, Category, Location

Subject Dimension contained: Subject ID, Subject Name

Location Dimension centralized county and region information into a reusable table. This allows both schools and teachers to reference the same geographical information while maintaining consistency.

Understanding Primary Keys and Foreign Keys

One concept that finally clicked for me during this project was the relationship between primary and foreign keys.

A Primary Key uniquely identifies each record within a table. For example:

School ID	School Name
101	Green Hills Academy
102	Sunrise School

The School ID is unique. No two schools share the same identifier.

A Foreign Key references the primary key of another table. For example:

Student	School ID
Alice	101
Brian	102

Rather than writing the school name repeatedly, each student simply stores the corresponding School ID. Power BI then uses relationships to retrieve the descriptive information whenever required. This is one of the principles that makes relational databases so efficient.

Creating Relationships in Power BI

Once the tables had been separated, we connected them using relationships. Power BI supports several relationship types:

One-to-One: Every record matches exactly one record in another table. Valid but relatively uncommon in analytical models.

One-to-Many: This became the dominant relationship in our project. For example, one school has many students; one teacher has many student records; one subject has many assessment records. This relationship type reflects how educational data naturally behaves.

Many-to-One: Power BI often displays the same relationship from the opposite perspective. Many students belong to one school. The underlying relationship remains identical.

Understanding Cardinality

Cardinality determines how Power BI filters information across related tables. If relationships are configured incorrectly, visuals may return duplicated values or incorrect totals. Because our model followed a proper Star Schema, defining cardinality became much more straightforward, with most relationships naturally following a One-to-Many structure.

Filter Direction

Power BI allows filters to flow between related tables. Whenever possible, we maintained single-direction filtering, allowing filters to flow from the dimension tables toward the fact table. For example, selecting a county automatically filtered schools, students, teachers, and performance records. This simplified report behavior while reducing ambiguity during DAX calculations.

Why Normalization Matters

Dividing one table into several smaller tables might appear to make the project more complicated. Ironically, the opposite is true. Normalization makes the model easier to maintain, easier to understand, faster to query, less repetitive, and more scalable. As datasets grow, these benefits become increasingly significant.

Turning Data into Insights with DAX

After building the data model, the next step was teaching Power BI how to answer business questions. This is where Data Analysis Expressions (DAX) became essential.

Think of DAX as the language that teaches Power BI how to think. While relationships organize data, DAX transforms that organized data into business metrics.

Calculated Columns vs. Measures

One key lesson from this project was understanding the difference between these two approaches:

Calculated Columns compute values for every row and store the result in the model
Measures calculate results dynamically based on the filters applied by the user

Since dashboards are interactive, measures are generally the better choice because they recalculate automatically as users interact with slicers and filters.

Measures We Created

Total Students
Total Schools
Total Teachers
Average Student Score
Student Distribution
School Distribution

Where the dataset lacked complete information, such as revenue or outstanding balances, we intentionally avoided creating misleading measures. Instead, we documented the limitation and focused our analysis on the data that was available.

Important principle: Good analysts never invent data to satisfy a report.

Applying Business Logic with DAX

Beyond simple aggregations, DAX can encode business rules. Functions such as IF(), AND(), and OR() allow multiple conditions to be evaluated, while SWITCH(TRUE()) provides a cleaner alternative to long nested IF statements.

For example, a revenue classification measure could be written as:

Revenue Category =
SWITCH(
    TRUE(),
    ISBLANK([Revenue]), "Not Provided",
    [Revenue] > 500000, "High Revenue",
    [Revenue] > 100000, "Medium Revenue",
    [Revenue] > 0, "Low Revenue"
)

We also learned how CALCULATE() changes the filter context, enabling measures such as total students in a specific county or average scores for selected schools. This flexibility is one of the reasons DAX is such a powerful analytical language.

Designing the Interactive Dashboard

With the model and measures complete, the final step was presenting the insights.

Our dashboard combined several visualizations to answer the questions outlined in the project brief:

KPI Cards for key metrics
Bar and Column Charts for comparisons
Donut Charts for distributions
Slicers for filtering by school, county, teacher, subject, and gender
Tables for detailed records

Rather than filling the report with as many visuals as possible, we focused on clarity. Each chart answered a specific business question, and together they formed a cohesive analytical story.

Key Insights from the Project

Building the dashboard highlighted how quickly insights become accessible once data is properly prepared. Users could identify:

Schools with the highest student populations
Student distribution across locations
Gender distribution
Teacher allocation
Subject performance
Academic trends across schools and regions

More importantly, the project demonstrated that the quality of these insights depended entirely on the quality of the underlying data model.

Beyond Power BI Desktop

One topic we covered during training was what happens after a report is built.

Publishing a report to the Power BI Service allows organizations to:

Share reports securely with stakeholders
Schedule automatic data refreshes
Create email subscriptions that send report snapshots at scheduled intervals
Embed reports into web applications and organizational portals

We also explored Microsoft Fabric, Microsoft's unified analytics platform that brings together data engineering, data warehousing, real-time analytics, and business intelligence into a single ecosystem. Although our project was developed in Power BI Desktop, understanding how it fits within the broader Microsoft analytics platform provided valuable context for enterprise-scale solutions.

Lessons I Took Away

This project changed the way I think about dashboards.

Before, I assumed the most important skill in Power BI was creating attractive visualizations. I now realize that visualization is only the final stage of a much larger process.

The real work happens long before the first chart is created:

Connecting securely to data sources
Cleaning inconsistent data
Designing scalable data models
Building meaningful relationships
Writing reusable DAX measures
Delivering insights that decision-makers can trust

A visually impressive dashboard built on poor-quality data is still a poor dashboard.

Final Thoughts

What began as a classroom assignment became an excellent introduction to the complete Business Intelligence workflow.

Over the course of the project, I learned how to connect Power BI to a PostgreSQL database using secure SSL connections, transform messy data with Power Query, redesign a flat dataset into a Star Schema, create relationships between fact and dimension tables, write DAX measures, and communicate insights through an interactive dashboard.

More importantly, I learned that Business Intelligence is not about creating charts. It is about transforming raw, imperfect data into information that supports better decisions.

As I continue exploring data engineering and analytics, this project serves as a reminder that every reliable dashboard begins with a well-designed data pipeline, a thoughtful data model, and a commitment to data quality.

Resources

If you are learning Power BI, I would recommend exploring:

Connect With Me

If you are working on Power BI, data engineering, or analytics projects, I would love to connect and exchange ideas.

GitHub: https://github.com/pinkleather221/

How Excel is Used in Real-World Data Analysis

Nelly Mogere — Sat, 06 Jun 2026 06:57:21 +0000

Introduction

In today’s digital world, data is everywhere. Businesses, schools, hospitals and organizations collect large amounts of information every day. One of the most commonly used tools for organizing and analyzing this data is Microsoft Excel.

Before starting my data analytics journey, I viewed Excel as a simple spreadsheet application mainly used for typing tables and performing calculations. However, after learning more about it during my training at LuxDevHQ, I now understand how powerful and important it is in real-world data analysis.

What is excel

Microsoft Excel is a spreadsheet software developed by Microsoft that helps users:

Organize data
Perform calculations
Analyze information
Create charts and reports
Visualize trends and patterns

Excel is widely used because it is easy to learn, flexible, and useful in many industries.

Real-World application of excel in data analysis

1. Business decision-making
Businesses use Excel to track and analyze important information such as:

Sales performance
Inventory records
Employee performance
Customer trends

For example, a company can use Excel to identify which products sell the most and which products are underperforming. This helps managers make better business decisions.

2. Financial Reporting and Budgeting
Excel is heavily used in finance and accounting. Companies use it to:

Calculate profits and losses
Track expenses
Prepare budgets
Forecast future revenue

Functions like SUMIF and AVERAGEIF help analysts quickly calculate totals and averages based on specific conditions.

Excel Features and Formulas I Have Learned

So far, I have learned several useful Excel features and formulas that are commonly used in data analysis.

Features

Freeze Panes
This feature keeps important rows or columns visible while scrolling through large datasets.

Remove Duplicates
This helps clean data by removing repeated information, making datasets more accurate and organized.

Formulas

SUMIF
Used to calculate totals based on a condition.
Example:
Calculating total sales from a specific region.

AVERAGEIF
Used to find averages that meet certain conditions.
Example:
Finding the average marks of students in one class.

COUNTIFS
Used to count values that meet multiple conditions.
Example:
Counting customers from a certain city who purchased a specific product.

My Personal Reflection

At first, I thought Excel was something simple and easy. However, after attending classes and practicing different features and formulas, I now realize that Excel is a powerful analytical tool used in real-world industries.

Even with the few classes I have attended so far, I already feel more confident working with data. I can now:

Find insights from datasets
Calculate totals and averages
Clean messy data
Visualize information clearly

This learning experience has made me more interested in data analytics and I look forward to learning more advanced skills in the future.

Conclusion

Excel plays a major role in real-world data analysis because it helps people organize, analyze and understand data efficiently. From business reporting to financial analysis and marketing insights, Excel continues to be one of the most valuable tools in the world of data.

As a beginner in data analytics, learning Excel has given me confidence and a better understanding of how data can be used to solve real-world problems.

I Built a Cloud Platform That Does Absolutely Nothing (And It Works Perfectly)

Nelly Mogere — Mon, 06 Apr 2026 05:31:11 +0000

This is a submission for the DEV April Fools Challenge

What I Built

I’ve spent a ridiculous amount of time learning cloud architecture, Kubernetes, scaling systems… all the serious stuff.
So naturally, I decided to build something completely useless.
I present: Cloud Architect Simulator™ a platform that successfully deploys absolutely nothing… at scale.
You can:
Deploy apps that don’t exist
Auto-scale to infinity (with 0 users 💀)
Optimize your infrastructure using AI (which deletes everything for better performance)
Brew coffee via API (spoiler: I’m a teapot ☕)
There’s even a region selector with options like Mars (Beta) and Some Guy’s Laptop because… why not?
The whole idea was to poke fun at how complex and over-engineered cloud systems can get except here, all that complexity leads to absolutely no outcome.
And somehow… it still works perfectly.

Demo

pinkleather221.github.io

Everything is working exactly as it shouldn’t

Code

pinkleather221 / Cloud-Architect-Simulator-Deploying-Nothing-at-Scale-

Cloud Architect Simulator™ is a fake cloud platform where you deploy apps that don’t exist, scale to infinity with zero users, and optimize everything into nothing. Built for the DEV April Fools Challenge, it’s a playful take on over-engineered systems.

Cloud-Architect-Simulator-Deploying-Nothing-at-Scale-

Cloud Architect Simulator™ is a fake cloud platform where you deploy apps that don’t exist, scale to infinity with zero users, and optimize everything into nothing. Built for the DEV April Fools Challenge, it’s a playful take on over-engineered systems.

View on GitHub

It’s a simple HTML, CSS and JavaScript setup nothing fancy, just pure intentional nonsense layered on top of clean structure.

How I Built It

I kept the stack intentionally simple:
HTML for structure
CSS for styling (dark mode because we’re engineers obviously)
Vanilla JavaScript for all the chaotic interactions
Most of the logic is just fake state changes, random values and delayed responses to make it feel real.
The fun part was designing interactions that look legitimate at first… and then slowly fall apart.
For example:
Fake deployment logs that end in “No application found”
Billing that spikes randomly then pretends nothing happened
AI optimization that aggressively removes everything
It’s basically a real cloud dashboard… if it had an existential crisis.

Prize Category

I had to pay respect to the legendary 418 I’m a teapot energy.
The “Brew Coffee API” feature is my tribute it goes through a whole dramatic process before ultimately refusing to cooperate because… it’s a teapot.
It felt very on-brand for a project that commits fully to doing the wrong thing in the most elaborate way possible.

DEV Community: Nelly Mogere

Need to calculate running totals or rank rows without GROUP BY headaches? Enter SQL Window Functions. Let’s break down how they work with practical, real-world examples.

SQL Window Functions: Rank, Compare, and Analyze Without Losing Rows

SQL Window Functions: Rank, Compare, and Analyze Without Losing Rows

The project dataset

First, what makes a function a window function?

The anatomy of a window expression

OVER

PARTITION BY

Window ORDER BY

The window frame

The main categories of window functions

1. Aggregate window functions

2. Ranking window functions

3. Offset or row-comparison functions

4. Framed window calculations

Query 1: Compare the ranking functions

Query 2: Rank drivers by total revenue

Query 3: Keep every trip and add the rider's total spend

Query 4: Find each driver's highest-fare trip

Query 5: Rank riders inside their city

Query 6: Compare each fare with the previous trip

Query 7: Calculate a three-trip rolling average

Query 8: Divide riders into spend quartiles

A practical way to choose a window function

Final thoughts

Write Clearer SQL: When to Use CTEs or Subqueries

Start with the mental model

The main types of CTEs

Example 1: A standalone, non-recursive CTE

Example 2: Chained non-recursive CTEs

Example 3: A recursive CTE

Now compare subqueries

CTE or subquery? Use this checklist

Common mistakes to avoid

SQL Intermediate Project: Build a Greenwood Academy Database with PostgreSQL

Intermediate SQL Portfolio Project: Master Key Skills

What this project is about

1. Creating the schema

2. Creating the students table

3. Creating the subjects table

4. Creating the exam results table

5. Altering tables after creation

6. Inserting student data

7. Inserting subjects and exam results

8. Updating and deleting records

9. Filtering students and subjects

10. Combining conditions with AND and OR

11. Using BETWEEN, IN, NOT IN, and LIKE

12. Working with string functions

13. Working with dates

14. Important gotcha: hidden spaces in TO_CHAR day names

15. Counting records

16. Using CASE WHEN for readable labels

17. Using CASE WHEN with class labels

18. Finding upcoming birthdays with a subquery

What I learned from this project

Final thoughts

SQL Beginner Project: Build a Real-World Supermarket Database with PostgreSQL

SQL Beginner Project: Build a Real-World Supermarket Database with PostgreSQL

What is SQL?

SQL Areas Covered in This Project

Project Scenario: Sunrise Supermarket

1. Creating the Database Schema

2. Creating the Customers Table

3. Inserting Customers

4. Creating the Products Table

5. Inserting Products

6. Creating Orders and Order Items

7. Inserting Orders and Order Items

8. Altering Table Structure

9. Updating and Deleting Data

10. Filtering Products and Customers

Products priced above 100

Customers not based in Nairobi

Products priced between 60 and 200

Customers in selected counties

Products with Oil in the name

11. Sorting and Limiting Results

12. Grouping and Aggregates

`OVER`

`PARTITION BY`

Window `ORDER BY`