DEV Community: Victoria Ndei

# SQL Essentials: Joins, Window Functions, CTEs, and Subqueries Using a Hospital Database

Victoria Ndei — Sun, 18 Oct 2026 05:39:00 +0000

Introduction

SQL is more than retrieving data from a database. It also helps us combine information from multiple tables, analyse trends, rank records, calculate running totals, and break down complex queries into manageable steps.

In this article, we will explore four essential SQL concepts: joins, window functions, Common Table Expressions (CTEs), and subqueries. Using a hospital database implemented in PostgreSQL, we will examine how each concept works, when to use it, and how it can help answer practical questions.

The hospital dataset contains information about patients, doctors, departments, diagnoses, procedures, wards, and hospital visits. It allows us to explore questions such as which department has the highest billing, which patients have multiple visits, and how individual bills compare with group averages.

Target audience

This article is intended for:

Beginners learning SQL and relational databases.
Students completing database and data analytics assignments.
Aspiring data analysts who want practical SQL experience.
Developers who want to write clearer and more effective SQL queries.

Prerequisites

Before following the examples, you should have:

Basic knowledge of SQL statements such as SELECT, FROM, WHERE, and ORDER BY.
A basic understanding of relational database tables, primary keys, and foreign keys.
Access to PostgreSQL or a PostgreSQL-compatible environment.
The hospital database script used in this article.

Learning objectives

By the end of this article, you should be able to:

Explain and use different types of SQL joins.
Apply window functions to rank records, compare visits, and calculate aggregates.
Understand OVER(), PARTITION BY, window ordering, and window frames.
Create basic, multiple, and recursive CTEs.
Use scalar, correlated, non-correlated, and nested subqueries.
Apply IN, EXISTS, ANY, and ALL.
Decide when to use a CTE, join, window function, or subquery.

Understanding the hospital database

The database uses the PostgreSQL schema morning.

The main tables include:

Table	Description
`morning.patients`	Stores patient names, ages, genders, counties, and sub-counties.
`morning.doctor`	Contains doctor names, specialties, genders, and experience.
`morning.department`	Stores hospital department names and IDs.
`morning.diagnosis`	Contains diagnosis names and categories.
`morning.procedure`	Stores medical procedure names and categories.
`morning.words`	Stores ward IDs and ward names.
`morning.visit_flat`	Contains visit dates, patient and doctor IDs, diagnoses, procedures, admission details, bills, and satisfaction scores.

The dataset contains 30 hospital visits, 25 patients, four doctors, four departments, 18 diagnoses, 15 procedures, and four wards.

Most examples use morning.visit_flat because it contains the records for individual hospital visits.

One important implementation detail is that the supplied script names the ward table morning. wards. The examples retain that exact table name. Also, the script does not declare foreign-key constraints for doctor_id and department_id, although the IDs match the corresponding records in the supplied data.

1. SQL Joins: Combining Data from Multiple Tables

A database often stores related information in separate tables to reduce duplication and improve organization. Joins allow us to retrieve related information using matching columns.

For example, the patient's name is stored in patients, while the visit date and bill are stored in visit_flat. A join allows us to display this information together.

INNER JOIN

An INNER JOIN returns only records that have matching values in both tables.

Example

SELECT
    p.patient_name,
    v.visit_id,
    v.visit_date,
    v.total_bill_kes
FROM morning.patients AS p
INNER JOIN morning.visit_flat AS v
    ON p.patient_id = v.patient_id
ORDER BY v.visit_id;

Explanation

patients provides the patient's name.
visit_flat provides the visit ID, date, and bill.
The ON clause matches records using patient_id.

Expected result

The query returns 30 rows because all 30 visits have matching patient records.

For example, visit 1001 belongs to Amina Otieno, occurred on 1 June 2026, and has a recorded bill of KSh 3,500.

When should you use INNER JOIN? Use it when you only want records with matching data in both tables.

LEFT JOIN

A LEFT JOIN returns every record from the left table and matching records from the right table. If no match exists, the right-side columns contain NULL.

Example

SELECT
    p.patient_id,
    p.patient_name,
    v.visit_id,
    v.visit_date
FROM morning.patients AS p
LEFT JOIN morning.visit_flat AS v
    ON p.patient_id = v.patient_id
ORDER BY p.patient_id, v.visit_date;

Explanation

Every patient is retained, even if they have no visit record. Patients with multiple visits appear more than once.

In the supplied dataset, every patient has at least one recorded visit, so no patient has NULL visit details.

To find patients without recorded visits, we can use:

SELECT
    p.patient_id,
    p.patient_name
FROM morning.patients AS p
LEFT JOIN morning.visit_flat AS v
    ON p.patient_id = v.patient_id
WHERE v.visit_id IS NULL;

This returns zero rows for the current dataset.

When should you use LEFT JOIN? Use it when all records from the left-hand table must be retained, including those without matching records on the right.

RIGHT JOIN

A RIGHT JOIN returns every record from the right table and matching records from the left table.

Example

SELECT
    p.patient_name,
    v.visit_id,
    v.visit_date,
    v.total_bill_kes
FROM morning.patients AS p
RIGHT JOIN morning.visit_flat AS v
    ON p.patient_id = v.patient_id
ORDER BY v.visit_id;

Explanation

Every visit is retained. If a visit referred to a patient who did not exist in the patients table, the patient columns would contain NULL.

In the supplied dataset, all visit patient IDs have matching patient records, so all 30 visits display a patient name.

When should you use RIGHT JOIN? Use it when every record from the right-hand table must be retained. The same result can often be expressed with a LEFT JOIN by reversing the table order.

FULL OUTER JOIN

A FULL OUTER JOIN returns all matching records and preserves unmatched records from both tables.

Example

SELECT
    p.patient_id,
    p.patient_name,
    v.visit_id,
    v.total_bill_kes
FROM morning.patients AS p
FULL OUTER JOIN morningvisit_flat AS v
    ON p.patient_id = v.patient_id
ORDER BY p.patient_id, v.visit_id;

Explanation

Matching patient and visit records are combined.
Patients without visits would still appear, with visit columns set to NULL.
Visits without matching patients would also appear, with patient columns set to NULL.

The supplied dataset has no unmatched patient-to-visit records, so the query returns 30 rows.

When should you use FULL OUTER JOIN? Use it when comparing or reconciling datasets where either side may contain unmatched records.

CROSS JOIN

A CROSS JOIN returns every possible combination of rows from two tables.

Example

SELECT
    d.doctor_name,
    dep.department_name
FROM morning.doctor AS d
CROSS JOIN morning. department AS dep
ORDER BY d.doctor_id, dep.department_id;

Explanation

The dataset contains four doctors and four departments. The query generates every possible doctor-department combination.

The expected number of rows is:

4 doctors × 4 departments = 16 combinations.

These combinations are generated for demonstration; they do not represent confirmed doctor assignments.

When should you use CROSS JOIN? Use it when you need every possible combination, such as generating combinations for testing or analysis.

SELF JOIN

A self join joins a table to itself. It allows us to compare records within the same table.

Example: Find patients from the same county

SELECT
    p1.patient_name AS patient_one,
    p2.patient_name AS patient_two,
    p1.county
FROM morning.patients AS p1
INNER JOIN morning.patients AS p2
    ON p1.county = p2.county
   AND p1.patient_id < p2.patient_id
ORDER BY p1.county, p1.patient_id;

Explanation

The aliases p1 and p2 represent two instances of the same table.

The query matches patients from the same county. The condition p1.patient_id < p2.patient_id prevents a patient from being matched with themselves and avoids returning the same pair twice.

For example, Amina Otieno and Isaac Kamau both come from Nairobi, while Esther Achieng and Brian Omondi both come from Kisumu.

When should you use SELF JOIN? Use it when comparing records within one table, such as patients from the same county or employees who share a manager.

Joining multiple hospital tables

Joins can combine several tables to create a more complete hospital visit report.

SELECT
    v.visit_id,
    v.visit_date,
    p.patient_name,
    d.doctor_name,
    dep.department_name,
    dg.diagnosis_name,
    pr.procedure_name,
    w.ward_name,
    v.total_bill_kes
FROM morning.visit_flat AS v
INNER JOIN morning.patients AS p
    ON v.patient_id = p.patient_id
INNER JOIN morning.doctor AS d
    ON v.doctor_id = d.doctor_id
INNER JOIN morning.department AS dep
    ON v.department_id = dep.department_id
INNER JOIN morning.diagnosis AS dg
    ON v.diagnosis_id = dg.diagnosis_id
INNER JOIN morning.procedure AS pr
    ON v.procedure_id = pr.procedure_id
INNER JOIN morning.words AS w
    ON v.ward_id = w.ward_id
ORDER BY v.visit_date, v.visit_id;

This query combines visit, patient, doctor, department, diagnosis, procedure, and ward information. It returns 30 rows for the supplied dataset.

Summary of join types

Join type	Purpose
`INNER JOIN`	Returns matching records from both tables.
`LEFT JOIN`	Preserves all left-table records.
`RIGHT JOIN`	Preserves all right-table records.
`FULL OUTER JOIN`	Preserves records from both tables, matched where possible.
`CROSS JOIN`	Generates every possible combination.
`SELF JOIN`	Compares records within the same table.

2. SQL Window Functions: Analysing Data Without Losing Rows

Window functions perform calculations across a group of related rows while keeping each individual row in the result.

Unlike GROUP BY, which normally combines rows into summary records, window functions allow us to display individual visits alongside calculated values.

Understanding OVER()

The OVER() clause defines the window of rows used by a window function.

Example

SELECT
    visit_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER ()
        AS overall_average_bill
FROM morning.visit_flat
ORDER BY visit_id;

The query calculates the average bill across all 30 visits and displays that same average beside every visit.

PARTITION BY

PARTITION BY divides the rows into groups for a window calculation.

Example

SELECT
    visit_id,
    doctor_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER (
        PARTITION BY doctor_id
    ) AS doctor_average_bill
FROM morning.visit_flat
ORDER BY doctor_id, visit_id;

This calculates a separate average bill for each doctor while retaining all individual visit records.

ORDER BY inside a window

The ORDER BY inside OVER() defines the order used by the window calculation. It is different from the final query-level ORDER BY, which controls how the results are displayed.

Example

SELECT
    visit_id,
    visit_date,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total
FROM morning.visit_flat
ORDER BY visit_date, visit_id;

The running total increases as each visit is processed in date and visit ID order.

Window frames

A window frame defines which rows are included in a calculation relative to the current row.

Common frame boundaries include:

UNBOUNDED PRECEDING: the beginning of the partition.
CURRENT ROW: the current row.
UNBOUNDED FOLLOWING: the end of the partition.

For example, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW calculates a running total from the first row up to the current row.

Explicitly specifying a frame is particularly important for functions such as LAST_VALUE(), where the default frame may not extend to the final row of the entire partition.

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within a window.

Example: Number visits by bill for each doctor

SELECT
    visit_id,
    doctor_id,
    total_bill_kes,
    ROW_NUMBER() OVER (
        PARTITION BY doctor_id
        ORDER BY total_bill_kes DESC, visit_id
    ) AS visit_number
FROM morning.visit_flat
ORDER BY doctor_id, visit_number;

Explanation

The query partitions visits by doctor and numbers them from the highest bill to the lowest bill.

For doctor 101, visit 1017, with a bill of KSh 82,000, receives row number 1. Visit 1010, with a bill of KSh 76,000, receives row number 2.

The visit_id acts as a tie-breaker when bills are equal.

When should you use ROW_NUMBER()? Use it when assigning sequential numbers, selecting the first record in a group, or identifying duplicate records.

RANK() and DENSE_RANK()

Both functions rank records according to an ordering, but they handle ties differently.

Example

SELECT
    visit_id,
    total_bill_kes,
    RANK() OVER (
        ORDER BY total_bill_kes DESC
    ) AS bill_rank,
    DENSE_RANK() OVER (
        ORDER BY total_bill_kes DESC
    ) AS dense_bill_rank
FROM morning.visit_flat
ORDER BY bill_rank, visit_id;

Explanation

RANK() assigns equal ranks to tied bills and leaves gaps afterward.
DENSE_RANK() assigns equal ranks to tied bills without leaving gaps.

For example, two visits with bills of KSh 6,500 receive the same rank. With RANK(), the next rank is skipped; with DENSE_RANK(), the next distinct bill receives the next consecutive rank.

When should you use them? Use these functions when ranking visits, doctors, departments, or other records where tied values should share a position.

NTILE()

NTILE(n) divides an ordered result into approximately equal-sized groups.

Example: Divide visits into four billing groups

SELECT
    visit_id,
    total_bill_kes,
    NTILE(4) OVER (
        ORDER BY total_bill_kes DESC, visit_id
    ) AS bill_quartile
FROM morning.visit_flat
ORDER BY bill_quartile, total_bill_kes DESC;

Explanation

The 30 visits are divided into four groups based on their bills. Because 30 cannot be divided evenly into four whole-number groups, some groups contain seven rows, and others contain eight.

Group 1 contains the highest-billed visits, while group 4 contains the lowest-billed visits.

When should you use NTILE()? Use it to divide data into quartiles or other approximately equal-sized groups for descriptive analysis. Billing quartiles are financial groupings, not measures of clinical severity.

LAG()

LAG() retrieves a value from a preceding row without requiring a self join.

Example: Compare each patient's bill with their previous bill

SELECT
    patient_id,
    visit_id,
    visit_date,
    total_bill_kes,
    LAG(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
    ) AS previous_bill
FROM morning.visit_flat
ORDER BY patient_id, visit_date;

Explanation

The query compares each visit with the previous recorded visit for the same patient.

Amina Otieno's first visit has no previous bill, so the value is NULL. Her second recorded visit has a bill of KSh 4,200, and the previous bill is KSh 3,500.

When should you use LAG()? Use it to compare current and previous transactions, calculate changes over time, or examine changes between consecutive visits.

LEAD()

LEAD() retrieves a value from a following row.

Example

SELECT
    patient_id,
    visit_id,
    visit_date,
    total_bill_kes,
    LEAD(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
    ) AS next_bill
FROM morning.visit_flat
ORDER BY patient_id, visit_date;

The query displays the bill from each patient's next recorded visit. If a patient has no later visit, the result is NULL.

For Amina Otieno, the first visit's next bill is KSh 4,200.

When should you use LEAD()? Use it to examine subsequent transactions, visits, or events in an ordered dataset.

FIRST_VALUE()

FIRST_VALUE() returns the value from the first row of the window frame.

Example: Find each patient's first recorded bill

SELECT
    patient_id,
    visit_id,
    visit_date,
    total_bill_kes,
    FIRST_VALUE(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS first_bill
FROM morning.visit_flat
ORDER BY patient_id, visit_date;

The query displays each patient's first recorded bill alongside every visit for that patient.

For Amina Otieno, the first bill is KSh 3,500.

When should you use FIRST_VALUE()? Use it to compare each record with the first value or event in a group.

LAST_VALUE()

LAST_VALUE() returns the value from the last row of the window frame.

Example: Find each patient's last recorded bill

SELECT
    patient_id,
    visit_id,
    visit_date,
    total_bill_kes,
    LAST_VALUE(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS last_bill
FROM morning.visit_flat
ORDER BY patient_id, visit_date;

The explicit window frame ensures that the calculation reaches the final row of each patient's partition.

For Amina Otieno, the last recorded bill is KSh 4,200, which appears alongside both of her visits.

When should you use LAST_VALUE()? Use it to compare records with the final value in an ordered group.

SUM() OVER()

SUM() calculates a sum across a window without collapsing individual rows.

Example: Calculate cumulative hospital billing

SELECT
    visit_id,
    visit_date,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS cumulative_billing
FROM morning.visit_flat
ORDER BY visit_date, visit_id;

The cumulative total increases with each visit. The final total is KSh 488,000, representing the sum of all 30 visit bills in the dataset.

When should you use SUM()? Use it for running totals and group-level financial calculations.

AVG() OVER()

AVG() calculates an average across a window.

Example: Compare bills with their department average

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    ROUND(
        AVG(total_bill_kes) OVER (
            PARTITION BY department_id
        ),
        2
    ) AS department_average_bill
FROM morning.visit_flat
ORDER BY department_id, visit_id;

Each visit remains visible alongside the average bill for its department.

For department 10, the average bill is approximately KSh 23,344.44.

When should you use AVG()? Use it to compare individual records with their group's average without losing the original rows.

COUNT() OVER()

COUNT() can count rows in a window while retaining individual records.

Example: Count visits per patient

SELECT
    patient_id,
    visit_id,
    COUNT(*) OVER (
        PARTITION BY patient_id
    ) AS patient_visit_count
FROM morning.visit_flat
ORDER BY patient_id, visit_id;

Patients with two recorded visits receive a count of 2 on each of their visit rows. For example, patients with IDs 1 and 2 each have two visits.

When should you use COUNT()? Use it to identify repeat visits and measure how frequently records appear in a group.

MIN() and MAX() OVER()

MIN() returns the smallest value in a window, while MAX() returns the largest.

Example

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    MIN(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS minimum_department_bill,
    MAX(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS maximum_department_bill
FROM morning.visit_flat
ORDER BY department_id, visit_id;

For department 10, the minimum bill is KSh 3,500, and the maximum bill is KSh 82,000.

When should you use MIN() and MAX()? Use them to identify the smallest and largest values within a group while keeping each original record visible.

Summary of window functions

Function	Purpose
`ROW_NUMBER()`	Assigns a unique sequential number to each row.
`RANK()`	Ranks rows and leaves gaps after ties.
`DENSE_RANK()`	Ranks rows without gaps after ties.
`NTILE()`	Divides ordered rows into approximately equal-sized groups.
`LAG()`	Retrieves a value from a preceding row.
`LEAD()`	Retrieves a value from a following row.
`FIRST_VALUE()`	Returns the first value in the window frame.
`LAST_VALUE()`	Returns the last value in the window frame.
`SUM()`	Calculates a sum across the window.
`AVG()`	Calculates an average across the window.
`COUNT()`	Counts rows or non-null values in the window.
`MIN()`	Returns the smallest value in the window.
`MAX()`	Returns the largest value in the window.

3. Common Table Expressions (CTEs): Making Queries Easier to Read

A Common Table Expression is a named result set defined using the WITH clause. It exists for the duration of one SQL statement and helps organise complex queries into logical steps.

Basic CTE

Example: Find visits with bills above KSh 20,000

WITH high_bill_visits AS (
    SELECT
        visit_id,
        patient_id,
        visit_date,
        total_bill_kes
    FROM morning.visit_flat
    WHERE total_bill_kes > 20000
)
SELECT *
FROM high_bill_visits
ORDER BY total_bill_kes DESC;

Explanation

The CTE selects visits whose bills exceed KSh 20,000. The main query then retrieves the filtered records and sorts them by bill.

When should you use a basic CTE? Use it when you want to separate a filtering or calculation step from the main query to make the SQL easier to read.

Using multiple CTEs

CTEs can be chained together so that later CTEs use the results of earlier ones.

Example: Calculate total billing by department

WITH department_billing AS (
    SELECT
        department_id,
        COUNT(*) AS visit_count,
        SUM(total_bill_kes) AS total_billing
    FROM morning.visit_flat
    GROUP BY department_id
),
department_report AS (
    SELECT
        d.department_name,
        db.visit_count,
        db.total_billing
    FROM department_billing AS db
    INNER JOIN morning.department AS d
        ON db.department_id = d.department_id
)
SELECT *
FROM department_report
ORDER BY total_billing DESC;

Explanation

The first CTE calculates the number of visits and total billing for each department. The second joins those results to the department table to display department names.

Expected result

Department	Visits	Total billing
Cardiology	9	KSh 210,100
Orthopedics	6	KSh 105,200
General Medicine	9	KSh 97,800
Pediatrics	6	KSh 74,900

When should you use multiple CTEs? Use them when a query has several logical stages, such as aggregation followed by joining and filtering.

Recursive CTE

A recursive CTE references itself to generate a sequence or traverse hierarchical data. It typically contains an initial query and a recursive query connected by UNION ALL.

The supplied hospital database does not contain a natural parent-child hierarchy, so this example generates a calendar sequence using the hospital visit dates.

Example: Generate dates between the first and last visit

WITH RECURSIVE visit_calendar AS (
    SELECT MIN(visit_date) AS calendar_date
    FROM morning.visit_flat

    UNION ALL

    SELECT calendar_date + 1
    FROM visit_calendar
    WHERE calendar_date < (
        SELECT MAX(visit_date)
        FROM morning.visit_flat
    )
)
SELECT calendar_date
FROM visit_calendar
ORDER BY calendar_date;

Explanation

The initial query selects the earliest visit date. The recursive query adds one day to the previous date until it reaches the latest visit date.

The result contains 15 dates, from 2026-06-01 to 2026-06-15.

When should you use a recursive CTE? Use it for generating sequences, traversing hierarchies, or processing data that requires repeated steps.

CTEs with joins and aggregations

CTEs can combine aggregation and joins to produce readable summary reports.

Example: Department billing and average satisfaction

WITH department_metrics AS (
    SELECT
        department_id,
        COUNT(*) AS visit_count,
        SUM(total_bill_kes) AS total_billing,
        ROUND(AVG(patient_satisfaction_score), 2)
            AS average_satisfaction
    FROM morning.visit_flat
    GROUP BY department_id
)
SELECT
    d.department_name,
    dm.visit_count,
    dm.total_billing,
    dm.average_satisfaction
FROM department_metrics AS dm
INNER JOIN morningdepartment AS d
    ON dm.department_id = d.department_id
ORDER BY dm.total_billing DESC;

Explanation

The CTE calculates visit counts, total billing, and average satisfaction scores. The main query adds department names by joining the aggregated result to department.

This produces a report that combines operational and financial metrics.

CTEs versus subqueries

Feature	CTE	Subquery
Definition	Uses `WITH`.	Nested inside another query.
Readability	Often clearer for multi-step logic.	Often concise for simple calculations.
Reuse	Can be referenced multiple times in one statement.	May need to be repeated if the same logic is reused.
Recursive processing	Supports recursive queries using `WITH RECURSIVE`.	Not the usual approach for recursive processing.
Performance	Depends on the query and execution plan.	Also depends on the query and execution plan.

Neither approach is automatically faster. Choose based on readability, maintainability, and the execution plan.

4. Subqueries: Queries Inside Other Queries

A subquery is a query nested inside another SQL statement. It can return a single value, multiple rows, or a table-like result.

Subqueries can appear in SELECT, FROM, and WHERE clauses.

Scalar subquery

A scalar subquery returns a single value: one row and one column.

Example: Display the average bill alongside each visit

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT AVG(total_bill_kes)
        FROM morning.visit_flat
    ) AS overall_average_bill
FROM morning.visit_flat
ORDER BY visit_id;

Explanation

The inner query calculates the average bill across all visits. The outer query displays that value alongside every visit.

When should you use a scalar subquery? ** Use it when a single calculated value is needed within a larger query.

Non-correlated subquery

A non-correlated subquery does not depend on values from the outer query.

Example: Find visits with bills above the overall average

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM morning.visit_flat
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM morning.visit_flat
)
ORDER BY total_bill_kes DESC;

Explanation

The inner query calculates the overall average bill. The outer query returns visits whose bills exceed that value.

The subquery does not reference the outer query, so it is non-correlated.

When should you use it? ** Use it when comparing records against a general benchmark, such as an overall average.

Correlated subquery

A correlated subquery refers to a value from the outer query. Its result depends on the current outer row.

Example: Find visits above the average bill for that patient

SELECT
    v.visit_id,
    v.patient_id,
    v.total_bill_kes
FROM morning.visit_flat AS v
WHERE v.total_bill_kes > (
    SELECT AVG(v2.total_bill_kes)
    FROM morning.visit_flat AS v2
    WHERE v2.patient_id = v.patient_id
)
ORDER BY v.patient_id, v.visit_id;

Explanation

For each visit, the inner query calculates the average bill for the same patient. The outer query returns visits whose bills exceed that patient's average.

Patients with only one visit will not appear because their bill equals their own average.

When should you use it? Use it for comparisons within a group, such as comparing each visit with its patient's average.

Nested subquery

A nested subquery contains another subquery inside it.

Example: Find visits above the average bill for chronic-disease diagnoses

SELECT
    visit_id,
    diagnosis_id,
    total_bill_kes
FROM morning.visit_flat
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM morning.visit_flat
    WHERE diagnosis_id IN (
        SELECT diagnosis_id
        FROM morning.diagnosis
        WHERE diagnosis_category = 'Chronic Disease'
    )
)
ORDER BY total_bill_kes DESC;

Explanation

The innermost query identifies diagnoses in the chronic-disease category.
The middle query calculates the average bill for visits with those diagnoses.
The outer query returns visits whose bills exceed that average.

The outer query is not restricted to chronic-disease visits; it compares all visits with the calculated benchmark.

When should you use it? Use nested subqueries when one query must identify a set of records before another query can calculate or filter results.

Subqueries using IN

The IN operator checks whether a value matches any value returned by a list or subquery.

Example: Find visits associated with chronic diseases

SELECT
    visit_id,
    diagnosis_id,
    total_bill_kes
FROM morning.visit_flat
WHERE diagnosis_id IN (
    SELECT diagnosis_id
    FROM morning.diagnosis
    WHERE diagnosis_category = 'Chronic Disease'
)
ORDER BY visit_id;

The inner query returns diagnosis IDs in the chronic-disease category. The outer query returns visits associated with those diagnoses.

When should you use IN? Use it when filtering records based on membership in a set of values.

Subqueries using EXISTS

EXISTS checks whether a subquery returns at least one row.

Example: Find patients with multiple visits

SELECT
    p.patient_id,
    p.patient_name
FROM morning.patients AS p
WHERE EXISTS (
    SELECT 1
    FROM morning.visit_flat AS v
    WHERE v.patient_id = p.patient_id
    GROUP BY v.patient_id
    HAVING COUNT(*) > 1
)
ORDER BY p.patient_id;

Explanation

For each patient, the subquery checks whether that patient has more than one visit. EXISTS returns true when the subquery finds a matching group.

Patients with multiple visits include Amina Otieno, John Kariuki, Esther Achieng, Lucy Wambui, and Peter Ouma.

When should you use EXISTS? Use it when the goal is to check whether related records exist rather than retrieve those records.

Subqueries using ANY

ANY compares a value with the results of a subquery. The condition is true if it is satisfied for at least one returned value.

Example: Find visits with bills greater than at least one inpatient bill

SELECT
    visit_id,
    admission_type,
    total_bill_kes
FROM morning.visit_flat
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM morning.visit_flat
    WHERE admission_type = 'Inpatient'
)
ORDER BY total_bill_kes DESC;

