DEV Community: Mohammed Swaleh

SQL for Hospital Data Analysis: Window Functions, Joins, CTEs, and Subqueries

Mohammed Swaleh — Sat, 17 Oct 2026 17:56:56 +0000

Introduction

This article explains four core SQL areas using the hospital dataset: doctors, department, diagnosis, wards, patients, procedures, and visits. The visits table is the central fact table: each row records one patient visit, including the doctor, department, ward, diagnosis, procedure, admission type, length of stay, bill amount, payment method, and satisfaction score. [file:1]

1. SQL Window Functions

A window function performs a calculation across a set of related rows—called a window—without collapsing those rows into one output row. This differs from GROUP BY, which returns one row per group. Window functions are useful in a hospital setting when you need to retain individual visit details while also showing department totals, rankings, running totals, or comparisons with previous visits. [file:1]

How `OVER()` works

OVER() defines the window over which the function operates.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER () AS hospital_average_bill
FROM visits;

What it does: It returns every visit and adds the average bill across all visits.

Expected result: Every row remains visible, but each row has the same hospital-wide average bill. This helps identify visits whose cost is above or below the hospital average.

`PARTITION BY`

PARTITION BY divides rows into groups before applying the window function. It is like GROUP BY, except it does not reduce the number of rows.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_average_bill
FROM visits;

What it does: It calculates the average bill separately for Cardiology, General Medicine, Orthopedics, and Pediatrics.

Expected result: A Cardiology visit receives the Cardiology average, while an Orthopedics visit receives the Orthopedics average.

`ORDER BY` in a window

ORDER BY determines the sequence of rows within each partition. It is essential for rankings and running calculations.

SELECT
    visit_id,
    department_id,
    visit_date,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
        ORDER BY visit_date
    ) AS running_department_bill
FROM visits;

What it does: It calculates a running total of bills by department, ordered by visit date.

Expected result: For each department, the first visit shows its own bill, the second shows the sum of the first two bills, and so on.

Window frames

A window frame controls exactly which rows are included in the calculation. The default frame for ordered aggregate functions is usually:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

You can make the frame explicit:

SELECT
    visit_id,
    department_id,
    visit_date,
    total_bill_kes,
    AVG(total_bill_kes) OVER (
        PARTITION BY department_id
        ORDER BY visit_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS two_visit_moving_average
FROM visits;

What it does: For each visit, it averages the current bill and the immediately preceding bill within the same department.

Expected result: This produces a two-visit moving average, useful for identifying short-term changes in departmental billing.

Ranking functions

Function	Purpose	Tie behaviour
`ROW_NUMBER()`	Assigns a unique sequential number	No ties; every row receives a different number
`RANK()`	Ranks rows with gaps after ties	Tied rows share a rank; the next rank skips
`DENSE_RANK()`	Ranks rows without gaps	Tied rows share a rank; the next rank does not skip
`NTILE(n)`	Divides rows into `n` approximately equal groups	Useful for quartiles, quintiles, and segments

`ROW_NUMBER()`

SELECT
    visit_id,
    patient_id,
    department_id,
    total_bill_kes,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes DESC
    ) AS bill_rank
FROM visits;

What it does: It numbers visits from highest to lowest bill within each department.

Expected result: The most expensive visit in each department receives 1, the next receives 2, and so on. Even if two bills are equal, they receive different row numbers.

`RANK()`

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    RANK() OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes DESC
    ) AS bill_rank
FROM visits;

What it does: It ranks visits by bill amount within each department, allowing tied ranks.

Expected result: If two visits tie for the highest bill, both receive rank 1, and the next visit receives rank 3.

`DENSE_RANK()`

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    DENSE_RANK() OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes DESC
    ) AS dense_bill_rank
FROM visits;

What it does: It ranks visits like RANK() but does not skip ranks after ties.

Expected result: If two visits tie at rank 1, the next visit receives rank 2, not 3.

Comparison: Use ROW_NUMBER() when you need a unique position, RANK() when tied values should share a position and gaps are acceptable, and DENSE_RANK() when tied values should share a position without gaps.

`NTILE()`

SELECT
    visit_id,
    patient_id,
    total_bill_kes,
    NTILE(4) OVER (
        ORDER BY total_bill_kes DESC
    ) AS bill_quartile
FROM visits;

What it does: It divides all visits into four bill-value groups.

Expected result: Quartile 1 contains the highest-billing visits, while quartile 4 contains the lowest-billing visits. Hospital managers can use this to review the most expensive 25% of visits.

Value comparison functions

`LAG()`

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

What it does: For each patient, it retrieves the bill from the patient’s previous visit.

Expected result: A patient’s first visit has NULL for previous_bill; later visits show the previous amount. This can support follow-up cost analysis.

`LEAD()`

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

What it does: For each patient, it retrieves the bill from the next visit.

Expected result: The patient’s final visit has NULL for next_bill; earlier visits show the following visit’s bill.

`FIRST_VALUE()`

SELECT
    visit_id,
    patient_id,
    visit_date,
    total_bill_kes,
    FIRST_VALUE(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date
    ) AS first_visit_bill
FROM visits;

What it does: It shows the bill from each patient’s earliest visit.

Expected result: Every visit for the same patient displays that patient’s first recorded bill.

`LAST_VALUE()`

SELECT
    visit_id,
    patient_id,
    visit_date,
    total_bill_kes,
    LAST_VALUE(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_visit_bill
FROM visits;

What it does: It shows the bill from each patient’s latest visit.

Expected result: Every visit for the same patient displays that patient’s final recorded bill. The explicit frame is important because, without it, LAST_VALUE() may only look at rows up to the current row.

Aggregate window functions

Aggregate window functions calculate totals, averages, counts, and extremes while preserving individual rows.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_total_bill,
    AVG(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_average_bill,
    COUNT(*) OVER (
        PARTITION BY department_id
    ) AS department_visit_count,
    MIN(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_minimum_bill,
    MAX(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_maximum_bill
FROM visits;

What it does: For every visit, it displays the department’s total bill, average bill, number of visits, lowest bill, and highest bill.

Expected result: Each department’s summary values repeat on every visit from that department, while the original visit-level data remains visible. This is useful for comparing an individual visit against its department’s performance.

2. SQL Joins

A join combines rows from two or more tables based on a related column. In this hospital database, joins are needed because visit details are separated across patients, doctors, department, diagnosis, wards, and procedures. [file:1]

`INNER JOIN`

An INNER JOIN returns only rows with matching values in both tables.

SELECT
    v.visit_id,
    p.patient_name,
    d.doctor_name,
    dep.department_name,
    v.total_bill_kes
FROM visits v
INNER JOIN patients p
    ON v.patient_id = p.patient_id
INNER JOIN doctors d
    ON v.doctor_id = d.doctor_id
INNER JOIN department dep
    ON v.department_id = dep.department_id;

What it does: It returns only visits that have a matching patient, doctor, and department.

Expected result: Each row shows a visit with the patient’s name, treating doctor, department, and bill. Visits with missing matching records are excluded.

Use case: Generate a clean operational report of completed visits with full patient, doctor, and department details.

`LEFT JOIN`

A LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match, columns from the right table are NULL.

SELECT
    p.patient_id,
    p.patient_name,
    v.visit_id,
    v.visit_date,
    v.total_bill_kes
FROM patients p
LEFT JOIN visits v
    ON p.patient_id = v.patient_id;

What it does: It returns every patient, including patients with no recorded visit.

Expected result: Patients with visits show their visit details; patients without visits show NULL in the visit columns.

Use case: Identify registered patients who have not attended any hospital visit.

To find only patients without visits:

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

`RIGHT JOIN`

A RIGHT JOIN returns all rows from the right table and matching rows from the left table.

SELECT
    d.doctor_id,
    d.doctor_name,
    v.visit_id,
    v.visit_date
FROM visits v
RIGHT JOIN doctors d
    ON v.doctor_id = d.doctor_id;

What it does: It returns every doctor, including doctors with no visits.

Expected result: Doctors with visits show their visits; doctors without visits show NULL visit details.

Use case: Identify doctors who have not handled any recorded patient visit. The same result can usually be written as a LEFT JOIN by reversing the table order.

`FULL OUTER JOIN`

A FULL OUTER JOIN returns all rows from both tables, matching where possible and using NULL where no match exists.

SELECT
    p.patient_id,
    p.patient_name,
    v.visit_id,
    v.total_bill_kes
FROM patients p
FULL OUTER JOIN visits v
    ON p.patient_id = v.patient_id;

What it does: It returns all patients and all visits, whether or not they match.

Expected result: Patients without visits and visits without a matching patient both appear, with NULL on the unmatched side.

Use case: Data-quality auditing—for example, detecting orphaned visit records or patients missing visit data. Note that PostgreSQL supports FULL OUTER JOIN; some database systems use slightly different syntax or do not support it directly. [file:1]

`CROSS JOIN`

A CROSS JOIN returns every possible combination of rows from the two tables. It does not require an ON condition.

SELECT
    dep.department_name,
    pr.procedure_name
FROM department dep
CROSS JOIN procedures pr;

What it does: It pairs every department with every procedure.

Expected result: If there are 4 departments and 15 procedures, the result contains 60 rows.

Use case: Build a planning matrix, such as reviewing which procedures could potentially be offered in each department. Use it carefully because the output grows quickly.

`SELF JOIN`

A self join compares rows within the same table by using two aliases for that table.

SELECT
    d1.doctor_name AS doctor,
    d2.doctor_name AS more_experienced_colleague,
    d2.doctor_experience_years
FROM doctors d1
INNER JOIN doctors d2
    ON d2.doctor_experience_years > d1.doctor_experience_years;

What it does: For each doctor, it finds colleagues with more experience.

Expected result: Dr. Faith Chebet, with 8 years of experience, is paired with all three more experienced doctors; Dr. Peter Otieno, with 15 years, has no more experienced colleague and therefore does not appear. [file:1]

Use case: Compare doctors, patients, or departments against other records in the same table—for example, identifying senior colleagues for mentorship.

Join comparison

Join type	Rows returned	Hospital use case
`INNER JOIN`	Only matching rows	Report visits with complete patient, doctor, and department details
`LEFT JOIN`	All left-table rows plus matches	Find patients with no visits
`RIGHT JOIN`	All right-table rows plus matches	Find doctors with no visits
`FULL OUTER JOIN`	All rows from both tables	Audit unmatched patients or visits
`CROSS JOIN`	Every combination of rows	Create a department-procedure planning matrix
`SELF JOIN`	Rows from one table compared with itself	Compare doctors by experience

3. Common Table Expressions

A Common Table Expression, or CTE, is a named temporary result set defined using WITH. It makes complex queries easier to read, test, and reuse within one statement.

Basic CTE

WITH cardiology_visits AS (
    SELECT
        visit_id,
        patient_id,
        doctor_id,
        total_bill_kes
    FROM visits
    WHERE department_id = 10
)
SELECT
    cv.visit_id,
    p.patient_name,
    cv.total_bill_kes
FROM cardiology_visits cv
INNER JOIN patients p
    ON cv.patient_id = p.patient_id;

What it does: The CTE first selects all Cardiology visits, then the main query joins those visits to patient names.

Expected result: A list of Cardiology visits with patient names and bill amounts.

Use case: Isolate one department before performing further analysis.

Multiple CTEs

WITH department_summary AS (
    SELECT
        department_id,
        COUNT(*) AS total_visits,
        SUM(total_bill_kes) AS total_revenue,
        AVG(patient_satisfaction_score) AS average_satisfaction
    FROM visits
    GROUP BY department_id
),
high_revenue_departments AS (
    SELECT
        department_id,
        total_visits,
        total_revenue,
        average_satisfaction
    FROM department_summary
    WHERE total_revenue > 50000
)
SELECT
    dep.department_name,
    h.total_visits,
    h.total_revenue,
    ROUND(h.average_satisfaction, 2) AS average_satisfaction
FROM high_revenue_departments h
INNER JOIN department dep
    ON h.department_id = dep.department_id
ORDER BY h.total_revenue DESC;

What it does: The first CTE summarizes visits and revenue by department. The second CTE filters for departments earning more than KES 50,000. The final query adds department names.

Expected result: A list of high-revenue departments, their visit counts, total revenue, and average patient satisfaction.

Use case: Break a large analytical question into readable stages.

Recursive CTE

A recursive CTE references itself and is useful for hierarchical or sequential data, such as generating a sequence of dates or walking through an organizational structure.

WITH RECURSIVE visit_sequence AS (
    SELECT
        1 AS visit_number
    UNION ALL
    SELECT
        visit_number + 1
    FROM visit_sequence
    WHERE visit_number < 10
)
SELECT *
FROM visit_sequence;

What it does: It generates numbers from 1 to 10.

Expected result: Ten rows numbered 1 through 10.

Hospital use case: Generate a date series for a dashboard, such as all dates in June 2026, then left join it to daily visit counts so that days with zero visits still appear.

CTEs with joins and aggregation

WITH patient_visit_summary AS (
    SELECT
        patient_id,
        COUNT(*) AS total_visits,
        SUM(total_bill_kes) AS total_spent,
        AVG(patient_satisfaction_score) AS average_satisfaction
    FROM visits
    GROUP BY patient_id
)
SELECT
    p.patient_name,
    p.county,
    pvs.total_visits,
    pvs.total_spent,
    ROUND(pvs.average_satisfaction, 2) AS average_satisfaction
FROM patient_visit_summary pvs
INNER JOIN patients p
    ON pvs.patient_id = p.patient_id
ORDER BY pvs.total_spent DESC;

What it does: It aggregates each patient’s visit count, total spending, and satisfaction score, then joins the result to patient demographic details.

Expected result: One row per patient who has visited, ordered from the highest total spending to the lowest.

Use case: Identify high-value or frequent patients for follow-up, loyalty programmes, or service-quality review.

CTEs versus subqueries

Aspect	CTE	Subquery
Definition	A named temporary result set introduced with `WITH`	A query embedded inside another query
Readability	Usually clearer for multi-step logic	Can become difficult to read when deeply nested
Reuse	Can be referenced multiple times within the same query	Normally written again if needed in several places
Recursion	Supports recursive CTEs	Does not naturally support recursion
Scope	Exists only for the duration of the main query	Exists only within the enclosing query
Best use	Multi-step analysis, reusable logic, hierarchical data	Simple filtering, calculated values, or small derived tables

Example using a subquery instead of a CTE:

SELECT
    dep.department_name,
    ds.total_visits,
    ds.total_revenue
FROM department dep
INNER JOIN (
    SELECT
        department_id,
        COUNT(*) AS total_visits,
        SUM(total_bill_kes) AS total_revenue
    FROM visits
    GROUP BY department_id
) ds
    ON dep.department_id = ds.department_id;

This produces the same department summary as a CTE, but the CTE version is generally easier to maintain when the logic becomes longer.

4. SQL Subqueries

A subquery is a query placed inside another SQL statement. Subqueries can return a single value, one column, one row, or an entire table-like result.

Scalar subquery

A scalar subquery returns exactly one value.

SELECT
    visit_id,
    patient_id,
    total_bill_kes,
    total_bill_kes - (
        SELECT AVG(total_bill_kes)
        FROM visits
    ) AS difference_from_average
FROM visits;

What it does: It calculates how much each visit’s bill differs from the hospital-wide average bill.

Expected result: Positive values indicate above-average bills; negative values indicate below-average bills.

Use case: Flag unusually expensive or inexpensive visits.

Non-correlated subquery

A non-correlated subquery can run independently because it does not refer to the outer query.

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM visits
);

What it does: It returns visits with bills above the hospital average.

Expected result: Only higher-than-average visits appear.

Use case: Identify costly visits for cost-control review.

Correlated subquery

A correlated subquery refers to a column from the outer query, so it is evaluated in relation to each outer row.

SELECT
    v.visit_id,
    v.department_id,
    v.total_bill_kes
FROM visits v
WHERE v.total_bill_kes > (
    SELECT AVG(v2.total_bill_kes)
    FROM visits v2
    WHERE v2.department_id = v.department_id
);

What it does: For every visit, it compares that visit’s bill with the average bill in the same department.

Expected result: Only visits costing more than their department’s average are returned.

Use case: Detect unusually expensive visits relative to the department, rather than the hospital as a whole.

Nested subqueries

A nested subquery is a subquery inside another subquery.

SELECT
    patient_id,
    patient_name
FROM patients
WHERE patient_id IN (
    SELECT patient_id
    FROM visits
    WHERE department_id = (
        SELECT department_id
        FROM department
        WHERE department_name = 'Cardiology'
    )
);

What it does: It first finds the Cardiology department ID, then finds patients who visited that department.

Expected result: A list of patients treated in Cardiology.

Use case: Answer layered questions, such as “Which patients were treated in the department named Cardiology?”

Subqueries in `SELECT`

A subquery in SELECT is commonly scalar and adds a calculated value to every row.

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT MAX(total_bill_kes)
        FROM visits
    ) AS highest_hospital_bill
FROM visits;

What it does: It displays each visit alongside the highest bill in the hospital.

Expected result: Every row has the same highest_hospital_bill value.

Subqueries in `FROM`

A subquery in FROM acts as a derived table and must be given an alias.

SELECT
    dep.department_name,
    ds.total_visits,
    ds.total_revenue
FROM (
    SELECT
        department_id,
        COUNT(*) AS total_visits,
        SUM(total_bill_kes) AS total_revenue
    FROM visits
    GROUP BY department_id
) ds
INNER JOIN department dep
    ON ds.department_id = dep.department_id;

What it does: It first summarizes visits by department, then joins the summary to department names.

Expected result: One row per department showing visit count and total revenue.

Subqueries in `WHERE`

Subqueries in WHERE filter rows based on another query’s result.

SELECT
    patient_id,
    patient_name
FROM patients
WHERE patient_id IN (
    SELECT patient_id
    FROM visits
    WHERE total_bill_kes > 20000
);

What it does: It returns patients who had at least one visit costing more than KES 20,000.

Expected result: Only patients associated with high-cost visits appear.

`IN`

IN checks whether a value matches any value returned by a subquery.

SELECT
    p.patient_name,
    p.county
FROM patients p
WHERE p.patient_id IN (
    SELECT v.patient_id
    FROM visits v
    WHERE v.admission_type = 'Inpatient'
);

What it does: It returns patients who have had at least one inpatient admission.

Expected result: A list of admitted patients.

Use case: Identify patients who required hospital admission.

`EXISTS`

EXISTS returns TRUE if the subquery returns at least one row. It is often efficient because the database can stop as soon as it finds one matching row.

SELECT
    p.patient_id,
    p.patient_name
FROM patients p
WHERE EXISTS (
    SELECT 1
    FROM visits v
    WHERE v.patient_id = p.patient_id
      AND v.follow_up_required = 'Yes'
);

What it does: It returns patients with at least one visit requiring follow-up.

Expected result: A list of patients needing follow-up attention.

Use case: Support follow-up scheduling and patient-outreach workflows.

`ANY`

ANY compares a value with at least one value returned by a subquery.

SELECT
    visit_id,
    department_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM visits
    WHERE department_id = 20
);

What it does: It returns visits whose bill is greater than at least one General Medicine visit bill.

Expected result: Visits that exceed the minimum General Medicine bill.

Use case: Compare a department’s costs against another department’s cost range.

`ALL`

ALL compares a value with every value returned by a subquery.

SELECT
    visit_id,
    department_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
    SELECT total_bill_kes
    FROM visits
    WHERE department_id = 20
);