Explanation

The subquery returns the bills of inpatient visits. A visit satisfies the condition if its bill is greater than at least one of those bills.

For a non-empty set of values, > ANY is equivalent to being greater than the smallest value in that set.

When should you use ANY? Use it when a comparison needs to be satisfied by at least one value in a result set.

Subqueries using ALL

ALL compares a value with every value returned by a subquery. The condition must be true for all returned values.

Example: Find visits whose bills are at least as high as every inpatient bill

SELECT
    visit_id,
    admission_type,
    total_bill_kes
FROM morning.visit_flat
WHERE total_bill_kes >= ALL (
    SELECT total_bill_kes
    FROM morning.visit_flat
    WHERE admission_type = 'Inpatient'
)
ORDER BY total_bill_kes DESC;

Explanation

The condition requires a visit's bill to be greater than or equal to every inpatient bill.

The highest inpatient bill in the dataset is KSh 82,000, recorded for visit 1017. Therefore, that visit satisfies the condition.

If we used > ALL instead, no rows would be returned because no bill exceeds KSh 82,000.

When should you use ALL? Use it when a value must satisfy a comparison against every value in a set.

Subqueries in the SELECT clause

A subquery in the SELECT clause can add a calculated value to each output row.

Example: Display the highest bill alongside each visit

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT MAX(total_bill_kes)
        FROM morning.visit_flat
    ) AS highest_bill_in_dataset
FROM morning.visit_flat
ORDER BY visit_id;

The highest_bill_in_dataset column displays KSh 82,000 on every row, while the individual visit bills vary.

When should you use it? ** Use a scalar subquery in SELECT when each row needs a shared benchmark or summary value.

Subqueries in the FROM clause

A subquery in the FROM clause acts as a derived table. In PostgreSQL, it must have an alias.

Example: Count visits for each patient

SELECT
    p.patient_name,
    patient_summary.visit_count
FROM (
    SELECT
        patient_id,
        COUNT(*) AS visit_count
    FROM morning.visit_flat
    GROUP BY patient_id
) AS patient_summary
INNER JOIN morning.patients AS p
    ON patient_summary.patient_id = p.patient_id
ORDER BY patient_summaryvisit_count DESC, p.patient_name;

Explanation

The derived table calculates the number of visits per patient. The outer query joins those counts to patients to display patient names.

When should you use it? ** Use a derived table when an aggregated or transformed result needs to be used as input for a larger query.

Summary of subquery types

Type	Purpose
Scalar	Returns one value.
Non-correlated	Does not depend on the outer query.
Correlated	References values from the outer query.
Nested	Contains another subquery.
`IN`	Checks membership in a set of values.
`EXISTS`	Checks whether a matching row exists.
`ANY`	Requires a comparison to be true for at least one returned value.
`ALL`	Requires a comparison to be true for every returned value.
In `SELECT`	Adds a calculated value to the output.
In `FROM`	Creates a derived table.
In `WHERE`	Filters records using another query's result.

5. CTEs vs Subqueries

Both CTEs and subqueries help structure query logic, but they differ in how the logic is expressed.

Feature	CTE	Subquery
Definition	Uses `WITH`.	Nested inside another query.
Readability	Often clearer for multi-step logic.	Often concise for simple logic.
Reuse	Can be referenced multiple times within one statement.	May need to be repeated if the same logic is reused.
Recursive queries	Supported using `WITH RECURSIVE`.	Not the usual approach for recursive processing.
Performance	Depends on the query plan.	Also depends on the query plan.

Neither approach is universally faster. The choice should depend on clarity, maintainability, and the execution plan produced by PostgreSQL.

6. Combining These Concepts

The real value of SQL comes from combining these techniques to answer practical questions.

For example, we can rank visits by bill within each department, then join the result to the department table to display department names.

WITH RankedVisits AS (
    SELECT
        department_id,
        visit_id,
        patient_id,
        total_bill_kes,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY total_bill_kes DESC, visit_id
        ) AS rn
    FROM morning.visit_flat
)
SELECT
    dep.department_name,
    r.visit_id,
    p.patient_name,
    r.total_bill_kes
FROM RankedVisits AS r
INNER JOIN morning.department AS dep
    ON r.department_id = dep.department_id
INNER JOIN morning.patients AS p
    ON r.patient_id = p.patient_id
WHERE r.rn = 1
ORDER BY r.total_bill_kes DESC;

This query combines three concepts:

CTE: Organises the visit-ranking logic.
Window function: Ranks visits within each department.
Joins: Retrieves department names and patient names.

The result contains one highest-billed visit for each department. The visit_id tie-breaker makes the ranking consistent when bills are equal.

Conclusion

Joins, window functions, CTEs, and subqueries are essential tools for writing effective SQL queries.