What it does: It returns visits whose bill is greater than every General Medicine visit bill.

Expected result: Only visits more expensive than the highest General Medicine bill.

Use case: Identify exceptional high-cost cases relative to a benchmark department.

Practical Combined Example

The following query combines a CTE, window function, join, and subquery to identify each department’s highest-billing visit.

WITH ranked_visits AS (
    SELECT
        v.visit_id,
        v.patient_id,
        v.department_id,
        v.total_bill_kes,
        ROW_NUMBER() OVER (
            PARTITION BY v.department_id
            ORDER BY v.total_bill_kes DESC
        ) AS bill_rank
    FROM visits v
)
SELECT
    dep.department_name,
    p.patient_name,
    rv.visit_id,
    rv.total_bill_kes
FROM ranked_visits rv
INNER JOIN department dep
    ON rv.department_id = dep.department_id
INNER JOIN patients p
    ON rv.patient_id = p.patient_id
WHERE rv.bill_rank = 1;

What it does: The CTE ranks visits by bill amount within each department. The final query keeps only the visit ranked 1 in each department and adds department and patient names.

Expected result: One row per department, showing that department’s most expensive visit and the patient involved.

Hospital use case: Hospital administrators can use this report to review the most expensive case in each department, investigate whether the cost was justified by the diagnosis or procedure, and identify opportunities for cost management.

Conclusion

SQL window functions, joins, CTEs, and subqueries are essential tools for turning raw hospital records into useful operational and clinical insights. Window functions allow analysts to rank visits, calculate running totals, compare each visit with departmental averages, and examine patient visit patterns without losing visit-level detail. Joins bring together information stored across the patients, doctors, department, diagnosis, wards, and procedures tables to create complete reports. CTEs and subqueries make complex analysis easier to structure, especially when calculating departmental revenue, identifying high-cost visits, finding patients who need follow-up, or detecting data-quality issues. Applied together, these SQL concepts support better hospital decision-making in areas such as resource planning, cost control, patient follow-up, service-quality monitoring, and performance evaluation.

DBeaver SQL Summary Learning Notes

Mohammed Swaleh — Thu, 15 Oct 2026 06:59:58 +0000

DBeaver & SQL Summary Learning Notes

A step-by-step guide using the Hospital Database worked examples

Learning Roadmap

Understand DBeaver and the SQL editor
Connect to the database and select a schema
Understand tables, columns, keys and constraints
Create tables and insert records
Retrieve data with SELECT
Filter rows with WHERE and operators
Sort and limit results
Summarise data with aggregate functions
Group records with GROUP BY and HAVING
Join related tables
Modify records and table structures
Delete data safely and understand table removal
Write SQL in the correct order and practise

1. DBeaver and the SQL Editor

Connect to PostgreSQL database.
Use SQL Editor → run queries with Ctrl+Enter.
Inspect Results grid and error panel.
Use comments:

  -- Show all doctors
  SELECT * FROM doctors;

  /* This query returns all rows and columns */

2. Create and Select a Schema

CREATE SCHEMA morning;
SET search_path TO morning;
SHOW search_path;

3. Tables, Columns, Keys and Constraints

Common Data Types

INT → whole numbers
VARCHAR(n) → text
DATE → YYYY-MM-DD
NUMERIC(10,2) → decimals
SERIAL → auto-increment integer

Constraints

PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, DEFAULT

Example

CREATE TABLE doctors (
  doctor_id INT PRIMARY KEY,
  doctor_name VARCHAR(50) NOT NULL,
  doctor_gender VARCHAR(20),
  doctor_specialty VARCHAR(20),
  doctor_experience_years INT
);

4. Insert Records

INSERT INTO doctors
(doctor_id, doctor_name, doctor_gender, doctor_specialty, doctor_experience_years)
VALUES
(101, 'Dr. Brian Mwangi', 'Male', 'Cardiologist', 12),
(102, 'Dr. Sarah Wanjiku', 'Female', 'General Physician', 9);

SELECT * FROM doctors;

5. Retrieve Data with SELECT

-- All columns
SELECT * FROM doctors;

-- Selected columns
SELECT doctor_id, doctor_name FROM doctors;

-- Alias
SELECT d.doctor_id, d.doctor_name FROM doctors AS d;

-- Limit rows
SELECT * FROM doctors LIMIT 5;

6. Filtering Rows

Comparison Operators

SELECT * FROM patients WHERE patient_age = 30;
SELECT * FROM patients WHERE patient_age <> 30;
SELECT * FROM patients WHERE patient_age BETWEEN 19 AND 30;

Logical Operators

SELECT * FROM patients WHERE patient_gender = 'Male' AND patient_age > 40;
SELECT * FROM patients WHERE patient_gender = 'Male' OR patient_age > 40;
SELECT * FROM patients WHERE NOT patient_gender = 'Male';

IN, NOT IN, NULL, DISTINCT

SELECT * FROM visits WHERE triage_level IN ('Green','Yellow');
SELECT * FROM visits WHERE ward_id IS NULL;
SELECT DISTINCT insurance_provider FROM visits;

7. Sorting and Limiting

SELECT visit_id, visit_date FROM visits ORDER BY visit_date DESC;
SELECT patient_id, patient_name, patient_age FROM patients ORDER BY patient_age DESC LIMIT 1;

8. Aggregate Functions

SELECT COUNT(*) AS total_patients FROM patients;
SELECT AVG(patient_age) AS average_age FROM patients;
SELECT SUM(total_bill_kes) AS total_billed FROM visits;
SELECT MAX(total_bill_kes), MIN(total_bill_kes) FROM visits;

9. GROUP BY and HAVING

SELECT patient_gender, AVG(patient_age) FROM patients GROUP BY patient_gender;
SELECT doctor_id, SUM(total_bill_kes) FROM visits GROUP BY doctor_id HAVING SUM(total_bill_kes) > 100000;

10. Joins

INNER JOIN

SELECT v.visit_id, v.visit_date, p.patient_name, d.doctor_name, v.total_bill_kes
FROM visits v
INNER JOIN patients p ON v.patient_id = p.patient_id
INNER JOIN doctors d ON v.doctor_id = d.doctor_id
ORDER BY v.visit_date;

LEFT JOIN

SELECT p.patient_id, p.patient_name, v.visit_id
FROM patients p
LEFT JOIN visits v ON p.patient_id = v.patient_id;

11. Modify Records

UPDATE

UPDATE doctors SET doctor_name = 'Dr. Peter Ochieng' WHERE doctor_id = 103;

DELETE

DELETE FROM triage WHERE triage_id = 2;
DELETE FROM triage WHERE risk_level IS NULL;

12. Alter Table

ALTER TABLE doctors ADD COLUMN email VARCHAR(60);
ALTER TABLE doctors RENAME COLUMN email TO doctors_email;
ALTER TABLE triage RENAME TO dim_triage;

13. DELETE vs TRUNCATE vs DROP

Command	Removes	Use
DELETE FROM table WHERE…	Selected rows	Specific records
DELETE FROM table	All rows	Retain structure
TRUNCATE TABLE	All rows efficiently	Empty table
DROP TABLE	Table + structure	Remove table

14. SQL Order

Writing order:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Execution order:

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

15. Practical Patterns

SELECT patient_id, length_of_stay_days FROM visits WHERE length_of_stay_days > 0;
SELECT visit_id, patient_id FROM visits WHERE payment_method = 'Cash';
SELECT insurance_provider, SUM(total_bill_kes) AS total_revenue
FROM visits GROUP BY insurance_provider ORDER BY total_revenue DESC LIMIT 1;

16. Common Mistakes

Use exact column names (patient_age, not patients_age).
Use single quotes for text/date values.
Use IS NULL, not = NULL.
Always add WHERE in UPDATE/DELETE unless affecting all rows.

17. Suggested Practice Sequence

Display all patients.
Find patients aged ≤ 30.
List visits between 1–10 June 2026.
Count visits and average bill.
Calculate total billed by admission_type.
Find patients with >1 visit.
Join visits with patients and doctors.
Alter a table (add/rename/drop column).
Compare DELETE, TRUNCATE, DROP.

18. Quick Reference

Need to…	SQL keyword
Read data	SELECT
Choose source table	FROM
Filter rows	WHERE
Remove duplicates	DISTINCT
Sort results	ORDER BY
Limit rows	LIMIT
Aggregate	COUNT, SUM, AVG, MIN, MAX
Group	GROUP BY
Filter groups	HAVING
Combine tables	JOIN
Add rows	INSERT INTO
Change rows	UPDATE
Remove rows	DELETE
Change structure	ALTER TABLE
Empty table	TRUNCATE
Remove table	DROP TABLE

From Raw Data to Business Decisions: JCars Logistics Power BI Project

Mohammed Swaleh — Fri, 02 Oct 2026 08:00:20 +0000

Introduction

JCars Logistics, a leading vehicle importer and distributor in Kenya, wanted to transform its raw operational data into actionable insights. The dataset contained thousands of records on sales, customers, vehicles, branches, payments, deliveries, logistics costs, returns, and cancellations.

The challenge: build a Power BI solution that moves from messy raw data to a clean, interactive dashboard that helps management answer critical business questions.

Data Preparation

The raw dataset was full of issues:

Inconsistent date formats (Aug 29, 2025, 2026-13-04, not sure).
Mixed currencies (KES, USD, EUR, ZAR).
Spelling variations (Toyta, totoya).
Missing values (customer age, vehicle year).
Suspicious entries (negative revenue, units sold = -1)
Duplicate or inconsistent IDs (ORD1020 appears twice).
Mixed numeric formats (0.08M vs 80,000).
Text in numeric fields (Discount “ten percent”).
Outliers (Customer Age = 121).
Invalid dates (31/02/2026).

Using Power Query, the task involved the following:

Standardized date formats.
Converted all monetary values into Kenya Shillings (KES) using Central Bank exchange rates.
Corrected spelling and standardized categories.
Flagged unusual records instead of deleting them.
Created a clean, analysis‑ready model.
Normalize text (uppercase/lowercase, spelling corrections).
Convert numeric fields to proper data types.
Replace or flag missing values.
Standardize categorical values (Payment Status, Delivery Status).
Create calculated fields if needed (e.g., Gross Profit = Revenue – Cost – Logistics).

Data Modelling

I reorganized the flat file into:

Fact Table: Orders (sales, costs, revenue, logistics).
Dimension Tables: Customers, Vehicles, Branches, Sales Reps, Dates.

Relationships were defined using Order ID, Customer ID, and Branch ID.

DAX Measures

Key measures included:

Total Revenue = SUM(fact_orders[RevenueRecorded])

Gross Profit =
SUMX(
    fact_orders,
    fact_orders[RevenueRecorded] - fact_orders[UnitCost] - fact_orders[LogisticsCost]
)

Gross Profit Margin = DIVIDE([Gross Profit], [Total Revenue], 0)

Return Rate =
DIVIDE(
    CALCULATE(COUNTROWS(fact_orders), fact_orders[Returned] = "Yes"),
    COUNTROWS(fact_orders),
    0
)

These measures allowed management to track profitability, efficiency, and customer behavior.

Executive Dashboard

The one‑page dashboard highlighted:

KPIs:

Revenue (KES 1.44B), > Units Sold (458), > Orders (276), > Avg Order Value (KES 5.6M).

Trends: Monthly revenue fluctuations.

Breakdowns: Revenue by Region, Vehicle Type, Sales Rep, Lead Source.

Alerts: High cancellations, unusual logistics costs.

Detailed Reports

Additional pages allowed deeper investigation:

Regional Performance: Rift Valley leads with 25.3% of revenue.

Vehicle Performance: SUVs dominate with 57.8% of revenue.

Sales Rep Analysis: Faith Achieng contributes 14.5% of revenue.

Lead Source Analysis: Instagram and Facebook drive the highest sales.

Returns & Cancellations: Certain models (e.g., Toyota LC200, Subaru XV) show higher return rates.

Insights

SUVs are the backbone of JCars revenue, but pickups and sedans provide stronger margins.

Rift Valley dominates regional sales, while Nairobi suffers from cancellations.

Digital channels outperform traditional lead sources — Instagram and Facebook are critical.

Top reps drive disproportionate revenue — Faith Achieng alone contributes 14.5%.

Future‑dated and cancelled orders distort reporting, requiring stricter validation.

Recommendations

Investigate Nairobi operations to reduce cancellations and improve delivery reliability.
Expand SUV inventory but balance with pickups and sedans for profitability.
Invest further in digital marketing, especially Instagram and Facebook.
Replicate top rep practices through training and mentorship.
Strengthen data governance to prevent future‑dated and erroneous entries.

Conclusion