Joins combine related data from multiple tables. Window functions analyse and compare rows without losing individual records. CTEs organise complex queries into readable steps, while recursive CTEs support sequences and hierarchical processing. Subqueries allow one query to use the results of another for filtering, calculations, and comparisons.

Working with the hospital dataset demonstrates how these concepts can answer practical questions about patient visits, doctor activity, diagnoses, department billing, and repeat visits.

Understanding the problem before choosing a SQL technique is essential. With practice, these concepts can help you write queries that are clearer and more maintainable.

From Raw Data to Business Insights: Building a JCars Logistics Power BI Dashboard

Victoria Ndei — Wed, 30 Sep 2026 12:40:21 +0000

Introduction

Business Intelligence is not simply about creating attractive dashboards. A reliable dashboard starts with understanding the data, identifying quality problems, applying appropriate transformations, building a suitable analytical model, and developing calculations that answer meaningful business questions.

For this project, I worked with the JCars Logistics vehicle sales and operations dataset to develop an interactive Power BI solution for analysing sales performance, profitability, customers, vehicles, branches, logistics operations, and unusual business patterns.

The project followed a complete BI workflow:

Raw Data → Data Investigation → Cleaning & Transformation → Data Modelling → DAX → Dashboard Development → Analysis → Insights → Recommendations

The goal was to transform a raw and inconsistent dataset into a structured analytical solution that could support management-level decision-making.

In this article, I will walk through the problems I encountered in the raw data, how I cleaned and prepared it, how I designed the data model, how I developed the DAX calculations, and how the final report was designed to turn the data into useful business information.

Receiving and Understanding the Raw Dataset

The project started with a raw CSV dataset containing vehicle sales, customer, financial, operational, and location-related information.

At first, the dataset looked like a straightforward table that could be imported directly into Power BI. However, before building any visuals, I needed to determine whether the data was actually reliable enough for analysis.

This was an important step because errors in the source data could eventually appear as incorrect KPIs, misleading comparisons, or inaccurate business conclusions.

What I Investigated

I performed a data-quality investigation covering several areas:

Missing and blank values
Null values
Duplicate or repeated records
Invalid numerical values
Negative values
Text appearing in numerical fields
Inconsistent currency symbols and labels
Values containing K and M suffixes
Invalid and inconsistent dates
Placeholder values such as - and TBD.
Ratings outside the expected range
Inconsistent discount formats
Inconsistent categorical values

Rather than assuming that every unusual value was an error, I considered the meaning of each field and determined an appropriate treatment.

For example, a negative value could indicate a genuine business adjustment or could be a data-entry or scraping issue. Automatically deleting such records could therefore remove potentially useful information.

Why the Investigation Mattered

The investigation established that data preparation needed to happen before the analytical model was built.

This changed the workflow from simply:

Import → Visualize

to:

Investigate → Clean → Validate → Model → Analyze

That distinction became one of the most important lessons from the project: a dashboard is only as reliable as the data and business rules behind it.

Data Cleaning and Transformation with Power Query

After identifying the data-quality issues, I moved into the cleaning and transformation stage using Power Query in Power BI.

The objective was not simply to make the dataset look cleaner. Each transformation needed to make the data more consistent and suitable for analysis.

Main Cleaning Steps

The cleaning process included:

Promoting the correct row to column headers.
Assigning appropriate data types to the columns.
Cleaning text fields and removing unnecessary spaces.
Handling blank, null, and placeholder values.
Cleaning numerical fields that contained text or symbols.
Standardizing monetary fields.
Cleaning and standardizing discounts.
Standardizing customer ratings.
Cleaning and standardizing dates.
Handling negative values according to the documented business rules.
Creating the keys required for the analytical model.
Preparing the fact and dimension tables.
Validating the cleaned data before continuing to modelling.

Handling Missing Values

Some fields contained missing information. Instead of leaving these values untreated, I applied documented business rules where a reasonable default was appropriate.

Examples included:

Missing discount → 0
Missing units sold → 1
Missing review count → 0
Missing delivery fee → 0
Missing logistics cost → 0
Missing customer rating → 3
Missing customer age → 35
Missing vehicle year → 2022
Missing order ID → UNKNOWN

These assumptions were documented so that the analytical results could be interpreted in the context of how incomplete records had been treated.

Cleaning Negative Values

Negative values required additional consideration because their meaning depends on the business field.

Where a negative value represented a value that should logically be non-negative, the data was cleaned according to the defined business rule. I avoided making arbitrary changes where the original business meaning could not be established.

This helped maintain a balance between data correction and preservation of potentially meaningful records.

Validation

After the transformation steps, I checked the cleaned data to confirm that:

Numerical columns contained appropriate numerical values.
Dates were consistently formatted.
Categories were standardized.
Missing values had been handled according to the documented rules.
The resulting data could be loaded into the analytical model.

The validation stage was important because cleaning data without checking the results can introduce new errors while attempting to fix old ones.

Currency Handling and Business Assumptions

One of the more challenging parts of preparing the dataset was handling monetary values.

The raw data contained different currency symbols, currency labels, and numerical representations. Some values also used suffixes such as K and M.

Because financial calculations depend heavily on consistent units, currency handling had to be addressed before building the final measures.

Currency Standardisation

The final reporting currency for the project is Kenyan Shillings (KES).

During Power Query transformation, currency symbols and textual indicators were cleaned so that monetary fields could be used consistently in calculations.

Values using K and M suffixes were interpreted according to their numerical meaning.

Where foreign-currency labels appeared, but there was not enough reliable information to establish and apply a defensible exchange rate, I did not introduce an unsupported conversion.

This was an important modelling decision because applying an assumed exchange rate without sufficient evidence could create a different type of data-quality problem.

Revenue Calculation

The project used the following business rule for revenue:

Revenue = Units Sold × Unit Selling Price × (1 − Discount) + Delivery Fee

This calculation was implemented in the analytical layer so that revenue could be consistently analysed across the report.

Other Assumptions

Several additional assumptions were required when dealing with incomplete records.

For example, missing discounts were treated as zero, while missing delivery and logistics fees were treated as zero. Missing ratings were assigned a neutral value of 3.

These decisions were documented rather than hidden inside the transformation process.

Why Documentation Matters

An important lesson from this stage was that data cleaning is not purely technical.

When the source data does not provide complete information, the analyst has to make decisions about how the data should be interpreted. Those decisions become part of the analytical methodology and therefore need to be transparent.

The assumptions used in this project are documented in the accompanying project documentation in the GitHub repository.

Building the Analytical Data Model

Once the data had been cleaned and validated, I moved from data preparation into data modelling.

The raw dataset was originally structured as a large flat table. Although a flat table can be useful for initial investigation, it is not the most suitable structure for a scalable analytical report.

I therefore created a star-schema data model consisting of a central fact table surrounded by descriptive dimension tables.

_Figure 1 illustrates how the dataset looked after cleaning. _

Fact Table

The central table is FactSales.

Figure 2.1 illustrates the FactSales Table

It contains the transaction-level information required for quantitative analysis, including measures related to sales, revenue, costs, discounts, delivery, logistics, ratings, and other transaction indicators.

Dimension Tables

I created the following dimensions:

Dim Vehicle

Contains descriptive vehicle attributes such as:

Vehicle Key
Car Make
Car Model
Vehicle Type
Vehicle Year
Fuel Type
Transmission
Colour

Dim Customer

Contains customer-related descriptive information used to analyse customer activity and performance.

Dim Location

Contains branch and location information used for geographical and branch-level analysis.

Dim Date

The Date dimension was created from the Order Date and contains:

Date
Date Key
Month
Year

Relationships

The dimension tables connect to FactSales using one-to-many relationships, with the dimension acting as the "one" side and FactSales as the "many" side.

This allows users to filter transactions by dimensions such as vehicle, customer, location, and date while keeping the model structured.

Why a Star Schema?

The star schema provided several advantages:

Clear separation between descriptive attributes and transaction data
Simpler DAX calculations
Better filtering behaviour
Easier report maintenance
Improved analytical flexibility
A structure that is easier to understand and extend

The modelling stage therefore transformed the cleaned dataset into a structure designed specifically for business analysis rather than simply reproducing the source layout.

Developing DAX Measures

With the data model in place, the next stage was developing the DAX measures required for analysis.

Instead of relying only on implicit aggregations, I created dedicated measures so that the business calculations could be reused consistently across different report pages and visuals.

Sales and Revenue Measures

The model includes measures for:

Total Revenue
Units Sold
Total Orders
Average Order Value
Average Selling Price

These measures support the high-level sales analysis as well as more detailed comparisons by vehicle, branch, location, payment method, and time.

Profitability Measures

To analyse value generation, I developed measures covering:

Total Cost
Gross Profit
Profit Margin
Profit Per Vehicle

Looking at these measures together makes it possible to distinguish sales volume from actual financial contribution.

Customer and Operational Measures

Additional measures were developed for areas such as:

Total Customers
Customer activity
Review counts
Customer ratings
Delivery fees
Logistics costs
Returns
Cancellations

These measures extend the analysis beyond sales and help provide an operational and customer-experience perspective.

Discounts and Payment Methods

I also created measures to support discount and payment analysis, including average discount and revenue by payment method.

This allowed discounts and payment channels to be analysed alongside financial performance rather than in isolation.

Ranking and Time Analysis

Ranking measures were developed to compare vehicles and branches across relevant performance indicators.

Time-based measures were also used to analyse changes across years and months using the Date dimension.

Why Measures Instead of Only Calculated Columns?

A key part of the DAX development process was understanding when a measure was more appropriate than a calculated column.

Measures are evaluated according to the current filter context. This makes them particularly useful for dashboards where the same calculation needs to respond dynamically to slicers, page filters, and visual selections.

For example, Total Revenue can automatically recalculate when a user filters the report to a particular branch, vehicle type, year, or payment method.

This made the DAX layer an important part of creating an interactive analytical report rather than a collection of static calculations.

Designing the Power BI Dashboard and Report

After completing the data preparation, modelling, and DAX development, I moved to the report-development stage.

The main design objective was to avoid creating a collection of unrelated charts. Instead, I organized the report around different levels of business analysis, starting with an executive overview and moving into detailed investigation.

Executive Dashboard

The first page was designed as the management entry point.

It provides a high-level view of important KPIs and business performance indicators so that a user can quickly understand the overall position before moving into deeper analysis.

The dashboard focuses on measures such as:

Revenue
Profitability
Units
Orders
Customers
Operational indicators

Sales & Analysis

The second page provides more detailed sales analysis.

Users can explore performance across dimensions such as:

Vehicle type
Car make
Car model
Branch
Location
Payment method
Time

This page moves from the overall picture into the factors contributing to sales performance.

Profitability & Branches

The profitability page focuses on financial performance and branch comparisons.

The intention was to allow management to examine revenue and profitability together rather than treating sales volume as the only indicator of performance.

Branch Detail

A dedicated Branch Detail page was created to provide deeper analysis of individual branches.

I also implemented drill-through functionality, allowing users to move from broader analysis into branch-specific details.

This reduced the need to place every possible detail on the main dashboard.

Operations & Customer Experience

This page examines the operational and customer side of the business.

It includes analysis related to:

Delivery
Logistics
Returns
Cancellations
Ratings
Reviews
Customer activity

This provides a different perspective from purely financial reporting.

Investigations & Exceptions

The investigations page was designed to identify unusual records and business patterns that could require further attention.

The purpose was not to automatically label every unusual observation as an error, but to provide a structured way of identifying areas for investigation.

Management Insights & Recommendations

The final page translates the analysis into management-focused insights and recommendations.

This helps bridge the gap between technical Power BI analysis and practical business decision-making.

Interactivity and Navigation

The report includes slicers for dimensions such as:

Year
Month
Branch
Location
Vehicle Type
Car Make
Car Model
Payment Method

The visuals respond to selections through Power BI's filtering and cross-filtering behaviour.

The report also includes:

Page navigation
Drill-through
A dedicated branch tooltip
Interactive visual filtering
Dynamic DAX measures

These features allow users to explore the data rather than simply read a static report.

Why the Report Was Structured This Way

The report was intentionally organised from summary to detail.

A management user can begin with the Executive Dashboard, identify an area that requires attention, move to a more detailed analytical page, and then use filtering or drill-through to investigate the underlying business area.

This structure helped keep the main dashboard focused while still providing access to detailed analysis.

Turning the Dashboard into Business Analysis

Once the report was complete, I used the model and dashboard to investigate business questions rather than simply observing the visuals.

The analysis covered several areas of the JCars Logistics business.

Sales Performance

I examined how revenue, units, and orders varied across:

Time
Branches
Locations
Vehicle types
Makes and models
Payment methods

This provided a way to move from overall sales performance into the specific business dimensions contributing to it.

Profitability

Revenue alone does not provide a complete picture of business performance.

I therefore compared revenue with costs, gross profit, profit margin, and profit per vehicle.

This helped identify areas where sales activity and financial contribution needed to be considered together.

Vehicle Performance

Vehicle-level analysis was used to investigate whether different vehicle types, makes, models, and vehicle characteristics showed different levels of sales and profitability.

This can support future decisions around inventory, sales focus, and vehicle-level performance monitoring.

Branch and Location Performance

Branch analysis was used to compare performance across locations.

Rather than relying on a single KPI, I considered multiple measures including revenue, profitability, units, and operational indicators.

This provides a more complete view of branch performance.

Customer and Operational Experience

Customer ratings, reviews, returns, cancellations, delivery, and logistics were analysed alongside financial measures.

This allowed operational performance to be considered as part of the overall business picture.

Discounts and Payment Methods

Discounts were investigated in relation to sales and profitability, while payment methods were analysed based on their contribution to revenue.

This helped answer questions about how different commercial activities relate to financial performance.

Investigating Exceptions

The report also included an investigation section for unusual records and patterns.

An important principle was to distinguish between:

“This record is unusual”

and:

“This record is definitely wrong.” **

An unusual observation may represent a genuine transaction, a data-quality issue, or a business situation requiring further investigation. The dashboard therefore supports investigation rather than making unsupported causal claims.

Analyst-Defined Questions

In addition to the required management questions, I developed additional questions to explore the dataset more deeply.

These included:

Which vehicle categories contribute to sales activity?
How does profitability vary across branches?
How do discounts relate to profitability?
How does revenue vary by payment method?
Which vehicle-level patterns require further investigation?
What operational patterns exist around returns and cancellations?
How does customer activity relate to business performance?
How does performance change over time?
Which unusual records deserve additional investigation?
Which combinations of business dimensions reveal useful performance differences?

These questions helped guide the choice of measures and visuals instead of creating visuals first and searching for a purpose afterwards.

Key Insights from the Analysis

The completed analysis produced several important observations about how the JCars Logistics business can be evaluated.

1. Revenue and Profitability Need to Be Viewed Together

One of the main analytical lessons was that revenue should not be treated as the only measure of performance.

A business area can generate substantial sales activity while having a different profitability profile after considering costs, discounts, delivery, and logistics.

For this reason, the report presents revenue alongside gross profit, profit margin, and profit per vehicle.

2. Branch Performance Requires Multiple KPIs

Branch performance cannot be fully understood from a single metric.

The report allows branch performance to be examined using financial, sales, customer, and operational indicators.

This makes it possible to identify areas where a branch may require further investigation without relying on one isolated KPI.

3. Vehicle-Level Analysis Adds Important Detail

Overall sales figures can hide differences between individual vehicle categories, makes, and models.

Vehicle-level analysis therefore provides additional context for understanding which parts of the vehicle portfolio are contributing to sales and profitability.

4. Discounts Can Affect Value Generation

Discounts are useful commercial tools, but their impact needs to be considered alongside profitability.

The report therefore allows discount levels to be analysed together with financial performance instead of evaluating discounts based only on transaction activity.

5. Operational Metrics Add Context to Financial Results

Delivery fees, logistics costs, returns, cancellations, ratings, and reviews provide additional information about business operations and customer experience.

These indicators can help management identify areas that deserve further investigation even when the financial results alone do not immediately reveal the issue.

6. Data Quality Is Part of the Analysis

The project demonstrated that data quality is not a separate activity that ends before analysis begins.

Currency inconsistencies, missing values, invalid dates, inconsistent formats, and unusual records can directly affect KPIs and business conclusions.

The cleaning and validation stages were therefore essential to the reliability of the final report.

7. Exceptions Should Be Investigated, Not Automatically Removed

Some records appeared unusual and required additional attention.

Rather than automatically deleting unusual observations, the analytical approach was to investigate them within the context of the available data.

This reduces the risk of removing legitimate business activity simply because it differs from the expected pattern.

Management Recommendations

The analysis provides several areas where management can use the Power BI solution to support ongoing decision-making.

1. Monitor Profitability Alongside Sales

Management should review revenue together with total cost, gross profit, profit margin, and profit per vehicle.

This provides a more complete picture of value generation than monitoring sales volume alone.

2. Review Branch Performance Regularly

Branch performance should be monitored using multiple KPIs rather than a single measure.

Regular review of revenue, profitability, sales activity, and operational indicators can help identify branches or locations that require additional investigation.

3. Evaluate the Effectiveness of Discounts

Discounts should be evaluated alongside profitability and sales performance.

This can help management understand whether discounting is supporting the desired business outcome while maintaining an appropriate level of value generation.

4. Investigate Operational Exceptions

Returns, cancellations, delivery-related measures, logistics costs, and unusual records should be monitored regularly.

Where unexpected patterns appear, management can investigate the underlying transactions or processes before deciding on corrective action.

5. Use Vehicle-Level Analysis for Planning

Vehicle make, model, type, year, and related performance indicators can provide useful information for sales and inventory planning.

The dashboard can be used to identify changing performance patterns and support future vehicle-related decisions.

6. Maintain Data-Quality Controls

The project showed how inconsistent source data can affect downstream analysis.

Future data collection processes should therefore include validation rules for:

Currency
Dates
Numerical values
Ratings
Discounts
Missing fields
Categorical values

Improving data quality at the source can reduce the amount of corrective work required during later BI development.

Conclusion

The JCars Logistics project provided an end-to-end experience of developing a Business Intelligence solution from a raw dataset.

The project began with data-quality investigation and progressed through Power Query cleaning, currency handling, assumptions, data modelling, DAX development, dashboard design, analysis, and management recommendations.

The most important lesson was that successful BI development is not only about producing a dashboard. It is about creating a reliable chain from raw data to evidence-based business understanding.

The final Power BI solution combines an analytical star schema, reusable DAX measures, interactive report pages, drill-through functionality, tooltips, and investigation-focused analysis.

By combining technical data preparation with business-oriented analysis, the project demonstrates how Power BI can transform raw operational data into a practical decision-support tool.
https://github.com/4688-tori/Jcars-Logistics-PowerBI

Data Modelling, Relationships & Joins

Victoria Ndei — Mon, 14 Sep 2026 07:49:58 +0000

Introduction

Power BI helps organizations turn raw data into meaningful insights for better decision-making. A well-designed data model is essential for accurate analysis, efficient DAX calculations, and good report performance. This article explains data modelling, fact and dimension tables, relationships, filter directions, and Power Query joins, using practical examples to demonstrate how these concepts are applied when building an effective Power BI solution.

1. Data Modelling

What is data modelling?

Data modelling in Power BI is the process of organizing data from one or more sources into tables and defining the relationships between those tables so that the data can be analyzed efficiently.

A well-designed data model provides the foundation for accurate Power BI reports and dashboards. Instead of keeping all information in one large table, you can separate related information into logical tables and connect them using keys.

For example, in an agricultural business, crop production records could be stored in a central fact table while information about farmers, crops, locations, and seasons is stored in separate dimension tables.

A good data model is important because it:
Improves reporting and analytical accuracy.
Makes DAX calculations easier to write and understand.
Improves Power BI performance by reducing unnecessary data duplication.
Makes models easier to scale when new data is added.
Improves maintainability because each table has a clear purpose.
Makes relationships and filter propagation easier to understand.

There are three common approaches to structuring data: Flat Table, Star Schema, and Snowflake Schema.

1.2 Flat Table

A flat table stores all information in a single table. Instead of separating customers, products, locations, and transactions into different tables, all the attributes and business-event information are stored together.

Structure

For example, an agricultural dataset could look like this:

Illustration

Figure 1.1- shows a flat table

Advantages

Simple to understand.
Easy to import and begin analyzing.
Requires few or no relationships.
Suitable for small datasets and simple reports.

Disadvantages

Creates data redundancy because descriptive information is repeated.
Can make the dataset unnecessarily large.
Changes to descriptive information may need to be repeated across many rows.
Can make complex DAX calculations and modelling more difficult.
Less scalable as the dataset grows.

When is it appropriate?
A flat table can be appropriate when working with a small, simple dataset, especially for basic analysis where the data does not need to be reused across multiple business processes.

Power BI implications
Flat tables are initially simple, but large flat tables can contain substantial redundancy. This can increase model size and make the model harder to maintain as more business requirements are introduced.

1.3 Star Schema