This project demonstrates how Power BI can transform raw, inconsistent data into a decision‑support tool. By cleaning, modelling, and visualizing the dataset, JCars Logistics now has a clear view of performance drivers, risks, and opportunities.

The journey from raw CSV to polished dashboard highlights the importance of data quality, modelling discipline, and actionable insights in business intelligence.

Building Effective Power BI Data Models: Schemas, Relationships, and Joins Explained

Mohammed Swaleh — Sun, 27 Sep 2026 19:40:53 +0000

Introduction

A well-designed data model is the foundation of every successful Power BI solution. It determines how fast your reports load, how simple your DAX formulas are, and how easily your solution can scale as your business grows. This article walks through the core concepts of data modelling in Power BI—covering schema design, fact and dimension tables, relationships, filter direction, and joins in Power Query—so you can build models that are performant, maintainable, and intuitive for report authors.

1. Data Modelling in Power BI
What is Data Modelling and Why Does it Matter?
Data modelling in Power BI is the process of structuring your data into tables, defining how those tables relate to one another, and organising them in a way that supports efficient querying, accurate calculations, and intuitive report building.

A well-designed data model is critical for:

Reporting and analytics: Clean, logical structures make it easier for users to drag and drop fields without confusion.
DAX calculations: Simpler models lead to simpler, more reliable measures.
Performance: Efficient schemas reduce the amount of data Power BI needs to scan and filter.
Scalability: Good models grow gracefully as data volumes and business requirements expand.
Maintainability: Clear structures are easier to document, troubleshoot, and hand over to other developers.

Data Modelling Approaches: Flat Table, Star Schema, and Snowflake Schema

Flat Table
Definition: A flat table is a single table where all data—facts and descriptive attributes—resides in one large table.

Structure:

One table with many columns. Columns for facts (sales amount, quantity) and attributes (customer name, product category)
No relationships; everything is in one place.

Advantages:

Simple to understand for beginners.
No need to manage relationships.
Quick to build for very small datasets.

Disadvantages:

High data redundancy (e.g., customer name repeated on every sales row).
Larger file size and slower refresh.
Harder to maintain as business rules change.
DAX can become complex when handling repeated attributes.

When appropriate:

Very small datasets or one-off analyses.
Prototyping or quick proofs of concept.

Implications for Power BI:

Poor performance at scale due to redundancy.
Increased model size and memory usage.
Difficult to extend or refactor later.

For instance, all descriptive attributes like PatientsName, PatientsGender, PatientsAge are repeated on every row

Normalization -

breaking down tables into smaller tables to reduce redundancies

Star Schema

Definition: A star schema consists of a central fact table surrounded by dimension tables, each connected via one-to-many relationships. A Central fact table connected to multiple dimension tables.

Structure:

One fact table in the centre, surrounded by Multiple dimension tables radiating out like a star.
Each dimension connects directly to the fact table.

Advantages:

Optimised for Power BI's tools, clear relationships, efficient queries.
Minimal DAX complexity.

Disadvantages:

Requires upfront modelling effort.
Slightly more tables to manage than a flat table.

When appropriate:

Most business intelligence and reporting scenarios.
When performance and usability are priorities.

Implications for Power BI:

Best-practice schema for Power BI.
Excellent query performance and DAX simplicity.
Scales well with large fact tables.

Illustration:

Snowflake Schema

Definition: A snowflake schema is a variation of the star schema where dimension tables are further normalised into sub-dimensions, creating a "snowflake" shape.

Structure:

Central fact table.eg Hospital_visits_fact_table
Dimension tables that are normalised into multiple related tables.
Example: DimPatients → DimPatientsCounty → DimPatientsSubcounty

Advantages:

Reduced data redundancy in dimensions.
More "database-normalised" design.
Useful when dimensions are very large and highly structured.

Disadvantages:

More complex model with longer relationship chains.
Potentially slower filter propagation.
Harder for report authors to understand and to navigate
More tables in the Data pane, which can confuse users.

When appropriate:

Very large, highly normalised data warehouses.
When dimension tables are extremely wide and benefit from normalisation.

Implications for Power BI:

Often unnecessary complexity for typical BI projects.
Can degrade performance due to longer filter paths.
Microsoft generally recommends star schema over snowflake for Power BI.

2. Fact Tables and Dimension Tables

Fact Table

A fact table stores measurable, numeric events—typically transactions or occurrences that you want to analyse.

Typical contents:

Numeric measures: SalesAmount, Quantity, Cost, Profit.
Foreign keys to dimension tables: CustomerID, ProductID, DateKey.
Very little descriptive text.

Grain (granularity):

The grain defines the level of detail in the fact table.
Example: One row per sales transaction line item.
Grain determines what you can aggregate and how you slice data.

Examples:

FactSales: One row per sales order line.
FactOrders: One row per order or order line.
FactTransactions: One row per financial transaction.

What is a Dimension Table?

A dimension table stores descriptive attributes used to slice, filter, and group facts.

Typical contents:

Primary key: CustomerID, ProductID, DateKey.
Descriptive columns: CustomerName, ProductName, Category, Country, Month.
Hierarchies: Year → Quarter → Month → Day; Category → Subcategory → Product.

Examples:

DimCustomer: CustomerID, CustomerName, Segment, Region.
DimProduct: ProductID, ProductName, Category, Brand.
DimDate: DateKey, Date, Day, Month, Quarter, Year.
DimLocation: LocationID, City, State, Country.

3. Relationships in Power BI

What is a Relationship?
A relationship in Power BI defines how two tables are connected based on matching column values. Relationships enable Power BI to:

Filter data across tables.
Combine fields from different tables in visuals.
Calculate measures correctly using DAX.

Without relationships, Power BI cannot correctly join data from multiple tables, leading to incorrect totals and broken visuals.

Relationship Cardinalities

Power BI supports three main cardinality types:

1. One-to-Many (1:*)
How it works:

One row in the "one" table matches many rows in the "many" table.
The "one" side must have unique values (primary key).
The "many" side can have duplicates (foreign key).

Example:

DimProcedureProcedureID → FactVisitVisitID.
One procedure can have many visits; each procedure belongs to one patient visit.

When to use:

Most common relationship in BI models.
Between dimension tables and fact tables.

When not to use:

When both sides have duplicates without a clear "one" side (consider bridge tables or many-to-many).

Illustration:

2. One-to-One (1:1)
How it works:

Each row in Table A matches exactly one row in Table B.
Both sides must have unique values.

Example:

DimEmployee and DimEmployeeDetails, where each employee has exactly one detail record.

When to use:

Splitting wide tables for manageability.
Integrating data from different sources with a perfect 1:1 match.

When not to use:

If there's any possibility of duplicates on either side (use 1:* instead).

Illustration:

(Each employee has exactly one detail record.)

3. Many-to-Many (:)
How it works:

Both sides can have duplicate values.
Power BI creates a virtual bridge to resolve the relationship.

Example:

Students and Courses: a student can take many courses; a course can have many students.
In Power BI, often implemented via a bridge table (e.g., FactEnrolment) to maintain clarity.

When to use:

True many-to-many business scenarios.
When no natural "one" side exists.
When not to use:
If you can introduce a bridge or fact table to convert to 1:* relationships (preferred for clarity and performance).

Illustration

(Multiple students per course; multiple courses per student.)

Key Relationship Concepts

Primary Keys and Foreign Keys

Primary Key: A column (or set of columns) with unique values that identify each row in a table. Example: CustomerID in DimCustomer.

Foreign Key: A column in another table that references a primary key. primary key referenced in another table, not unique. Example: CustomerID in FactSales.

In a 1:* relationship:

The "one" side holds the primary key (unique).
The "many" side holds the foreign key (duplicates allowed).

Unique Values and Referential Integrity

Unique values on the "one" side ensure each dimension row is distinct.

Referential integrity means every foreign key in the fact table matches a valid primary key in the dimension. Violations can cause missing or incorrect data in reports.

Active and Inactive Relationships

Active relationship: The default relationship used for filter propagation and DAX calculations. Only one active relationship per table pair for a given column path.

Inactive relationship: Exists but is not used unless explicitly activated in DAX using USERELATIONSHIP. Useful for role-playing dimensions (e.g., OrderDate vs ShipDate both linking to DimDate).