A star schema separates the data into a central fact table and several surrounding dimension tables.
The fact table contains measurable business events, while dimension tables contain descriptive information used to analyze those events.

Structure

For the agricultural example:
Advantages

Excellent for Power BI reporting and dashboards.
Reduces data redundancy.
Makes DAX measures easier to understand.
Relationships are generally straightforward.
Supports efficient filter propagation.
Easier to maintain and extend.
Provides a clear and intuitive model structure.

Disadvantages

Requires more planning than a flat table.
Requires relationships between tables.
Users need to understand facts, dimensions, and keys.
Creating the model can initially take more time.

When is it appropriate?
Star schemas are particularly appropriate for business intelligence, reporting, and analytical systems where users need to analyze measures by different dimensions.

For example:

Total Profit by Crop, County, Farmer, and Season

can be easily analyzed using a star schema.

Power BI implications
Star schemas are generally preferred in Power BI because they provide simple relationship paths, efficient filtering, and a clear separation between descriptive attributes and measurable events.

1.4 Snowflake Schema

A snowflake schema is an extension of the star schema in which dimension tables are further divided into related tables.
For example, instead of storing the county and region directly inside DimLocation, location information could be separated:
Advantages

Reduces repeated information within dimensions.
Can be useful when dimensions contain complex hierarchies.
Can provide greater control over highly structured data.
May reduce redundancy in some large and complex datasets.

Disadvantages

Creates more tables.
Creates more relationships.
Makes filter propagation more complicated.
Can make DAX and report development less intuitive.
Increases model complexity.

When is it appropriate?
A snowflake schema may be appropriate when dimensions are large, hierarchical, or naturally normalized.

For example, a multinational agricultural organization could have:

Country → Region → County → Location

and maintaining these levels separately may be useful.

Power BI implications
Although a snowflake schema can reduce redundancy, it introduces additional relationships and filter paths. For many Power BI analytical models, a well-designed star schema is simpler and easier to work with.

2 Fact Table and Dimensions

A well-designed Power BI data model usually separates business events from the descriptive information used to analyze those events. This is where fact tables and dimension tables become important.

Fact Tables

A fact table stores measurable business events or transactions. It normally contains numeric values that can be aggregated, together with foreign keys that connect the facts to descriptive dimension tables.
The table contains business events such as sales transactions. Quantity and SalesAmount are measures that can be aggregated, while CustomerID, ProductID, and DateID identify the dimensions associated with each sale.

Dimension Tables

A dimension table contains descriptive information about the entities involved in business events. Dimensions provide the context used to analyze facts.

Examples include:

DimCustomer – customer name, gender, segment, and location.
DimProduct – product name, category, brand, and price.
DimDate – date, month, quarter, and year.
DimLocation – county, region, town, or country. For example: The distinction can therefore be summarized as:

Understanding Grain

One of the most important concepts when designing a fact table is its grain, or granularity.
The grain defines exactly what one row in the fact table represents.
For example, if the grain of FactSales is:
"One row represents one product sold in one sales transaction."
then every row should represent one product line within a transaction.
Clearly defining the grain prevents problems such as duplicated transactions and incorrect calculations.

3. Relationships in Power BI

A relationship connects tables in a Power BI data model. Relationships are necessary because business data is often distributed across multiple tables rather than stored in one large table.
For example, FactSales may contain CustomerID, while DimCustomer stores customer names and customer segments.
This relationship lets Power BI connect the two tables and use customer information when analyzing sales.

Primary Keys and Foreign Keys

A primary key uniquely identifies a record in a table.
For example, CustomerID can be the primary key in DimCustomer:
Each CustomerID should occur only once.

The same CustomerID can appear multiple times in FactSales because one customer can make many purchases:

Each CustomerID should occur only once.
A one-to-many relationship means one record in one table can relate to many records in another table.
For example:

|DimCustomer | |FactSales|

|CustomerID | | CustomerID|
C001 C001
C001
C001

DimCustomer[CustomerID] contains unique values, while FactSales[CustomerID] can contain repeated values.

This is the most common relationship type in a star schema.
Use it when a dimension contains unique entities and a fact table contains multiple business events associated with those entities.

3.2 One-to-One Relationship (1:1)

A one-to-one relationship means that each record in one table corresponds to only one record in another table.
For example:
EmployeeID EmployeeDetails
E001 ────> E001
E002 ────> E002
E003 ────> E003

This relationship may be appropriate when information about the same entity has intentionally been split into two tables.
However, a 1:1 relationship should not normally be used simply because two tables can be connected by a column. If the tables represent the same entity and there is no strong modelling reason to separate them, combining them may be simpler.

3.3 Many-to-Many Relationship (:)

A many-to-many relationship occurs when multiple records in one table can be related to multiple records in another table.
For example, students can enrol in multiple courses, while each course can have multiple students.

Students Courses

Student A ───────────────> Course 1
Student A ───────────────> Course 2
Student B ───────────────> Course 1
Student C ───────────────> Course 2

In a proper dimensional model, a bridge or factless fact table can often be introduced to resolve this situation.
Many-to-many relationships should be used carefully because they can make filter
propagation and DAX calculations more difficult to understand.

Cardinality
Cardinality describes how records in two related tables correspond to each other.
The main cardinalities in Power BI are:

1:* — One-to-Many
1:1 — One-to-One
: — Many-to-Many For most analytical models, 1:* relationships are preferred because they provide a clear dimension-to-fact structure.

Referential Integrity
Referential integrity means that foreign-key values should correspond to valid primary-key values in the related table.
For example, if FactSales contains:

CustomerID = C005

then C005 should normally exist in DimCustomer.
Missing dimension records can create unmatched or blank members in reports and may indicate data-quality problems.

Active and Inactive Relationships
Power BI can have both active and inactive relationships.
An active relationship is used automatically when filters move between related tables.
An inactive relationship is not automatically used but can be activated in a DAX calculation when required.
A common example is a sales table containing multiple date columns:

FactSales
---------
OrderDate
ShipDate
DeliveryDate

A date dimension may therefore have multiple relationships to the fact table. Only one can normally be active for the same pair of tables, while others can remain inactive and be activated in specific calculations.

4. Filter Direction

Relationships do more than connect tables. They also determine how filters propagate through the model.

Single-Direction Filtering

In a typical star schema, filtering flows from the dimension table to the fact table.
For example:

DimProduct
│
│ Filter
▼
FactSales

Suppose DimProduct contains:

If a user selects Electronics in a Power BI report, the filter is passed from DimProduct to FactSales.
Power BI then calculates sales using only the transactions associated with products in the Electronics category.
This is the preferred direction for many star-schema models because it is predictable and easy to understand.

Bidirectional Filtering

With bidirectional filtering, filters can move in both directions:
DimProduct
↕
FactSales

Although bidirectional filtering can solve some modelling scenarios, it should be used carefully.
It can create:

Ambiguous filter paths

Unexpected filtering behaviour
More complex DAX calculations
Performance problems in complex models
Difficulty understanding why a visual is being filtered

For example, if several dimension tables can reach each other through multiple paths, Power BI may have more than one possible route through which a filter can travel.
Therefore, single-direction filtering from dimensions to facts is generally preferable in a standard star schema.

_Figure 4.1 single-direction filtering versus bidirectional filtering between DimProduct and FactSales _

5. Joins in Power Query

Relationships are not the only way to work with multiple tables. Power Query provides Merge Queries, which allow tables to be joined during the data transformation stage.
A join matches records from two tables using one or more common columns.
Consider these two tables.

The tables can be merged using CustomerID.

5.1 Left Outer Join

A Left Outer Join keeps every record from the left table and matching records from the right table.
If Customers is the left table:

Carol remains in the result even though she has no matching order.
Use case: Find all customers and show their orders where available.

5.2 Right Outer Join

A Right Outer Join keeps every record from the right table and matching records from the left table.

Order O004 remains even though customer C004 does not exist in the Customers table.
Use case: Keep all orders and add customer information when available.

5.3 Full Outer Join

A Full Outer Join keeps all records from both tables, whether or not they have a match.
The result would include:

This is useful when you need to identify both:

Customers without orders
Orders without valid customers It can therefore be particularly useful for data-quality analysis.

5.4 Inner Join

An Inner Join keeps only records that exist in both tables.
Customers without orders and orders without matching customers are excluded.

Use case: Analyse only orders associated with valid customers.

5.5 Left Anti Join

A Left Anti Join returns records that exist in the left table but have no matching record in the right table.
Using Customers as the left table:

This identifies customers who have not placed an order.
Use case: Finding inactive customers or records missing from another dataset.

5.6 Right Anti Join

A Right Anti Join returns records that exist in the right table but have no matching record in the left table.
Using Orders as the right table:

This identifies orders whose customer does not exist in the Customers table.
Use case: Detecting invalid foreign keys or data-quality issues

_ Figure 5.3 -illustrates the summary of different joins _

6. Power Query Joins vs Power BI Relationships

Although both joins and relationships connect tables, they serve different purposes and operate at different stages of the Power BI workflow.

Power Query Merge

A Merge Query is a data-transformation operation.
It occurs before the data is loaded into the Power BI model.
For example:
Customers
+
Orders
↓
Power Query Merge
↓
Combined Table

The merge can bring columns from one table into another.
For example, an Orders table could be merged with Customers to add:
CustomerName CustomerSegment County
The result is physically represented as a combined query/table when loaded into the model.

Power BI Relationship

A relationship is created after the tables are loaded into the data model.
For example:
DimCustomer
│
│ 1:*
▼
FactSales

The relationship does not physically combine the tables.
Instead, it tells Power BI how the tables are connected so that filters and calculations can work across them.

Key Difference

When Should You Use a Merge?
Tables, Relationships, Filter Direction and Joins in Power BI
A merge is appropriate when the columns are genuinely required in the same table.
For example, if a source system stores product information separately but a transformation requires the product category to be included in a staging table, merging may be appropriate.

When Should You Use a Relationship?

A relationship is preferable when the tables represent different business entities.

For example:

DimProduct
│
▼
FactSales

There is usually no need to copy product name, category, and brand into every sales transaction.
Keeping them in DimProduct reduces data redundancy and preserves a clean dimensional model.

Problems with Excessive Merging

Excessive merging can create a very wide table containing repeated descriptive information.
For example, instead of:
DimProduct ProductID ProductName Category Brand
and:
FactSales SalesID ProductID Quantity Quantity SalesAmount
a merge could produce:
SalesID ProductID ProductName Category Brand Quantity SalesAmount
If thousands or millions of transactions contain the same product information, the data becomes unnecessarily repetitive.
This can increase model size, reduce readability, and make maintenance more difficult.
For business intelligence solutions, keeping fact and dimension tables separate is often preferable because it supports a cleaner and more scalable model.