4. Filter Direction

How Filters Propagate
In Power BI, when a user selects a value in a visual (e.g., a product name), filters propagate through relationships to related tables, affecting which rows are included in calculations.

Single-Direction Filtering

Definition: Filters flow in one direction only—typically from the "one" side to the "many" side in a 1:* relationship.

Example:

Selecting "Product A" in DimProduct filters FactSales to show only sales of Product A.
Filters do not flow back from FactSales to DimProduct.

Why it's preferred:

Predictable behaviour.
Avoids ambiguous filter paths.
Better performance.

Bidirectional (Both) Filtering

Definition: Filters flow in both directions between related tables.

Example:

With bidirectional filtering between DimProduct and FactSales:
Selecting a product filters sales (as usual).
Selecting a sales record could also filter the product table (less common in practice).

When to use carefully:

Complex models with multiple paths between tables.
Scenarios requiring filters to flow "up" from facts to dimensions (rare).

Potential problems:

Ambiguous filter paths: Power BI may not know which path to use, leading to errors or unexpected results.
Performance impact: Extra filter propagation can slow down queries.
Model complexity: Harder to understand and maintain.

Best practice:

Use single-direction filtering by default.
Enable bidirectional filtering only when clearly needed and tested.

5. Joins in Power Query

What is a Join?

Combines two tables based on matching columns.

Left Outer Join

Keeps all rows from left + matches from right.
Unmatched right rows → null.

Left Outer Join Example

Right Outer Join

Keeps all rows from right + matches from left.
Unmatched left rows → null.

Full Outer Join

Keeps all rows from both tables.
Unmatched rows → nulls.

Inner Join

Keeps only matching rows from both tables.

Left Anti Join

Keeps rows from left with no match in right.

Right Anti Join

Keeps rows from right with no match in left.

6. Power Query Joins vs Power BI Relationships

Aspect	Power Query Merge	Power BI Relationship
Combines Data?	Yes, physically merges columns.	No, logical link only.
Stage	During ETL (Power Query).	After load, in model layer.
Use When	Need single denormalised table.	Building relational BI models.
Impact	Wide, redundant tables.	Compact, reusable dimensions.

Best Practice: Keep fact and dimension tables separate for clarity, performance, and scalability.

7. Recommended Power BI Model Design

Preferred Schema: Star Schema

Why:

Optimised for Power BI engine.
Simple DAX and predictable filters.
Clear separation of facts and dimensions.
Scalable and maintainable.

Relationship Design:

Cardinality: One-to-many (1:*).
Filter Direction: Single-direction.
Active Relationships: Standard reporting.
Inactive Relationships: Role-playing dimensions (activated in DAX).

Deviations:

Snowflake schema only for very large, normalised dimensions.
Bidirectional filtering only when necessary.
Flat table only for small prototypes.

Final Thoughts

Power BI’s strength lies in well-structured relational models. By adopting a star schema, using one-to-many single-direction relationships, and reserving Power Query merges for specific needs, you create models that are fast, clear, and easy to maintain. Understanding schemas, relationships, filter direction, and joins lets you design solutions that scale and give users accurate, insightful reports.

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

Mohammed Swaleh — Mon, 07 Sep 2026 08:35:23 +0000

Project Introduction and Objective

E-commerce platforms thrive on data. Sellers and marketplaces like Jumia need to understand how pricing, discounts, and customer feedback influence product performance. This project set out to build an interactive Excel dashboard that transforms raw Jumia product data into actionable insights.

The goal was not just to create charts, but to audit, clean, analyze, and visualize the data in a way that supports evidence-based recommendations for sellers.

Dataset and Business Questions

The dataset contained product names, current and old prices, advertised discounts, customer reviews, and ratings. Reviews were used as a proxy for engagement, since sales volumes were not available.

Key business questions:

Do larger discounts attract more reviews?
Do highly rated products show stronger engagement?
Is there a relationship between price and rating?
Which products perform best, and which need pricing or marketing adjustments?

Initial Data-Quality Audit

Before cleaning, the dataset revealed several issues:

Misspelled headers (e.g., Ratingd instead of Rating)
Negative review counts
Blank values in reviews and ratings
Prices expressed as ranges
Duplicate product rows

These anomalies were documented in a Data Dictionary worksheet to ensure transparency.

Cleaning and Preparation Decisions

Data cleaning followed clear, traceable rules:

Prices: stripped of “KSh” and commas, converted to currency (KES).
Discounts: converted to decimals.
Reviews: negative signs treated as scraping artifacts, converted to absolute values.
Ratings: standardized to decimals, blanks left as missing.
Price ranges: replaced with midpoints, while preserving original text for auditability.
Duplicates: removed only when all fields matched.

PivotTable and Analysis Workflow

PivotTables powered the analysis:

Rating Mix → distribution of Poor, Average, Excellent products
Discount Mix → proportion of Low, Medium, High discounts
Price vs Rating → average rating by price category
Engagement by Discount → reviews by discount category
Top Products → ranked by rating, reviews, and discount

Scatter plots with trendlines and correlation coefficients tested relationships between:

Discounts vs Reviews
Ratings vs Reviews
Price vs Rating

Dashboard Design and Slicer Connections

The dashboard was designed for single-screen readability:

KPIs row: total products, average price, average discount, average rating, total reviews
Ranked tables: top products by rating, reviews, and discount
Scatter plots: discount vs reviews, rating vs reviews, price vs rating
Mix charts: rating and discount distributions
Slicers: interactive filters for rating, discount, and price categories

Consistent formatting ensured clarity:

KES for prices
Decimals for discounts and ratings
Thousands separators for reviews

Key Findings

1. Price vs Rating

Products priced between KSh 1600–1999 show the highest average rating (0.87).
Lower-priced items (KSh 400–799) also perform well (0.83).
Mid-range prices dip slightly, showing weaker ratings. ➡️ Both affordable and moderately premium products attract better ratings.

2. Engagement by Discount

Engagement peaks in the 0.2–0.3 discount band (~18 reviews).
Very high discounts (>0.5) show lower engagement (~7 reviews). ➡️ Moderate discounts are the sweet spot for driving customer interaction.

3. Top Products by Rating

Near-perfect ratings (0.96):
- LASA Folding Table Serving Stand
- 40cm Gold DIY Acrylic Wall Sticker Clock
- Portable Home Small Air Humidifier

4. Top Products by Reviews

120W Cordless Vacuum Cleaner leads with 69 reviews but has a low rating (0.56).
Other high-engagement products:
- 137 Pieces Cake Decorating Tool Set (55 reviews)
- Electronic Digital Vernier Caliper (49 reviews)
- 3D Waterproof EVA Shower Curtain (44 reviews) ➡️ High engagement does not always align with high ratings.

5. Top Products by Discount

6-in-1 Bottle Can Opener tops with a 64% discount.
Heavy discounting concentrated in low-cost household goods. ➡️ Sellers use price cuts to stimulate demand in this category.

Correlation Analysis

Price vs Rating: Weak positive correlation (0.191426). Higher prices show a slight tendency toward better ratings, but the link is weak.

Ratings vs Reviews: Weak positive correlation (0.14671). More reviews loosely align with higher ratings, but the relationship is minimal.

Discount vs Reviews: Weak negative correlation (-0.13682). Larger discounts are slightly associated with fewer reviews.

➡️ Overall, none of the variables strongly predict the others.

Business Recommendations

Test discount bands to optimize engagement without eroding margins.
Improve listing content for products with visibility but average ratings.
Investigate quality issues for highly discounted but poorly rated items.
Promote products that combine excellent ratings with strong engagement.

Limitations and Lessons Learned

Reviews are a proxy for engagement, not sales.
Missing values were left blank rather than imputed.
Price ranges required assumptions (midpoints).
Correlation does not imply causation — discounts may coincide with other factors like product age or visibility.

Dashboard Screenshot

Jumia Product Dashboard Screenshot

Conclusion