7. Recommended Power BI Model

For a typical business intelligence project, I would recommend a star schema rather than a flat table or a highly normalized snowflake schema.
A typical model could look like this:
graph TD
`
Customer["DimCustomer"]
Product["DimProduct"]
Date["DimDate"]
Location["DimLocation"]
Sales["FactSales"]

Customer -->|1:*| Sales
Product -->|1:*| Sales
Date -->|1:*| Sales
Location -->|1:*| Sales

`
The fact table contains measurable business events, while dimensions contain descriptive attributes.
Why Choose a Star Schema?

1. Performance

Star schemas generally provide an efficient structure for analytical queries because dimensions are separated from large transaction tables and relationships are straightforward.

2. Simpler DAX

Clear relationships make DAX calculations easier to understand.
For example, a measure such as:
Total Sales = SUM(FactSales[SalesAmount])
can be analyzed by product, customer, location, or date through the relationships in the model.

Model Readability A star schema is visually easy to understand. Developers and report users can quickly identify:
Which table contains transactions
Which tables contain descriptive attributes
How the tables are connected ###4. Scalability As the business grows, additional dimensions can be added without redesigning the entire model. For example:

             DimCustomer
                  |
DimProduct — FactSales — DimLocation
                  |
               DimDate

A future DimSalesperson or DimPromotion could also be connected to the fact table.

5. Reduced Redundancy

Product and customer information does not need to be repeated for every transaction.
Instead of storing:

Laptop | Electronics | HP | 500 times

the information can be stored once in DimProduct and referenced through ProductID.

6. Maintainability

If a product category changes, it can be updated in the dimension rather than repeatedly changing the same information across thousands of fact records.

7. Easier Reporting

Report users can drag attributes such as:

DimProduct[Category]
DimDate[Year]
DimLocation[County]

`
and measures such as:

[Total Sales]

into visualizations without needing to understand complex joins.

Recommended Relationships

For a standard star schema, I would normally implement:

One-to-many (1:*) relationships
Dimension table on the 1 side
Fact table on the *side
Single-direction filtering from dimensions to facts
Unique primary keys in dimension tables
Matching foreign keys in fact tables
Clearly defined fact-table grain
Active relationships for the main analytical paths Many-to-many relationships should only be introduced when the business requirement genuinely requires them, and bridge tables should be considered where appropriate.

Star Schema vs Flat Table vs Snowflake Schema

A flat table can be useful for small or simple datasets, but it can become inefficient and difficult to maintain as the amount of data grows.
A snowflake schema normalizes dimensions into additional related tables. This can reduce redundancy further, but it introduces additional relationships and complexity.
For most Power BI analytical models, the star schema provides the best balance between performance, simplicity, scalability, and maintainability.

Final Recommendation

My preferred Power BI model would therefore be a star schema with a central fact table surrounded by dimension tables. I would use one-to-many relationships wherever possible, with dimensions on the 1 side and facts on the * side.
I would also use single-direction filter propagation from dimensions to facts unless there is a specific modelling requirement for bidirectional filtering.
This approach produces a model that is easier to understand, easier to maintain, and easier to use when developing DAX measures and reports. More importantly, it separates business events from descriptive context, allowing the model to scale as the organization's reporting requirements grow.

A good Power BI model is therefore not simply about connecting tables. It is about designing those connections so that the resulting model is accurate, performant, understandable, and useful for business analysis.

Building an Interactive Excel Dashboard For E-commerce for Product Analysis: A Case Study for Jumia Products

Victoria Ndei — Sun, 06 Sep 2026 19:32:17 +0000

Introduction

E-commerce platforms generate large amounts of product information that can help explain pricing strategies, customer engagement, and product performance.

For this project, I analyzed a dataset of products from Jumia, an e-commerce marketplace, using Microsoft Excel. The goal was to transform raw product data into a cleaned dataset, perform descriptive and relationship analysis, and develop an interactive dashboard that communicates the most important findings.
The analysis focused on four major areas:

Product pricing
Discounts
Customer reviews
Product rating
The final result was an Excel-based analytical dashboard designed to help sellers identify high-performing products, products requiring attention, and potential pricing and marketing strategies.

Project Objectives

The goal of this project is to create an interactive Excel dashboard that provides insights into the
performance of products listed on Jumia and is capable of answering the following questions:
What is the average product price?
What is the average discount?
Which products have the highest ratings?
Which products have the highest number of reviews?
Do higher discounts result in higher customer engagement?
Are highly rated products more expensive?
Which products are performing well?
Which products may require improved pricing or product strategies?
What recommendations can be made to Jumia sellers?

Dataset Description

The dataset contains information about Jumia products
using the following fields:
Product:product name
Current Price:Current selling price
Old Price:Original/listed price
Discount:Percentage discount
Review:Number of customer reviews
Rating:Customer rating out of 5
The original dataset contained 115 records.
After data cleaning and duplicate removal, the final dataset contained 112 products

Raw Data

The first step was to inspect the raw dataset before making any changes.

The initial audit looked for:

Missing values
Duplicate records
Incorrect data types
Negative review counts
Invalid ratings
Invalid discounts
Price inconsistencies

Data Cleaning

Data cleaning was important because raw e-commerce data is not always immediately suitable for analysis.
For my data cleaning, I used Power Query, which made the cleaning process much easier.

Removing Duplicate Records

Duplicate records can cause products to be counted more than once and can distort totals and averages.
The dataset contained three duplicate records. These duplicates were removed, leaving 112 unique records.

Cleaning Review Counts

Some review values were negative.
A review count cannot logically be negative, so these values were converted to their absolute values.
Blank review values were retained as missing values rather than automatically being treated as zero.
This distinction is important because a missing review count does not necessarily mean that the product received zero reviews.

Cleaning Ratings

Ratings were originally stored in text formats such as:
4.5 out of 5
These values were converted into numerical ratings so that averages, sorting, and correlations could be calculated.

Cleaning Discounts

Discount values were originally represented as percentages, such as:
38%
These were converted into numerical percentage values for analysis.

Cleaning Prices

Currency text was removed from the price fields so that prices could be treated as numerical values.
One product contained a price range rather than a single price. A midpoint was used for the numerical analysis so that the record could be included in price calculations.
Also, we removed the 'KSh' and ',' before changing prices to whole numbers.

Data Enrichment

After cleaning the original fields, additional analytical columns were created.

Discount Amount

The discount amount was calculated using:

Discount Amount = Old Price - Current Price

This provides the actual monetary reduction rather than only the percentage reduction.

Rating Category

Products were categorized according to their rating performance.
The categories were used to make it easier to analyze the overall distribution of customer ratings.

Discount Category

Products were also grouped into discount categories:

Low
Medium
High These categories made it possible to compare product performance across different discount levels.

Price Category

Products were grouped into price ranges to make price analysis easier and to support interactive dashboard filtering.

Excel Techniques Used

Several Excel features were used throughout the project.

Excel Formulas

Formulas were used to create calculated fields and perform summary calculations.
For example, the discount amount was calculated by subtracting the current price from the old price.
Functions such as AVERAGE, SUM, COUNT, MAX, MIN, IF, and other conditional functions were useful for transforming and analyzing the dataset.

Sorting and Filtering

Sorting was used to identify:

Highest-rated products
Lowest-rated products
Products with the most reviews
Products with the highest discounts
Products requiring attention

Pivot Tables

Pivot Tables were used to summarize product performance by categories such as rating and discount levels.
They provided a flexible way to aggregate the data without manually calculating every summary.

Pivot Charts

Pivot Charts converted the Pivot Table results into visualizations suitable for the dashboard.

Slicers

Slicers were added to make the dashboard interactive.
Users can filter the dashboard based on variables such as:

Rating Category
Discount Category
Price Category

Data Analysis

1 Descriptive Analysis

After cleaning the data, the following statistics were obtained:

Trend Analysis

Relationship Between Discounts and Reviews

One of the main questions was whether larger discounts result in greater customer engagement.
Customer engagement was represented using the number of reviews.
The correlation between discount percentage and review count was approximately:
-0.14
This represents a very weak negative relationship.
Therefore, the analysis does not provide evidence that increasing the discount percentage automatically results in more customer reviews.
Some products with substantial discounts had very few reviews, while some products with moderate discounts had significantly more reviews.

Business Interpretation

Sellers should therefore avoid treating discounts as the only method of increasing engagement.
Product quality, value, visibility, presentation, and customer experience may also influence customer response.

Relationship Between Price and Rating

The correlation between current price and rating was approximately:
+0.11
This is a very weak positive relationship.
The result suggests that expensive products were not necessarily rated much higher than cheaper products.
For example, the Electronic Digital Display Vernier Caliper had a relatively low current price but achieved a rating of 4.6/5 alongside 49 reviews.

Business Interpretation

Price alone does not appear to determine customer satisfaction.
Sellers should focus on providing strong value for money rather than assuming that a higher selling price will result in higher ratings.

Relationship Between Ratings and Reviews

The correlation between rating and review count was approximately:
+0.06
This is effectively a negligible linear relationship.
A product can therefore have many reviews without necessarily having a high rating.
This is particularly important when evaluating product performance.
A high number of reviews indicates strong customer engagement, but it does not automatically indicate customer satisfaction.

Sales Performance Analysis

The following table summarizes the product performance analysis by:

Strong demand proxy: Which products have review counts above the engagement threshold?
Pricing/marketing attention: Which products combine weak engagement or weak ratings with high price/high discount?
Many reviews, average experience: Which products have high engagement but fall in the Average rating category? -Promotion inefficiency: Which high-discount products remain below the engagement threshold?

Building the Dashboard

The final stage of the project was creating an interactive Excel dashboard.
The dashboard was designed to provide a quick overview of product performance without requiring the user to inspect the raw dataset.

KPI Cards

The dashboard includes key performance indicators such as:

Total Products
Average Current Price
Average Discount
Average Rating
Total Reviews ##Product Performance Charts Charts were created to display:
Top 10 products by rating
Top 10 products by reviews
Top 10 products by discount ###Category Analysis The dashboard also presents the distribution of:
Rating categories -Discount categories -Price categories ###Interactive Slicers Slicers allow the user to filter the dashboard by selected product characteristics.

Screenshot: Pivot Tables

Insert screenshot of your Pivot Tables here.

Screenshot: Charts

Insert screenshot of your charts here.

Screenshot: Final Dashboard

Insert screenshot of your completed Dashboard here.

Key Business Insights

The analysis produced five major conclusions.

1. Large discounts do not guarantee engagement

The discount-review correlation was approximately -0.14, showing a very weak negative relationship.

2. Higher prices do not guarantee better ratings

The price-rating correlation was approximately +0.11.

3. Review volume and rating measure different aspects of

performance
Review count reflects customer engagement, while rating provides an indication of customer satisfaction.

4. High-performing products combine engagement and satisfaction

Products such as the Cake Decorating Tool Set and Vernier Caliper demonstrated strong performance through both review volume and rating.

5. Some heavily discounted products still perform poorly

High discounts cannot compensate for poor product quality or customer dissatisfaction.

Recommendations for Jumia Sellers

Based on the findings, sellers should consider the following strategies.

1. Do not rely entirely on discounts

Discounting should be part of a broader marketing strategy rather than the only method used to generate engagement.

2. Monitor customer ratings carefully

A product receiving many reviews but maintaining a low rating should be investigated immediately.

3. Prioritize high-rated and highly reviewed products

Products demonstrating both strong engagement and customer satisfaction should receive greater promotional attention.

4. Improve product quality

Products with consistently poor ratings should be reviewed for quality and functionality issues.

5. Improve product descriptions

Accurate product descriptions and specifications can help reduce differences between customer expectations and the actual product.

6. Use competitive pricing

The analysis indicates that higher prices do not automatically result in higher ratings. Sellers should therefore balance profitability with customer-perceived value.

7. Use data for decision-making

Instead of deciding on pricing and promotions based solely on intuition, sellers can use review counts, ratings, discounts, and prices together to identify products requiring action.

Conclusion

This project demonstrated how Microsoft Excel can be used to transform raw e-commerce data into an interactive business intelligence dashboard.
The analysis showed that product performance cannot be explained by discounts or prices alone.
The weak relationship between discounts and reviews indicates that larger discounts do not automatically produce higher engagement. Similarly, the weak relationship between price and ratings indicates that expensive products are not necessarily better rated.
The most useful approach is to evaluate multiple indicators together.
Products with high review counts and strong ratings represent stronger performers, while products with high engagement but poor ratings may require quality or customer-experience improvements.
Ultimately, the dashboard provides sellers with a practical way to identify opportunities, monitor product performance, and make more evidence-based pricing and marketing decisions.

Final Takeaway

The key lesson from this project is simple:
A large discount can attract attention, but customer satisfaction and product value are essential for sustainable product performance.

Getting Started With Excel For Data Analytics: From Basic Data to Cleaning

Victoria Ndei — Sat, 29 Aug 2026 20:47:00 +0000

Introduction

This week at Luxdevhq, our data analyst group, we dove into Excel, learning how to navigate it and use its tools as real analysts. Join me as I explain what we covered this week. But before we go any further, we must first understand what Excel is.

What is Excel

Excel is a spreadsheet software program designed by Microsoft that allows you to collect, organize, analyze, calculate, and visualize data efficiently. It allows users to create charts,and manage large datasets, making it one of the most widely used tools in data analysis and business reporting.

1 Excel Fundamentals

1.1 Workbooks, Worksheets and Cells

Excel file is called a workbook, and a workbook is made of one or more worksheets(often called sheets), visibly seen as tabs at the bottom of the screen. Each worksheet is a grid of cells arranged into columns labelled with letters A, B, C... and rows labelled with numbers:1,2,3...
Every cell has a unique address formed by its column and row, such as A2 or G12, and that address is what formulas use to refer to data elsewhere in the sheet.

1.2 Data Types

Numbers: plain quantities use in calculations e.g: 2, 56, 134
Texts: words or codes that Excel won't try to calculate e.g Nairobi
Dates: stored internally as serial numbers;1/12/2000
Formulas: expression begins with equals sign(=) that calculates result from other cells. e.g (=A2+C2)

The Ribbon and Navigation

The ribbon is the toolbar across the top that contains all commands organized into tabs (Home, Insert, Page Layout, Formulas, Data, Review) being the most commonly used.
Home tab covers formatting(fonts,colors,number formats),the formula tab gives access to the function library, and the Data tab is where most data-cleaning tools are found, including Removing Duplicates, Text to Columns, Data Validation, and Sort& Filter.

1.4 Basic Formulas and Functions

A formula is an expression that calculates the value of a cell. Formulas begin with an equals (=) sign and can include:

Constants (numbers like 100)
Cell references(like A1, B2)
Operations like (+, -, *, /)
Built-in functions (like SUM, AVERAGE)

FUNCTIONS
The following are some of the functions we went through this week:

=SUM() - adds a range of numbers
=AVERAGE() -calculates the mean of a range
=COUNT()/=COUNT() -counts numeric cells, or non-blank cells respectively.
=COUNTIFS() and =SUMIFS() -conditional counting and summing

2 Data Cleaning Exercise

This section we will walk through a realistic sample dataset: fifteen order records from a small online store selling electronics and furniture. This dataset was built in away that it contains different problems every analyst runs into early in a project; inconsistent capitalization, stray whitespace, mixed data formats, a duplicate record, missing values, and a data entry error

2.1 The Raw Data

Below is the "Raw Data" worksheet exactly as it may arrive from an export or a manual entry log. A few problems can be noted immediately, a few become obvious once formulas are applied to check for them:

2.2 Standardizing Text with TRIM, PROPER, and LOWER

These text functions mostly do most of the work when cleaning inconsistencies text entries:

=TRIM() -removes leading, trailing, and repeated internal spaces (e.g, turns " Peter Otieno" into "Mary Achieng")

=PROPER() -converts text to Title Case, so "john mwangi" and "MARY ACHIENG" to "John Mwangi" and "Mary Achieng"

=LOWER() -converts texts to lowercase, useful for standardizing email addresses so "JOHNMWANGI@GMAIL.COM and johnmwangi@gmail.com are recognized as equal. ### 2.3 Handling Dates and Catching Format Errors Data in Excel can sometimes look correct but actually can be stored incorrectly, which can cause problems when calculating and sorting data. e.g a date like 13/06/2023 might be understood differently depends on Excel's regional settings. The formula checks whether Excel can understand the entry as a real date. If it's a real date, it changes it format DD-MMM-YYYY such as 13-05-2023 ### 2.4 Flagging Duplicates, Blanks, and Errors This step is about finding problems in a data automatically instead of checking rows manually. Three checks are used:

Duplicate detection: Finds records that appear more than once and marks them as "Duplicate"
Blank Detection: Finds cells where information is missing and marks them as "Yes".
Price validation: Checks whether the price is negative. If it is, it shows "Negative Price"; otherwise, it shows OK.
Conditional formatting is used to highlight these problems in red problems making them easy to spot:

2.5 Producing the Final, Clean Dataset

With every issue identified, the final step combines Excel's inbuilt data; Remove duplicates with the corrections suggested by the flag columns: the duplicate order (1007) was removed, the negative price on order was corrected to its intended positive value, missing quantities and regions were filled after checking the original records, and every data was rewritten in single consistent format. The result is a clean analysis ready table:
" width="799" height="280">

Key Takeaways

Data cleaning is very important in data analysis. Excel functions like TRIM, PROPER, LOWER, COUNTSIFS help fix common data problems.

Use conditional formatting to highlight problems so that they are easy to see.
Always check dates. A date may look correct but could be stored as a text, which can cause errors.
Use formulas for analysis instead of manually typing.

My First GitHub Project: From a Local Folder to GitHub using Git and SSH

Victoria Ndei — Sat, 22 Aug 2026 14:55:35 +0000

Introduction

This week I learnt how to create my first GitHub project from a local folder on my desktop using tools such as Git and an SSH connection. Git is used to track changes in the source code and files over time, while SSH is a key used for authenticating your desktop with your GitHub account. There are several steps that we need to follow before connecting our project to our GitHub account.

1 Creating a Folder

There are two ways to create a folder; the first way is to go to your file explorer and select Desktop to create the folder directly there. The second step is to use Git Bash. When using Git Bash, first, you run the command pwd to see which folder you're currently in; e.g when using Windows, you may see /c/Users/John _.To change your directory to Desktop, you run the command

cd Desktop

Then run ls to see the files located on your desktop. Now we create our folder. To create a folder, we run

mkdir Kenya_Health_Records

Then we run *cd Kenya_Health_Records * to change the directory to our folder.

2 Adding Files to The Folder

This is our second step after creating our folder. We are going to add the following folders to our project folder:

data
notebooks
scripts

To add the folders, we will run the following commands:

mkdir data

mkdir notebooks

mkdir scripts

We will also add our README.md using the command:

touch README.md`shell

Adding information to our README.md

To add information to our README, we use the echo command, two double quotes, and the> sign.
Example:
echo " ## My First GitHub Project" >README.mdshell
If you want to check that the information is added to the README.md file, we use

cat README.mdshell

3 Initializing the Project with Git

To initialize the project, we use the command git init
git init initializes the directory as a Git repository.
When I ran git init, it created a hidden .git directory inside my project to track changes.
Afterwards, I checked the status of my files using git status.

4 Stage the Whole Project

To stage the remaining changes to the whole project, I ran:

git add.shell
The period (.) tells Git to add all files to the current directory. I ran *git status * to check if the files have been added.

5 Creating the First Commit

Run: git commit-m" Add Kenya Health Records`
git commit is a snapshot of the changes made to the project.

6 Check Current Branch

Run:

git branch

shell
You may see *main, which is the current branch we are in.
For our beginner project, we will use main as our primary branch. If your branch says master, rename it using git branch -M main`

7 Creating GitHub repository

Sign in to your GitHub account and then click New repository.
Name your repository as Kenya-Health-Records.
You can choose whether to set it as private or public.
Our project already has a README.md, so do not select to add it to the repository.

8 Selecting SSH

After creating the repository, GitHub displays connection options like HTTPS and SSH.
Select SSH because it was already configured earlier.
The address will look similar to:git@github.com:USERNAME/kenyan-hospital-health-records.git

9 Connecting the Local Repository to GitHub

Open your git bash and run;

git remote add origin git@github.com:USERNAME/kenyan-hospital-health-records.gitshell
I replaced the username with my GitHub username.
I verified the remote connection using:
git remote-v

10 Pushing the Project to GitHub

Run:

git push -u origin main
git push sends the changes from my computer to my remote repository.

11 Verifying the Project on GitHub

After the push was completed, I went to GitHub and refreshed the page to see if my project files had been added to the repository.

Conclusion

I gained practical experience in how to upload my local folder using Git and SSH on GitHub. The experience was truly great, and now I'm more familiar with the tools, making it easier to work on more projects on my own from henceforth.