This project demonstrates how Excel can be used not just for reporting, but for data-driven decision-making in e-commerce. By combining careful cleaning, structured analysis, and interactive dashboards, sellers gain practical insights into how pricing and customer feedback shape product performance.

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Mohammed Swaleh — Sat, 29 Aug 2026 21:14:53 +0000

Excel Data Cleaning & Analysis Guide

1. Introduction

Excel is more than a spreadsheet—it’s a powerful data-analysis tool. Analysts use it to inspect, validate, filter, summarize, and prepare raw data before deeper analysis.

The quality of insights depends on the quality of data. That’s why data cleaning is the foundation of effective analysis.

This guide demonstrates Week 1 Excel concepts using an employee dataset with fields like employee IDs, names, departments, gender, hire dates, salaries, education level, and performance scores. The raw file contains common issues: inconsistent capitalization, blanks, duplicates, varying department names, and misformatted dates/currency.

By applying Excel’s formatting tools, text functions, filters, conditional formatting, numerical functions, and date functions, we transform messy data into an analysis-ready dataset.

2. Why Data Cleaning Matters

Garbage in, garbage out: dashboards or predictions are misleading if data is flawed.
Standardization makes datasets transparent, usable, and valuable.
Examples:
- “IT”, “I.T.”, and “Information Tech” → treated as different departments unless standardized.
- Duplicate employee IDs → inflate totals.
- Blank performance scores → missing information.
- Dates saved as text → unreliable for tenure calculations.

Best practice: keep raw data separate, create a cleaned worksheet, and document your cleaning logic.

3. Dataset & Initial Inspection

Dataset size: 877 employee rows (with duplicates).
Issues: inconsistent formats in IDs, names, departments, salaries, and dates.
First steps:
- Autofit Column Width for readability.
- Freeze top row for easier navigation.
- Apply a structured cleaning process.

3.1 Standardizing Employee IDs

Remove emp- prefix for uniform IDs.
Use Conditional Formatting to highlight duplicates.
Action:
- Delete exact duplicates.
- Retain one ID if records differ.
Result: reduced dataset to 870+ entries.

3.2 Cleaning Names

Apply =PROPER(text) → converts names to proper case.
Example: john → John.
Replace raw columns with corrected values.

3.3 Standardizing Departments

Filter and rename department names for consistency.
Eliminates spelling variations.

3.4 Formatting Salaries & Bonuses

Convert to KES currency format.
Sort values for comparison.
Replace other currencies with standardized KES.

3.5 Dates & Employment Details

Reformat Hire Dates consistently.
Sort newest → oldest for recruitment timeline.
Standardize categorical fields: Gender, Performance Score, Marital Status, Education Level, Employee Type, Office Location, Remote Work Status.
Standardize numerical fields: Experience, Project Count, Promotion Year, Training Hours, Feedback Score.

3.6 Filtering Data

Filters allow quick inspection:

Employees in IT department.
Female employees only.
Salaries > 80,000.
Blank performance scores.

Use Number Filters for thresholds (e.g., top 10 salaries).

4. Identifying Data-Quality Issues

4.1 Conditional Formatting

Highlights problems without deleting data:

Duplicate values.
Blank cells.
Dates before today.
Values above/below thresholds.

Example: Employee ID 10407 appears twice → verify before deleting.

4.2 Find & Replace (Ctrl + H)

Quick fixes for repeated issues:

Replace “Finanace” → Finance.
Replace “N/A” → blank.
Replace unwanted characters in IDs.

Use carefully—preview matches before replacing.

5. Cleaning Text with Functions

Function	Purpose	Example
`=PROPER(text)`	Capitalizes first letter	`aNNa mWANGI` → Anna Mwangi
`=LOWER(text)`	Converts to lowercase	`JAMES` → james
`=UPPER(text)`	Converts to uppercase	`it` → IT

5.1 Removing Extra Spaces

=TRIM(A2) → removes leading, trailing, and repeated spaces.

5.2 Measuring & Extracting Text

=LEN(text) → counts characters.
=LEFT(text,n) → extracts from left.
=RIGHT(text,n) → extracts from right.
=MID(text,start,n) → extracts middle.
=CONCAT(text1,text2,...) → joins text.

6. Numerical & Statistical Functions

6.1 Arithmetic Operators

+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponent

Example:

5% salary increase → =G2*0.05
New salary → =G2+(G2*0.05)

6.2 Aggregate Functions

=SUM(range) → total
=AVERAGE(range) → mean
=MEDIAN(range) → middle value
=MODE(range) → most frequent
=MIN(range) → lowest
=MAX(range) → highest

7. Counting & Conditional Analysis

7.1 Count Functions

COUNT → numeric cells only.
COUNTA → nonblank cells.
COUNTBLANK → empty cells.

7.2 Conditional Counting

COUNTIF(range,"IT") → IT employees.
COUNTIFS(range1,"IT",range2,"Female") → Female IT employees.

7.3 Conditional Aggregation

SUMIF → total salaries by condition.
SUMIFS → multiple conditions.
AVERAGEIF → average by condition.
AVERAGEIFS → average with multiple conditions.

8. Dates & Time-Based Cleaning

=TODAY() → current date.
=NOW() → current date & time.
=YEAR(F2) → extract year.
=MONTH(F2) → extract month.
=DAY(F2) → extract day.

8.1 DATEDIF

Tenure in days → =DATEDIF(F2,TODAY(),"d")
Months → "m"
Years → "y"

8.2 NETWORKDAYS

Working days between dates → =NETWORKDAYS(F2,TODAY())

9. Recommended Cleaning Workflow

Preserve raw data.
Copy into cleaned worksheet.
Inspect with filters & conditional formatting.
Identify duplicates visually.
Standardize text with TRIM, PROPER, LOWER, UPPER.
Validate text with LEN, LEFT, RIGHT, MID, CONCAT.
Check completeness with COUNT functions.
Summarize with COUNTIFS, SUMIFS, AVERAGEIFS.
Use date functions for tenure/workdays.
Review, document assumptions, then analyze.

Conclusion

Data cleaning must happen before interpretation.

Conditional formatting → detect issues.
Filters → inspect subsets.
Find & Replace → correct repeated values.
Text functions → standardize labels.
Aggregate functions → summarize numbers.
Conditional functions → reveal group patterns.
Date functions → calculate time-based insights.

By following a structured workflow, analysts ensure datasets are consistent, reliable, and ready for advanced analysis like PivotTables, dashboards, and statistical modeling.

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

Mohammed Swaleh — Sat, 22 Aug 2026 17:52:45 +0000

Introduction

I have always used the term Git, Github and Git bash interchangeably, thinking they refer to the same thing, but I have come to learn and understand that GitHub is an online platform on the internet where developers store and share their projects, or repositories. A repository, normally called a repo, is a project being tracked by Git. Git is a version control system that runs on the local computer and can be used to track checkpoints created in a project. Therefore, Git is the tool used to track changes in a project, while SSH, which stands for Secure Shell, provides a secure way to connect your git running on your computer to GitHub on the internet. _ Git Bash _ is a tool or an application that can be used to run Git commands

This guide explains how to upload a project from a local folder on your computer to GitHub.

1. Create a GitHub Account

Go to (https://github.com/) and sign up for an account if you do not already have one.

(https://github.com/signup)

You will normally provide your email address, password and username for the github account. After creating the account, verify your email address.

After signing in, create a new repository. A repository is a storage space for your project.

When creating the repository, provide a name for the repo, then click Create repository.

2. Install Git on your windows PC

First, install Git on your computer from the official Git website:

[Download Git] (https://git-scm.com/downloads)

After downloading the installer:

Open your Downloads folder.
Find the downloaded Git installer.
Double-click it.
Allow Windows to run the installer.
Go through the installation screens.
For a normal beginner installation, you can generally keep the recommended/default options.
Complete the installation.

One important tool installed with Git for Windows is Git Bash. After installing it, open git bash and run on the command prompt to check that Git works:

git --version

If Git is installed correctly, you will see its version number.

git version 2.55.0.windows.4

3. Configure Git Username and Email Address

Next, you need to tell git your username and email address associated with your GitHub account. You type on the command prompt

git config --global user.name "Your Full Name" to configure your name and
git config --global user.email "Your Email address" to configure your email

Example:
git config --global user.name "Mohammed Swaleh"

git config --global user.email mswaleh@gmail.com

4. Configure main as the Default Branch

Next, you need to Set main as the Default Branch
Run:
git config --global init.defaultBranch main

This tells Git to use main, as the default initial branch for new repositories.

5. Verify Your Git Configuration

To check your name:

Type in Git Bash
git config --global user.name

To check your email:
type
git config --global user.email

To check all global configuration:
git config --global --list

You should see something similar to:

user.name=Mohammed Swaleh
user.email=mswaleh@gmail.com
init.defaultbranch=main

6. Set Up Secure Shell (SSH)

SSH allows Git to connect securely to your GitHub account without asking for your password every time.

Confirm SSH key exists

Open your terminal and check whether SSH key exists
Type
ls -al ~/.ssh

You can see

id_ed25519
id_ed25519.pub

or older files such as:
id_rsa id_rsa.pub

if you encounter this error message:
No such file or directory

This means that the SSH key does not exist and you need to create an SSH key

Create an SSH key

Type in the terminal:
ssh-keygen -t ed25519 -C "your_email@example.com"

Replace:
your_email@example.com, with the email associated with your GitHub account.

Example:
ssh-keygen -t ed25519 -C "mswaleh@gmail.com"

Press Enter to accept the suggested file location. You can also create a passphrase for extra security.

Confirm That the Key Was Created

type:
ls -al ~/.ssh

You should now see:

id_ed25519
id_ed25519.pub

7. ADD THE private SSH KEY TO THE SSH AGENT

The SSH agent helps manage your private SSH key and can remember its passphrase during your session.

To Start the SSH Agent for windows,
open:
PowerShell, as Administrator.

On the powershell terminal,

Run:
Get-Service -Name ssh-agent | Set-Service -StartupType Manual

Then:

Start-Service ssh-agent

Exit powershell as an administrator, open powershell as a normal user,

Type:
ssh-add c:/Users/YOUR_USERNAME/.ssh/id_ed25519

Replace:
YOUR_USERNAME with your actual Windows username.

Example:
ssh-add c:/Users/mswaleh/.ssh/id_ed25519

If you do not know your Windows username, you can run:

whoami

You may receive something like:
DESKTOP-12345\mswaleh

Your username is therefore, in this example:
mswaleh

8. Windows — Copy the Public SSH Key

Open Git Bash.

Run
cat ~/.ssh/id_ed25519.pub | clip
The public key should now be in your clipboard.

If that does not work, display it:
cat ~/.ssh/id_ed25519.pub

You will see one long line similar to:

ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAA... mswaleh@gmail.com

Copy the entire line.

9. ADD THE SSH KEY TO GITHUB

On GitHub:

Click your profile picture.
Select Settings.
Open SSH and GPG keys.
Click New SSH key.
Give the key a name.
Paste your public key.
Click Add SSH key.

GitHub may ask you to confirm your email account.
Complete the confirmation.
Your SSH public key should then appear in your GitHub account.

10. TEST THE SSH CONNECTION

Test GitHub SSH Authentication
Return to:
Git Bash on Windows,

Run:
ssh -T git@github.com
If everything is correctly configured, you should get a message similar to:

Hi YOUR_USERNAME! You've successfully authenticated, but GitHub does not provide shell access. This is a success message.
It means:
Computer
↓
Private SSH Key
↓
GitHub verifies the public key
↓
Authentication Successful

11. Open Your Local Project

Use the terminal to move into your project folder.

For example:

cd path/to/your-project

You can check the files in the folder with:

ls

12. Turn the Folder Into a Git Repository

Inside the project folder, run:

git init

This creates a hidden .git folder. Git uses this folder to track changes in your project.

Now check the status of your files:

git status

13. Add a README File

A README file explains what your project is about. Create a file called README.md and write a short description.

Example:

# My First GitHub Project

This is my first project uploaded to GitHub using Git and SSH.

14. Save Your Files in Git

Add all project files:

git add .

The period means "add all files in this folder".

Create your first commit:

git commit -m "Initial commit"

A commit is a saved version of your project. The message explains what was changed.

15. Connect the Local Folder to GitHub

On your GitHub repository page, copy the SSH address. It will look similar to this:

git@github.com:your-username/your-repository.git

Connect your local project to the GitHub repository:

git remote add origin git@github.com:your-username/your-repository.git

You can check the connection with:

git remote -v

16. Upload the Project

Rename your main branch to main:

git branch -M main

Then upload the project to GitHub:

git push -u origin main

The push command sends your committed files from your computer to GitHub.

Refresh your GitHub repository page. Your project files should now be visible online.

17. Updating the Project Later

Whenever you make changes, use these three commands:

git add .
git commit -m "Describe your changes"
git push

For example:

git add .
git commit -m "Add homepage design"
git push

This saves the new changes locally and uploads them to GitHub.

18. Common Problems

GitHub rejects the push

Make sure the repository address is correct:

git remote -v

You can replace the incorrect address with:

git remote set-url origin git@github.com:your-username/your-repository.git

SSH permission denied

Check that your SSH key has been added to GitHub and test the connection again:

ssh -T git@github.com

Files are not uploaded

Check the repository status:

git status

Then add, commit, and push the files:

git add .
git commit -m "Add project files"
git push

Final Git Workflow

The basic workflow is:

git init
git add .
git commit -m "Initial commit"
git remote add origin git@github.com:your-username/your-repository.git
git branch -M main
git push -u origin main

After the first upload, you usually only need:

git add .
git commit -m "Describe your changes"
git push

Conclusion

Moving a project from a local folder to GitHub may seem difficult at first, but the process becomes simple with practice. Git tracks your work, GitHub stores it online, and SSH provides a secure connection between your computer and your GitHub account.

DEV Community: Mohammed Swaleh

SQL for Hospital Data Analysis: Window Functions, Joins, CTEs, and Subqueries

Introduction

1. SQL Window Functions

How OVER() works

PARTITION BY

ORDER BY in a window

Window frames

Ranking functions

ROW_NUMBER()

RANK()

DENSE_RANK()

NTILE()

Value comparison functions

LAG()

LEAD()

FIRST_VALUE()

LAST_VALUE()

Aggregate window functions

2. SQL Joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

Join comparison

3. Common Table Expressions

Basic CTE

Multiple CTEs

Recursive CTE

CTEs with joins and aggregation

CTEs versus subqueries

4. SQL Subqueries

Scalar subquery

Non-correlated subquery

Correlated subquery

Nested subqueries

Subqueries in SELECT

Subqueries in FROM

Subqueries in WHERE

IN

EXISTS

ANY

ALL

Practical Combined Example

Conclusion

DBeaver SQL Summary Learning Notes

DBeaver & SQL Summary Learning Notes

Learning Roadmap

1. DBeaver and the SQL Editor

2. Create and Select a Schema

3. Tables, Columns, Keys and Constraints

Common Data Types

Constraints

Example

4. Insert Records

5. Retrieve Data with SELECT

6. Filtering Rows

Comparison Operators

Logical Operators

IN, NOT IN, NULL, DISTINCT

7. Sorting and Limiting

8. Aggregate Functions

9. GROUP BY and HAVING

10. Joins

INNER JOIN

LEFT JOIN

11. Modify Records

UPDATE

DELETE

12. Alter Table

13. DELETE vs TRUNCATE vs DROP

14. SQL Order

15. Practical Patterns

16. Common Mistakes

17. Suggested Practice Sequence

18. Quick Reference

From Raw Data to Business Decisions: JCars Logistics Power BI Project

Introduction

How `OVER()` works

`PARTITION BY`

`ORDER BY` in a window

`ROW_NUMBER()`

`RANK()`

`DENSE_RANK()`

`NTILE()`

`LAG()`

`LEAD()`

`FIRST_VALUE()`

`LAST_VALUE()`

`INNER JOIN`

`LEFT JOIN`

`RIGHT JOIN`

`FULL OUTER JOIN`

`CROSS JOIN`

`SELF JOIN`

Subqueries in `SELECT`

Subqueries in `FROM`

Subqueries in `WHERE`

`IN`

`EXISTS`

`ANY`

`ALL`