DEV Community: asma Salah

SQL Window Functions, Joins, CTEs and Subqueries Explained with a Hospital Dataset

asma Salah — Mon, 19 Oct 2026 16:56:39 +0000

What this article covers

Window functions (ROW_NUMBER, RANK, LAG, NTILE, running totals and frames)
Joins (INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF)
Common table expressions (basic, multiple, recursive)
Subqueries (scalar, correlated, IN, EXISTS, ANY, ALL)

The dataset

The database lives in a schema called morning and follows a star-style design. One fact table, visits, connects to dimension tables:

visits: one row per hospital visit (date, bill, admission type, stay length, triage level, satisfaction score)
patients, doctors, department, wards, diagnosis, procedures: descriptive tables linked by IDs

It has 30 visits, 25 patients and 4 doctors. Run SET search_path TO morning; first so the queries below work as written.

1. SQL Window Functions

A normal aggregate with GROUP BY collapses rows. Summing bills by department gives 4 rows, one per department. A window function does the same kind of calculation but keeps every row, so each visit can sit next to its department total. That is the idea behind everything below.

How OVER, PARTITION BY, ORDER BY and frames work

OVER() turns a function into a window function. An empty OVER() means "use the whole result set".
PARTITION BY splits rows into groups, and the calculation restarts in each group. It works like GROUP BY without collapsing rows. PARTITION BY doctor_id restarts for each doctor.
ORDER BY inside OVER sets the row order within each partition. Ranking functions, LAG and LEAD need it, and it turns aggregates like SUM into running totals.
The window frame is which rows around the current row are included, for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. With an ORDER BY and no frame, the default runs from the first row of the partition to the current row and includes ties. With no ORDER BY, the frame is the whole partition.
When it runs: after WHERE, GROUP BY and HAVING, and before the final ORDER BY. This means you can't filter on a window function in WHERE. Wrap the query in a subquery or CTE and filter outside it.

ROW_NUMBER(), RANK() and DENSE_RANK()

All three number rows by an order. They differ in how they treat ties.

SELECT visit_id, patient_id, length_of_stay_days AS stay,
  ROW_NUMBER() OVER (ORDER BY length_of_stay_days DESC) AS row_num,
  RANK()       OVER (ORDER BY length_of_stay_days DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY length_of_stay_days DESC) AS dense_rnk
FROM visits
WHERE admission_type IN ('Inpatient', 'Emergency')
ORDER BY length_of_stay_days DESC, visit_id;

Look at the two visits tied at 3 days:

ROW_NUMBER never ties. It gives 4 and 5, and which tied row gets which number is arbitrary, so add a tiebreaker like visit_id to the ORDER BY if you need it stable.
RANK gives both rows 4, then skips to 6.
DENSE_RANK gives both rows 4, then continues with 5, with no gaps.

Practical use: each doctor's most recent visit. ROW_NUMBER is the right choice because you want exactly one row per doctor.

SELECT * FROM (
  SELECT doctor_id, visit_id, visit_date, total_bill_kes,
    ROW_NUMBER() OVER (PARTITION BY doctor_id ORDER BY visit_date DESC, visit_id DESC) AS rn
  FROM visits
) t
WHERE rn = 1;

This returns 4 rows: visits 1030 (doctor 101), 1029 (102), 1027 (103) and 1028 (104). The subquery is needed because of the "no window functions in WHERE" rule.

NTILE()

NTILE(n) splits the ordered rows into n roughly equal buckets. This puts every visit into a billing quartile.

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

With 30 visits, the buckets hold 8, 8, 7 and 7 rows, because the extra rows go to the earlier buckets. Quartile 1 covers bills from KES 1,800 to 3,500, and quartile 4 covers KES 28,000 to 82,000. It answers questions like "which visits are in the top 25% by cost?"

LAG() and LEAD()

LAG reads the previous row in the partition, and LEAD reads the next one. Five patients came back for a second visit, so we can compare their bills.

SELECT patient_id, visit_id, visit_date, total_bill_kes,
  LAG(total_bill_kes)  OVER (PARTITION BY patient_id ORDER BY visit_date) AS prev_bill,
  LEAD(total_bill_kes) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_bill
FROM visits
WHERE patient_id IN (1, 5, 10)
ORDER BY patient_id, visit_date;

The first visit has no previous row, so LAG returns NULL. You can pass an offset and a default, as in LAG(total_bill_kes, 1, 0). Then total_bill_kes - LAG(total_bill_kes, 1, 0) OVER (...) gives the change between visits. Patient 5's second bill is KES 3,600 higher than the first.

FIRST_VALUE() and LAST_VALUE()

These return the first or last value in the window frame. Here is doctor 101's first and last bill, with a common mistake shown next to the fix.

SELECT doctor_id, visit_id, visit_date, total_bill_kes,
  FIRST_VALUE(total_bill_kes) OVER w AS first_bill,
  LAST_VALUE(total_bill_kes)  OVER w AS last_default,
  LAST_VALUE(total_bill_kes)  OVER (PARTITION BY doctor_id ORDER BY visit_date, visit_id
     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full
FROM visits
WHERE doctor_id = 101
WINDOW w AS (PARTITION BY doctor_id ORDER BY visit_date, visit_id);

last_default just repeats the current row's bill. The default frame stops at the current row, so the "last" row it can see is the current one. This is the biggest LAST_VALUE trap. To get the real last value, widen the frame to UNBOUNDED FOLLOWING, as last_full does. FIRST_VALUE doesn't have this problem because the frame always starts at the first row.

SUM(), AVG(), COUNT(), MIN() and MAX() as window functions

Any aggregate becomes a window function when you add OVER. This lets you compare each visit to its department.

SELECT visit_id, department_id, total_bill_kes,
  SUM(total_bill_kes) OVER (PARTITION BY department_id) AS dept_total,
  ROUND(AVG(total_bill_kes) OVER (PARTITION BY department_id), 2) AS dept_avg,
  COUNT(*)            OVER (PARTITION BY department_id) AS dept_visits,
  MIN(total_bill_kes) OVER (PARTITION BY department_id) AS dept_min,
  MAX(total_bill_kes) OVER (PARTITION BY department_id) AS dept_max,
  ROUND(100.0 * total_bill_kes / SUM(total_bill_kes) OVER (PARTITION BY department_id), 1) AS pct_of_dept
FROM visits
WHERE visit_id <= 1010;

For department 10 (Cardiology), the three rows returned are:

Window functions only see rows that survive WHERE. Cardiology's real total across all 30 visits is KES 210,100. Here it shows 83,500 because the query is limited to the first 10 visits. Remove the WHERE to get full-hospital figures. The pct_of_dept column shows a single heart-failure admission making up 91% of this slice of the department's revenue.

Compare this with GROUP BY, which would return only 4 rows (one per department) with totals of 210,100, 97,800, 105,200 and 74,900. The window version keeps all the detail and adds the totals alongside it.

Window frames: running totals and moving averages

SELECT visit_id, visit_date, total_bill_kes,
  SUM(total_bill_kes) OVER (ORDER BY visit_date) AS running_default,
  SUM(total_bill_kes) OVER (ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows,
  ROUND(AVG(total_bill_kes) OVER (ORDER BY visit_date, visit_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3
FROM visits
ORDER BY visit_date, visit_id
LIMIT 6;

running_default uses the default frame, which includes ties. Visits 1001 and 1002 share a date, so both show 6,000.
running_rows uses ROWS, which counts physical rows, so it grows one visit at a time. For a true row-by-row running total, use ROWS and add a tiebreaker to the ORDER BY.
moving_avg_3 averages the current row and the 2 before it, giving a rolling 3-visit average. The first rows average over fewer rows because there aren't 2 previous ones yet.

2. SQL Joins

In this database, the visits table stores only IDs. A patient's name lives in patients, a doctor's name in doctors, and a diagnosis name in diagnosis. Almost every useful question needs a join, which combines rows from two tables based on a related column.

Join	Rows kept
`INNER JOIN`	Only rows that match in both tables
`LEFT JOIN`	All rows from the left table, plus matches from the right (`NULL` where none)
`RIGHT JOIN`	All rows from the right table, plus matches from the left
`FULL OUTER JOIN`	All rows from both tables, matched where possible
`CROSS JOIN`	Every row of one table paired with every row of the other
`SELF JOIN`	A table joined to itself, using two aliases

INNER JOIN

An inner join keeps only rows with a match on both sides. Use it when you only care about records that exist in all the tables involved. Here it builds a readable visit report from four tables.

SELECT v.visit_id, p.patient_name, d.doctor_name, dg.diagnosis_name, v.total_bill_kes
FROM visits v
INNER JOIN patients  p  ON p.patient_id   = v.patient_id
INNER JOIN doctors   d  ON d.doctor_id    = v.doctor_id
INNER JOIN diagnosis dg ON dg.diagnosis_id = v.diagnosis_id
ORDER BY v.visit_id
LIMIT 5;

The full query returns all 30 visits, because every visit has a valid patient, doctor and diagnosis. Only unmatched rows would disappear.

LEFT JOIN

A left join keeps every row from the left table and fills in NULL where the right table has no match. In this data every diagnosis was used and every patient visited, so a plain left join would look the same as an inner join. To see the difference, I asked a real question: which diagnoses led to an inpatient admission?

SELECT dg.diagnosis_id, dg.diagnosis_name, v.visit_id, v.admission_type
FROM diagnosis dg
LEFT JOIN visits v
  ON v.diagnosis_id = dg.diagnosis_id
 AND v.admission_type = 'Inpatient'
ORDER BY dg.diagnosis_id, v.visit_id;

The result has 20 rows: 9 matched visits and the rest NULL. Two things to notice:

There are only 18 diagnoses, but the result has 20 rows. Fracture and Heart Failure each matched two inpatient visits, so they repeat. A join can change the number of rows in your result.
The admission_type filter is in the ON clause on purpose. If you move it to WHERE, the NULL rows get filtered out and the query behaves like an inner join.

The anti-join pattern. Add WHERE v.visit_id IS NULL to keep only rows with no match:

SELECT dg.diagnosis_id, dg.diagnosis_name
FROM diagnosis dg
LEFT JOIN visits v
  ON v.diagnosis_id = dg.diagnosis_id
 AND v.admission_type = 'Inpatient'
WHERE v.visit_id IS NULL;

This returns 11 diagnoses that never led to an inpatient admission: Hypertension, Malaria, Diabetes, Asthma, Back Pian, Gastritis, Flu, Anaemia, Sprain, Migraine and Chest Pain.

RIGHT JOIN

A right join is a left join with the tables swapped. It keeps every row from the right table.

SELECT COUNT(*) AS total_rows, COUNT(v.visit_id) AS matched_rows
FROM visits v
RIGHT JOIN diagnosis dg
  ON v.diagnosis_id = dg.diagnosis_id
 AND v.admission_type = 'Inpatient';

The result is 20 total rows and 9 matched rows, exactly the same as the left join above. Most people rewrite right joins as left joins by swapping the table order, because "start from the main table and join outward" is easier to read.

FULL OUTER JOIN

A full outer join keeps everything from both sides. Rows with no match on one side get NULLs there. It is useful when you are comparing two sets and want to see what is in one, the other, or both. Here I compare diagnoses seen in inpatient visits with diagnoses seen in outpatient visits.

SELECT i.diagnosis_id AS inpatient_dx, o.diagnosis_id AS outpatient_dx,
  CASE WHEN i.diagnosis_id IS NOT NULL AND o.diagnosis_id IS NOT NULL THEN 'both'
       WHEN i.diagnosis_id IS NOT NULL THEN 'inpatient only'
       ELSE 'outpatient only' END AS seen_as
FROM (SELECT DISTINCT diagnosis_id FROM visits WHERE admission_type = 'Inpatient')  i
FULL OUTER JOIN
     (SELECT DISTINCT diagnosis_id FROM visits WHERE admission_type = 'Outpatient') o
  ON i.diagnosis_id = o.diagnosis_id;

seen_as	diagnoses
both	1 (Typhoid, 506)
inpatient only	6
outpatient only	10

The result has 17 rows. Typhoid is the only diagnosis that appeared in both inpatient and outpatient visits. Chest Pain (518) is missing from both sides because its only visit was an Emergency.

CROSS JOIN

A cross join pairs every row of one table with every row of the other, with no ON condition. 4 departments times 4 triage levels gives 16 combinations.

SELECT g.department_name, g.triage_level, COUNT(v.visit_id) AS visits
FROM (
  SELECT d.department_id, d.department_name, t.triage_level
  FROM department d
  CROSS JOIN (VALUES ('Red'), ('Orange'), ('Yellow'), ('Green')) AS t(triage_level)
) g
LEFT JOIN visits v
  ON v.department_id = g.department_id AND v.triage_level = g.triage_level
GROUP BY g.department_name, g.triage_level;

The practical use is building a complete grid first, then left joining the data onto it. Without the cross join, combinations with no visits (like Cardiology with Orange) would be missing from the report instead of showing 0. Be careful with cross joins on big tables, because the row count is the product of both sizes.

SELF JOIN

A self join joins a table to itself using two aliases, so you can compare rows within the same table. Which patients come from the same county?

SELECT a.patient_name AS patient_1, b.patient_name AS patient_2, a.county
FROM patients a
JOIN patients b
  ON a.county = b.county
 AND a.patient_id < b.patient_id;

The a.patient_id < b.patient_id condition does two jobs. It stops a patient being paired with themselves, and it stops each pair appearing twice (A-B and B-A).

Another example compares doctors by experience:

SELECT a.doctor_name AS more_experienced, b.doctor_name AS less_experienced,
       a.doctor_experience_years - b.doctor_experience_years AS gap
FROM doctors a
JOIN doctors b ON a.doctor_experience_years > b.doctor_experience_years
ORDER BY gap DESC
LIMIT 3;

Which join should I use?

Only records that exist in both tables: INNER JOIN
Everything from the main table, matched or not: LEFT JOIN
Find records with no match: LEFT JOIN ... WHERE right_key IS NULL
Compare two sets and see what is in one, the other, or both: FULL OUTER JOIN
Build every combination, such as a report grid: CROSS JOIN
Compare rows within one table: SELF JOIN

3. Common Table Expressions (CTEs)

A CTE is a named, temporary result that you define with WITH at the top of a query and then use like a table in the main query. It only exists for that one query. The main benefit is readability: instead of nesting one query inside another, you name each step and read the query from top to bottom.

Basic CTE

This query finds the number of visits and total revenue for each department. The CTE does the aggregation, and the main query adds the department names.

WITH dept_revenue AS (
  SELECT department_id, SUM(total_bill_kes) AS revenue, COUNT(*) AS visits
  FROM visits
  GROUP BY department_id
)
SELECT d.department_name, r.visits, r.revenue
FROM dept_revenue r
JOIN department d ON d.department_id = r.department_id
ORDER BY r.revenue DESC;

dept_revenue is calculated first, then joined to department like a normal table. Cardiology brings in the most revenue.

Multiple CTEs

You can define several CTEs in one WITH clause, separated by commas. Later ones can use earlier ones. This query finds doctors whose average patient satisfaction is above the hospital average.

WITH doctor_stats AS (
  SELECT doctor_id, COUNT(*) AS visits, SUM(total_bill_kes) AS revenue,
         ROUND(AVG(patient_satisfaction_score), 2) AS avg_satisfaction
  FROM visits
  GROUP BY doctor_id
),
hospital_avg AS (
  SELECT ROUND(AVG(patient_satisfaction_score), 2) AS avg_satisfaction
  FROM visits
)
SELECT d.doctor_name, s.visits, s.revenue, s.avg_satisfaction, h.avg_satisfaction AS hospital_avg
FROM doctor_stats s
JOIN doctors d ON d.doctor_id = s.doctor_id
CROSS JOIN hospital_avg h
WHERE s.avg_satisfaction > h.avg_satisfaction
ORDER BY s.avg_satisfaction DESC;

The hospital average is 4.28. Dr. Sarah Wanjiku (4.00) and Dr. Peter Otieno (4.23) are below it, so they are filtered out. hospital_avg returns a single row, so a CROSS JOIN attaches it to every doctor row for comparison.

CTEs with joins and aggregations

This query looks at admitted patients (inpatient and emergency) and summarizes them by diagnosis category.

WITH admitted AS (
  SELECT * FROM visits
  WHERE admission_type IN ('Inpatient', 'Emergency')
)
SELECT dg.diagnosis_category,
       COUNT(*) AS admissions,
       ROUND(AVG(a.length_of_stay_days), 1) AS avg_stay_days,
       SUM(a.total_bill_kes) AS revenue
FROM admitted a
JOIN diagnosis dg ON dg.diagnosis_id = a.diagnosis_id
GROUP BY dg.diagnosis_category
ORDER BY revenue DESC;

Chronic diseases (heart failure) bring the most revenue and the longest stays. The result also exposes a data quality problem: "Infectious" and "Infectious Disease" appear as separate categories, because Typhoid was entered with a shorter category name. Grouping makes inconsistent labels like this easy to spot.

Recursive CTEs

A recursive CTE refers to itself. It has two parts joined by UNION ALL:

The anchor member runs once and gives the starting rows.
The recursive member refers to the CTE's own name. It runs again and again on the rows from the previous step, and the CTE stops when a step returns no new rows.

Here is the simplest possible example, counting from 1 to 5:

WITH RECURSIVE n AS (
  SELECT 1 AS num                          -- anchor
  UNION ALL
  SELECT num + 1 FROM n WHERE num < 5      -- recursive step with a stopping condition
)
SELECT * FROM n;

The result is the numbers 1, 2, 3, 4, 5. The WHERE num < 5 is the stopping condition. Without one, the query would loop forever, so always include it.

A practical use in the hospital data: a calendar. Visits exist for 1 to 15 June. If I group visits by date, days with no visits simply don't appear. A recursive CTE can generate every date, and a LEFT JOIN fills the gaps with zeros.

WITH RECURSIVE calendar AS (
  SELECT DATE '2026-06-01' AS day
  UNION ALL
  SELECT day + 1 FROM calendar WHERE day < DATE '2026-06-17'
)
SELECT c.day, COUNT(v.visit_id) AS visits, COALESCE(SUM(v.total_bill_kes), 0) AS revenue
FROM calendar c
LEFT JOIN visits v ON v.visit_date = c.day
GROUP BY c.day
ORDER BY c.day;

The hospital had 2 visits every day from 1 to 15 June. The calendar adds 16 and 17 June with zero visits, which a plain GROUP BY would have left out. Recursive CTEs are also the standard tool for hierarchical data such as organization charts or category trees, where each row points to its parent.

CTEs vs subqueries

Both can answer the same question. Here is "which visits cost more than the average bill?" done both ways.

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

-- CTE
WITH avg_bill AS (SELECT AVG(total_bill_kes) AS avg_bill FROM visits)
SELECT v.visit_id, v.total_bill_kes
FROM visits v, avg_bill
WHERE v.total_bill_kes > avg_bill.avg_bill;

Both return the same 10 visits, with the average bill at KES 16,266.67. The highest are visit 1017 (KES 82,000) and visit 1010 (KES 76,000). The differences show up as queries get bigger:

	CTE	Subquery
Readability	Named steps, read top to bottom	Nested inside the query, harder to follow when deep
Reuse	Define once, reference many times	Must be repeated each time it is needed
Recursion	Supported (`WITH RECURSIVE`)	Not possible
Where it lives	Before the main query	Inline in `SELECT`, `FROM` or `WHERE`
Best for	Multi-step logic, reuse, recursion	Short, one-off lookups

Reuse example. This query uses dept_revenue twice, once for each department's revenue and once for the hospital total:

WITH dept_revenue AS (
  SELECT department_id, SUM(total_bill_kes) AS revenue
  FROM visits GROUP BY department_id
)
SELECT d.department_name, r.revenue,
       ROUND(100.0 * r.revenue / (SELECT SUM(revenue) FROM dept_revenue), 1) AS pct_of_hospital
FROM dept_revenue r
JOIN department d ON d.department_id = r.department_id
ORDER BY r.revenue DESC;

department_name	revenue	pct_of_hospital
Cardiology	210100	43.1
Orthopedics	105200	21.6
General Medicine	97800	20.0
Pediatrics	74900	15.3

Cardiology accounts for 43.1% of the hospital's KES 488,000 total revenue.

On performance: CTEs are not automatically faster or slower. In PostgreSQL 12 and later, a simple non-recursive CTE that is used once is generally treated like an inline subquery, and you can control this with MATERIALIZED or NOT MATERIALIZED. Older versions always computed the CTE separately first. So choose between them for readability, not speed.

4. SQL Subqueries

A subquery is a query inside another query, written in parentheses. It can sit in the SELECT, FROM or WHERE clause, and the outer query uses its result. The main kinds are:

Scalar subquery: returns exactly one value (one row, one column)
Non-correlated subquery: runs on its own, independent of the outer query
Correlated subquery: refers to a column from the outer query, so it depends on the current row
Nested subquery: a subquery inside another subquery

Scalar subqueries

A scalar subquery returns a single value, so you can use it anywhere a single value fits. Which visit had the highest bill?

SELECT visit_id, patient_id, total_bill_kes
FROM visits
WHERE total_bill_kes = (SELECT MAX(total_bill_kes) FROM visits);

The subquery returns 82000, and the outer query finds the visit with that bill: visit 1017, patient 16, KES 82,000.

A scalar subquery in the SELECT list adds a computed column. This shows how far each bill is from the hospital average:

SELECT visit_id, total_bill_kes,
  (SELECT ROUND(AVG(total_bill_kes), 2) FROM visits) AS hospital_avg,
  ROUND(total_bill_kes - (SELECT AVG(total_bill_kes) FROM visits), 2) AS diff
FROM visits
ORDER BY visit_id
LIMIT 5;

If a scalar subquery returns more than one row, PostgreSQL throws an error.

Non-correlated subqueries and IN

A non-correlated subquery runs once on its own, and the outer query uses its result. Both scalar examples above are non-correlated. Here is a list version: which patients have had an inpatient visit?

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

The inner query produces a list of patient IDs, and IN keeps patients whose ID is in that list. NOT IN gives the opposite: 16 patients with no inpatient visit. Be careful with NOT IN. If the subquery returns even one NULL, NOT IN returns no rows at all. It is safe here because visits.patient_id can't be NULL, but NOT EXISTS (below) avoids the problem entirely.

Correlated subqueries

A correlated subquery uses a column from the outer query, so it is evaluated against each outer row. Which visits cost more than that doctor's own average bill?

SELECT v.visit_id, v.doctor_id, v.total_bill_kes,
  (SELECT ROUND(AVG(v2.total_bill_kes), 2)
   FROM visits v2
   WHERE v2.doctor_id = v.doctor_id) AS doctor_avg
FROM visits v
WHERE v.total_bill_kes > (
  SELECT AVG(v2.total_bill_kes)
  FROM visits v2
  WHERE v2.doctor_id = v.doctor_id
)
ORDER BY v.doctor_id, v.total_bill_kes DESC;

For each visit, the subquery recalculates the average for that visit's doctor (v2.doctor_id = v.doctor_id). That is what makes it correlated. A non-correlated subquery could only give one overall average, not one per doctor. (Window functions from Part 1 can answer this too, with AVG(...) OVER (PARTITION BY doctor_id).)

EXISTS and NOT EXISTS

EXISTS is true if the subquery returns at least one row. What the subquery selects doesn't matter, so SELECT 1 is the convention. Which patients have had an emergency visit?

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.admission_type = 'Emergency'
);

The result is one patient: Peter Ouma (ID 10). NOT EXISTS flips it. Patients with no inpatient visit, using NOT EXISTS, gives 16 patients, the same as NOT IN, but without the NULL problem.

IN vs EXISTS:

IN compares a value against a list. It reads naturally for short, simple lists.
EXISTS only checks whether a matching row exists, and can stop at the first one. It is the safer choice for "does a related record exist?" questions, and NOT EXISTS is safer than NOT IN.

ANY and ALL

ANY and ALL compare a value against every value the subquery returns. ALL means the comparison must be true for every value. ANY means it must be true for at least one.

Which visits cost more than every Pediatrics visit?

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

Pediatrics bills range from KES 1,800 to 42,000, so only two visits beat all of them: visit 1017 (82,000) and visit 1010 (76,000). > ALL works like > MAX.

SELECT COUNT(*)
FROM visits
WHERE total_bill_kes > ANY (
  SELECT total_bill_kes FROM visits WHERE department_id = 40
);

This returns 29. A bill only has to beat at least one Pediatrics bill, so the cutoff is the smallest one (1,800). Only the KES 1,800 visit itself fails. > ANY works like > MIN.

= ANY is the same as IN. For example, visits handled by doctors with more than 10 years of experience:

SELECT visit_id, doctor_id
FROM visits
WHERE doctor_id = ANY (
  SELECT doctor_id FROM doctors WHERE doctor_experience_years > 10
);

This returns 15 visits, from Dr. Brian Mwangi (12 years) and Dr. Peter Otieno (15 years).

Subqueries in SELECT, FROM and WHERE

In SELECT: a scalar subquery adds a computed column, like the hospital_avg column above.
In WHERE: filters rows using a comparison, IN, EXISTS, ANY or ALL, as in the examples above.
In FROM: the subquery acts as a temporary table, called a derived table. PostgreSQL requires it to have an alias.

SELECT d.doctor_name, t.visits, t.avg_bill
FROM (
  SELECT doctor_id, COUNT(*) AS visits, ROUND(AVG(total_bill_kes), 2) AS avg_bill
  FROM visits
  GROUP BY doctor_id
) t
JOIN doctors d ON d.doctor_id = t.doctor_id
ORDER BY t.avg_bill DESC;

The subquery summarizes visits per doctor first, then the outer query joins it to doctor names.

Nested subqueries

A subquery can contain another subquery. Which patients were seen in the Cardiology department?

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

It runs from the inside out. The innermost query finds the Cardiology department ID, the middle one finds the patients with visits in that department, and the outer one gets their names. This works, but more than two levels of nesting gets hard to read. At that point a CTE or joins is usually clearer.

Conclusion

Working through these four topics on one dataset showed me how they relate:

Window functions add calculations (ranks, running totals, comparisons with the previous row) without losing row detail.
Joins combine tables, and they can change the number of rows in a result, as the left join on diagnoses showed.
CTEs let me name each step of a query so it reads top to bottom, and they are the only option when I need recursion.
Subqueries are the quickest way to answer a one-off question inside another query, and correlated subqueries can compare each row against its own group.

Many questions can be answered more than one way. "Which visits cost more than their doctor's average?" works as a correlated subquery or as a window function, and either can be wrapped in a CTE. The skill is choosing the version that is easiest to read and check. The checking matters too: running these queries also exposed data problems I would have missed otherwise, like the two spellings of "Infectious" in the diagnosis categories.

References

Turning Chaos Into Insight: Cleaning and Modeling the JCars Logistics Dataset

asma Salah — Sat, 03 Oct 2026 21:00:22 +0000

Introduction

As part of my business intelligence coursework, I was given a raw, messy dataset from a fictional company called JCars Logistics, a vehicle sales and delivery business in Kenya. My task was to turn this raw data into a working Power BI solution that management could actually use to understand the business. This article walks through my process: investigating the
data, cleaning it, building a model, writing DAX measures, and designing a dashboard including the mistakes I made along the way.

Understanding the Data

Before touching anything, I looked at what one row in the dataset actually represented: one vehicle sales order. Columns captured customer details, vehicle details, pricing, discounts, delivery information, and payment status. This told me I was dealing with a flat transactional dataset that would eventually need to be split into a proper fact and dimension structure.

Data Quality Audit

Raw data is never clean, and this dataset proved it. Some of the most significant issues I found:

Negative discount values (e.g., -0.1 instead of 0.1): likely sign entry errors that would have inflated revenue instead of reducing it
Discount values over 100% (e.g., 1.20): almost certainly misplaced decimals
"Paid" transactions with blank Revenue Recorded : a direct contradiction, since confirmed payment should mean recorded revenue
Duplicate Order IDs: 276 rows but only 255 distinct order identifiers
A negative revenue value paired with an invalid customer rating (-1) on the same row, flagged as an exception rather than guessed at

For each issue, I documented what I found, why it mattered, and how I decided to handle it, full detail is in my GitHub repository's data quality audit document.

Currency Standardization

The dataset contained monetary values in multiple currencies — KES, USD, EUR, and ZAR. This was one of the trickiest parts of the project. I'll be honest: during cleaning, I removed currency symbols to convert text values into numbers, but didn't preserve which currency each row originally came from. This means I couldn't retroactively convert foreign-currency rows to KES with confidence. I've documented this as a known limitation in my project, rather than presenting unconverted figures as if they were fully accurate, an important lesson in why preserving original data markers matters before transforming values.

Cleaning in Power Query

Using Power Query, I:

Trimmed and standardized inconsistent text (customer names had extra spaces, inconsistent capitalization)
Fixed data types across numeric and date columns
Corrected the discount issues found during the audit
Recalculated missing revenue values for confirmed "Paid" orders using Unit Selling Price × Units Sold × (1 − Discount)

Power Query editor showing applied steps

Data Validation

I didn't assume cleaning automatically meant correctness. I independently recalculated revenue and compared it against the recorded values. The initial discrepancy was tiny (a few hundred KES, clearly rounding), but after investigating further with a row-level comparison, I found a larger residual gap of roughly KES -468.51M (~36% of total revenue). I documented this honestly as an unresolved limitation requiring further investigation, rather than forcing the numbers to match artificially.

Building the Data Model

I organized the flat dataset into a star schema:

CarSalesFacts (fact table)
DimCarDetails, DimLocation, DimVehicleSpecs, DimDate(dimension tables)

Model view showing relationships

One real challenge I hit: after a Power BI crash during a transformation step, my relationship between CarSalesFacts and DimCarDetails broke silently; about 96% of rows stopped matching to a car make. I only caught this because a DAX measure threw an error, which led me to rebuild the relationship properly. It was a good reminder to validate relationships after any crash or recovery, not just assume they held.

I also built a dedicated Date table (DimDate), related it to Order Date as the active relationship, and to Delivery Date as an inactive one, switched on only inside specific measures using USERELATIONSHIP, since Power BI only allows one active date path at a time.

DAX Measures

I wrote measures to support reusable business calculations rather than relying on default aggregations, including:

Total Revenue, Total Cost, Gross Profit, Gross Profit Margin
Total Orders (using DISTINCTCOUNT to handle duplicate Order IDs correctly)
Return Rate, Cancellation Rate, Avg Customer Rating
Revenue YoY % (year-over-year growth, using SAMEPERIODLASTYEAR)

Executive Dashboard

Page 1 is a single-page overview for management: KPI cards for revenue, gross profit, margin, orders, units sold, and avg rating, plus a revenue trend by year, revenue by car make, revenue by region, and an order status breakdown.

Detailed Report Pages

I built three focused pages rather than one visual per question:

Sales & Revenue : performance by car make/model, branch, sales rep, and lead source

Customers & Payments : customer type, payment method/status, and rating-vs-revenue relationship

Logistics & Returns : logistics cost, return/cancellation rates, delivery status by region, and a detailed table of returned/cancelled orders

Key Insights

Order volume declined sharply from 158 orders in 2025 to 75 in 2026, despite full-year data coverage, a real trend worth investigating, not an artifact of incomplete data.
Toyota dominates vehicle revenue, led by the Prado TX at KES 195.5M.
Thika branch outperforms all others, while Nairobi HQ despite being a major hub, underperforms an unexpected pattern worth a closer look.
SUVs carry the highest logistics cost of any vehicle type.
Data entry issues (invalid discounts, missing revenue on paid orders) materially affected reported figures before correction.

Recommendations

Investigate the 2026 order decline, starting with Lead Source and Sales Rep performance.
Review Nairobi HQ's branch-level performance given its underwhelming results relative to smaller branches.
Introduce data entry validation rules for Discount and Revenue fields to prevent recurring data quality issues.

What I Learned

This project taught me that cleaning data is never just mechanical, every fix requires a judgment call, and documenting why you made a decision matters as much as the decision itself. I also learned the hard way that currency and identifier information should be preserved before transforming data, and that crashes can silently break relationships in ways that aren't obvious until something downstream fails.

Project Links

GitHub Repository: [https://github.com/Asma-Salah/jcars-logistics-power-bi.git
Full data quality audit and assumptions documentation available in the repository.

Data Modelling, Relationships & Joins

asma Salah — Sun, 13 Sep 2026 12:58:16 +0000

Data Modelling in Power BI

Coming into this, I assumed "data modelling" just meant loading a table into Power BI and building charts on top of it. What it actually means is deciding how your tables relate to each other before you ever touch a visual because that structure is what every DAX calculation, filter, and report performance number depends on downstream. A messy model doesn't just look ugly in the background, it makes calculations slower, filters behave unpredictably, and reports harder to maintain as the business grows.

There are three common approaches to structuring this:

flat tables
star schemas
snowflake schemas.

Flat table is the simplest structure, every piece of data lives in one wide table. A sales record might repeat the customer's name, region, and product category on every single row, because nothing is broken out separately.

Advantages:

No relationships to configure
Dead simple to build for tiny datasets
Easy for a beginner to understand at a glance

Disadvantages:

Massive redundancy: the same customer name gets typed thousands of times
Slower performance as row count grows
DAX measures get harder to write cleanly, since everything is tangled into one table

When it's appropriate:

Very small, one-off analyses
A dataset that will never grow or be reused
Quick exploratory checks, not a real report

Performance impact:

Power BI's engine (VertiPaq) compresses data column by column, so repeated values do compress individually
But forcing one column to carry mixed responsibilities hurts both compression and calculation clarity as the model scales

SaleFlat
Sale ID Category
CustomerName Quantity
ProductName Revenue
Region OrderDate

Star Schema

A star schema separates data into one central fact table (the measurable events: sales, orders) surrounded by dimension tables (descriptive context: customers, products, dates, locations), each connected directly to the fact table.

Advantages:

Minimal data redundancy
Fast performance: Power BI's engine is optimized specifically for this shape
Simple DAX measures
Easy to understand and extend as the model grows

Disadvantages:

Requires upfront design effort, you have to decide what's a "fact" vs a "dimension"
Slightly more setup than just dumping everything into one table

When it's appropriate:

Almost always, for any real Power BI report
It's the industry recommended default schema

Performance impact:

This is the exact shape Power BI is built around
Filters propagate cleanly across one relationship hop
DAX measures like SUM(FactSales[Revenue]) stay simple and fast

Snowflake Schema

A snowflake schema takes a star schema further by normalizing dimensions, splitting a dimension into multiple related tables. For example, DimProduct might split into DimProduct and DimCategory, so category names aren't repeated across every product row.

Advantages:

Even less data redundancy than a star schema
Useful when a dimension has a lot of repeated sub-attributes

Disadvantages:

More tables means more relationships to manage
Adds complexity to the model
Extra joins during query execution can slow performance

When it's appropriate:

Large enterprise models where a dimension is genuinely large and repetitive enough that normalizing it saves meaningful storage or performance
Otherwise it's often unnecessary complexity for a student or small-business project

Performance impact:

Generally slightly slower than a pure star schema
Power BI has to traverse an extra relationship hop to get from the fact table to the outer dimension table

Fact Tables and Dimension Tables

Once I understood the star shape, the next question was, how do I actually decide which table is the "fact" and which ones are "dimensions"?. Facts are things that happened, dimensions are things that describe what happened.

Fact Tables

A fact table stores the measurable events of the business, the numbers you actually want to add up, average, or count.

What's normally stored in a fact table:

Numeric measures (Quantity, Revenue, Profit)
Foreign keys linking to each related dimension table
A timestamp or date key for when the event occurred

Measures vs. descriptive attributes:

Measures are numbers meant to be aggregated, summed, averaged, counted (e.g. Revenue, UnitsSold)
Descriptive attributes are labels that explain the measure but aren't themselves calculated (e.g. CustomerName, ProductCategory),these belong in dimension tables, not the fact table

Grain / granularity:

Grain means what one single row in the fact table actually represents
Example: if each row is one line item on an order, the grain is "one product per order." If each row is one full order regardless of how many products it contains, the grain is "one order"
Getting the grain wrong early causes major rework later, since every measure and relationship is built assuming a specific row-level meaning

Examples of fact tables:

FactSales: one row per sale
FactOrders: one row per order
FactTransactions: one row per financial transaction

Dimension Tables

A dimension table stores the descriptive context that explains who, what, where, and when around a fact.

What's normally stored in a dimension table:

Descriptive attributes (names, categories, addresses)
A unique key that identifies each row (e.g. CustomerID)
Attributes used for filtering and grouping reports, not for calculation

Examples of dimension tables:

DimCustomer: customer name, region, contact info
DimProduct: product name, category, brand
DimDate: day, month, quarter, year
DimLocation: city, country, store branch

Practical Example: Connecting Fact to Dimensions

Using the diagram we used earlier, a central FactSales table records every sale, quantity, revenue, and foreign keys pointing to a customer, a product, a date, and a location. It doesn't store the customer's name or the product's category directly; it just stores the keys pointing to those details.

DimCustomer connects to FactSales so every sale can be traced - back to who bought it
DimProduct connects to FactSales so every sale can be traced back to what was sold

Relationships in Power BI

A relationship is what tells Power BI how two tables connect specifically, which column in one table matches which column in another. Without relationships, every table would sit isolated, and Power BI would have no way to combine a sale in FactSales with the customer who made it in DimCustomer. Relationships are what let a star schema behave like one connected model instead of a pile of unrelated spreadsheets.

One-to-Many (1:*)

How it works:

One row on the "1" side can match many rows on the "many" side
This is the default and most common relationship type in Power BI

Example:

One customer in DimCustomer can appear in many rows of FactSales (a customer can make multiple purchases).

When to use it:

Anytime a dimension table describes many events in a fact table, this is the backbone of every star schema

When not to use it:

Don't force a 1:* relationship between two tables that don't actually have that structure; it will silently produce wrong aggregations (e.g., inflated totals) if the "1" side isn't actually unique

One-to-One (1:1)

How it works:

Exactly one row in Table A matches exactly one row in Table B, with no repeats on either side

Example:

Employee and EmployeeBadge: Each employee has exactly one badge, and each badge belongs to exactly one employee

When to use it:

Rare in practice, mostly used when splitting one table into two for organizational or security reasons (e.g., separating sensitive HR data into its own table)

When not to use it:

Don't use 1:1 just because two tables happen to be the same size, confirm the actual relationship is genuinely one-to-one, not coincidentally matching row counts.

Many-to-Many (:)

How it works:

Multiple rows in Table A can match multiple rows in Table B, with no single "unique" side.

Example:

Students and Courses: One student takes many courses, and one course has many students enrolled

When to use it:

Only when the real-world relationship genuinely has no unique side. Power BI supports this, but it should be a deliberate choice, not a shortcut

When not to use it:

Avoid it as a workaround for poor table design; many-to-many relationships make filter behavior harder to predict and can silently produce incorrect totals if not modeled carefully Keys, Uniqueness, and Referential Integrity

Primary Keys:

A column that uniquely identifies each row in a table (e.g. CustomerID in DimCustomer)
Every value in this column must be unique, no duplicates allowed

Foreign Keys:

A column in another table that references a primary key elsewhere (e.g., CustomerID in FactSales)
These values are allowed to repeat, since one customer can have many sales

Why CustomerID is unique in DimCustomer but repeats in FactSales:

In DimCustomer, CustomerID is the primary key, each customer appears exactly once
In FactSales, CustomerID is a foreign key, it appears once per sale, so the same customer's ID shows up on every row representing their purchases

Referential integrity:

Every foreign key value should have a matching primary key somewhere. A CustomerID in FactSales should always correspond to a real customer in DimCustomer
When this breaks (a foreign key with no match), Power BI treats those rows as "unknown" in visuals, which can silently distort a report if left unnoticed.

Active vs. inactive relationships:

Power BI only allows one active relationship between two tables at a time; this is the one used automatically in calculations.
Additional relationships between the same two tables can exist but must be marked inactive and are only used when explicitly called with USERELATIONSHIP() in a DAX formula
A common example: a FactSales table might have both an OrderDate and a ShipDate, both relating to ``, but only one can be active by default, so the other stays inactive until a specific measure needs it.

Filter Direction

Once tables are related, the next question is: when I click on something in one table, which other tables actually respond to that click? That's what filter direction controls, it determines which way a filter is allowed to "travel" across a relationship.

Single-Direction Filtering

How it works:

A filter only flows from the "1" side of a relationship to the "many" side
This is Power BI's default behavior for relationships in a star schema

Example:

Selecting a product in DimProduct filters FactSales down to only the sales rows for that product
But selecting or filtering something in FactSales does not filter DimProduct back, the dimension table stays fully visible

Why this is the safe default:

It matches how a star schema is meant to work, dimensions describe facts, not the other way around
It keeps filter behavior predictable and easy to reason about Bidirectional Filtering

How it works:

The filter flows both ways, selecting something in the fact table can also filter the dimension table, and vice versa

Example:

Useful in a many-to-many scenario, like filtering a Students table based on a selection in a Courses table, where neither table is a clear "dimension" or "fact"

Why it should be used carefully:

Ambiguous filter paths: if a model has multiple bidirectional relationships forming loops between tables, Power BI can no longer determine a single clear path for a filter to follow, which can cause errors or force it to pick an unintended path
Unnecessary model complexity: turning on bidirectional filtering everywhere "just in case" makes the model harder to debug, since every filter interaction now has more possible directions to trace
Performance cost: filters traveling in both directions across many relationships require more computation than a clean single-direction star schema

Practical rule of thumb: only turn on bidirectional filtering when you have a specific, deliberate reason (like a genuine many-to-many relationship), never as a default setting across your whole model.

Joins in Power Query

A join is how you combine two tables based on a matching column, using Merge Queries in Power Query. Unlike a Power BI relationship, a merge actually pulls columns from one table into another during the data-loading stage, before the model is even built.

Using my Customers table (6 customers, two of whom, Grace and Mary, have never placed an order) and Orders table (7 orders, one of which references a CustomerID that doesn't exist in Customers), here's what each join type produces:

Inner Join

Definition: keeps only rows where the matching column exists in both tables
Retained: customers 1–4 and their matching orders
Result: 6 rows Grace, Mary, and the orphaned order (106) are all dropped

Left Outer Join

Definition: keeps every row from the left (first) table, plus matches from the right table where they exist
Retained: all 6 customers, with order details attached where available, blank/null where not
Result: 8 rows. Grace and Mary appear once each with null order fields

Right Outer Join

Definition: keeps every row from the right (second) table, plus matches from the left table where they exist
Retained: all 7 orders, with customer details attached where available
Result: 7 rows; Order 106 appears with null customer fields

Full Outer Join

Definition: keeps every row from both tables, matched where possible, null where not
Retained: everything, matched rows, Grace, Mary, and Order 106
Result: 9 rows total

Left Anti-Join

Definition: keeps only rows from the left table that have no match in the right table
Retained: customers with zero orders
Result: 2 rows, just Grace and Mary

Right Anti-Join

Definition: keeps only rows from the right table that have no match in the left table
Retained: orders with no valid customer
Result: 1 row, Order 106

Power Query Joins vs. Power BI Relationships

A merge and a relationship both "connect" two tables, but they do fundamentally different things, at different stages of the workflow.

Does a Power Query merge physically combine data?

Yes. A merge creates a genuinely new, combined table, the columns from both tables physically sit together in one result, as I saw when merging Customers and Orders
This happens during data preparation, before anything is loaded into the model.

Does creating a relationship combine the tables?

No. A relationship leaves both tables completely separate, DimCustomer and FactSales remain two distinct tables in the model
The relationship just tells Power BI, "These two tables are linked by this key," so it can look up matching rows on demand when a report needs it.

At what stage does each happen?

Merge in Power Query before the data is loaded into the model
Relationship to the Data Model, after the data has already been loaded

When would you choose a merge instead of a relationship?

When you genuinely need one flat, combined table for a specific export or a tool that can't handle relationships
When a calculation is much simpler to express against one combined table than across a relationship
Generally: rarely, for a proper BI model, most reporting scenarios are better served by relationships

How can excessive merging affect the model?

Every merge creates a new flattened table, which reintroduces the same redundancy problem a star schema was designed to avoid, the same customer name or product category gets repeated across every row again
A model full of merged tables starts looking like several disconnected flat tables instead of one clean star schema, making it harder to maintain and slower to query as data grows

Why keep fact and dimension tables separate instead of merging them?

Smaller table sizes: DimCustomer stores each customer once, not once per sale
Cleaner DAX: measures can reference the fact table's numbers and let relationships handle the descriptive context
Easier maintenance: updating a customer's region means changing one row in DimCustomer, not thousands of rows in a merged mega-table

Practical example, side by side:

If I merged Customers into Orders, I'd get one wide table where "Alice Wambui, Nairobi" is repeated on both her orders (101, 104), creating exactly the redundancy problem from flat table explanation.
If I instead related the two tables, "Alice Wambui, Nairobi" is stored once in Customers, and both her orders in the fact table simply reference her CustomerID, no repetition, and Power BI resolves the connection only when a report actually needs it

Recommended Power BI Model

After working through data modelling, relationships, filter direction, and joins hands-on, my recommendation for a typical business intelligence project is a star schema, with single direction one-to-many relationships as the default. Here's the reasoning, broken down by the factors that actually matter in practice:

Query and report performance:

Power BI's engine (VertiPaq) is specifically optimized around the star schema shape.
Fewer relationship hops mean filters resolve faster, especially as data volume grows

####DAX simplicity:

Measures like SUM(FactSales[Revenue]) stay simple when the fact table holds only numbers and keys
Snowflake or flat structures force DAX to work around extra joins or repeated columns, adding unnecessary complexity to formulas

Model readability:

A star schema is visually intuitive in Model View, one glance shows what's a fact and what's a dimension
A flat table hides this structure entirely; a snowflake schema adds enough branching that it takes longer to read at a glance

Scalability:

Adding a new dimension (e.g. DimSalesRep) to a star schema is a single new table and one new relationship
A flat table would require adding and repeating a new column across every existing row instead

Data redundancy:

A star schema keeps each customer, product, or date stored once, referenced by key, this is the single biggest advantage over a flat table
Snowflake reduces redundancy even further, but for most business reporting needs, the star schema's redundancy is already low enough that the extra normalization isn't worth the added complexity

Maintainability:

Updating a customer's region means editing one row in DimCustomer, not hunting through thousands of repeated rows in a flat table
Fewer tables and simpler relationships (star) are easier to hand off to another analyst than a deeply normalized snowflake model

Ease of creating reports:

Star schema's clean dimension-to-fact structure means report builders can drag fields into a visual and get correct, predictable aggregations without needing to understand complex relationship paths

Filter propagation:

Single-direction filtering (dimension to fact) is predictable and matches how most business questions are asked, "show me sales by region," not "show me regions by sales."
I would only enable bidirectional filtering in a specific, deliberate case, like a genuine many-to-many relationship, never as a blanket default.

Model complexity:

Star schema hits the sweet spot between "too simple" (flat table, all redundancy) and "too complex" (snowflake, extra hops and relationships) for the vast majority of business reporting needs.

My default relationship design: one-to-many, single-direction, active relationships from each dimension table to the central fact table. I'd only introduce a snowflake normalization if a specific dimension were large and genuinely repetitive enough to justify it, and I'd only use bidirectional filtering or many-to-many relationships when a real business question required it never as a starting default.

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

asma Salah — Mon, 31 Aug 2026 16:26:27 +0000

When people hear "Excel," they usually picture something simple; rows, columns, maybe a few totals. What I didn't expect going into this week was how much of actual data analytics happens before any analysis even starts. Most of the work is just getting the data into a state where it can be trusted. This week was about learning that groundwork: how to move around a spreadsheet efficiently, how to keep data organized as it grows, and how to clean up the small inconsistencies that quietly break formulas and calculations later.

To make this practical instead of theoretical, I worked with a sample sales dataset, 40 order records with customer names, products, quantities, prices, dates, regions, and order status. It wasn't a clean dataset on purpose. Names were inconsistently capitalized, some had extra spaces, some prices were stored as text with currency labels attached, a few cells were empty, and two rows were accidental duplicates. In other words, it looked like real data, because real data is almost never clean the first time you see it.

Excel Basics

Before cleaning anything, I had to get comfortable with how Excel actually organizes information. A spreadsheet isn't just a table, it's a grid of individually addressable cells, where every cell has a coordinate (like B7), and that coordinate is what formulas and functions actually reference. That single idea is the foundation for everything else: sorting, filtering, formulas, formatting, all of it works because Excel knows exactly where each piece of data lives.

Rows are records, columns are fields. Each row in my dataset was one order; each column was one attribute of that order (Product, Quantity, Price, etc.). Keeping that structure consistent is what makes a spreadsheet usable for analysis instead of just a place to dump numbers.
Cell references let formulas move with your data. Referencing F2 in a formula, then dragging it down to F40, automatically adjusts to F3, F4, and so on, Excel handles the relative positioning for you.
Formatting. How a number is formatted (currency, date, plain number) affects how Excel treats it, not just how it looks.

Freezing Panes

With 40 rows of data, it doesn't take long before your headers scroll out of view, and once that happens, it's easy to misread which column is which. Freezing panes solves this by locking the header row and the first column in place while the rest of the sheet scrolls underneath it.

I did this through View → Freeze Panes → Freeze Top Row, and it immediately made the sheet easier to navigate — I could scroll through all 40 orders and still see exactly which column I was looking at.

Sorting and Filtering

Once the headers were locked in place, the next step was organizing them in a way that reveals patterns..

Sorting rearranges the entire dataset based on one or more columns. I sorted my sales data by Region first, using Data → Sort, which grouped all orders from the same region together instead of leaving them scattered in the order they were entered.

Filtering, on the other hand, doesn't rearrange anything,it temporarily hides rows that don't match a condition you set, so you can focus on a subset without losing the rest of the data. I applied a filter using Ctrl + Shift + L or HOME -> filter & sort, which added dropdown arrows to each header. From there, I filtered the Status column to show only "Cancelled" orders, which instantly narrowed 40 rows down to a handful, useful if I wanted to investigate cancellations specifically without deleting or reorganizing anything else.

Cleaning Text

Scrolling through the Customer Name column also exposed a different problem that the values weren't consistent. Some names were fully uppercase (ALICE WAMBUI), some had random casing (grace Njeri), and a few had extra spaces hiding inside them.

Two functions fixed this:

TRIM() removes extra spaces, leading, trailing, and repeated spaces between words. Leaving only single spaces between words. =TRIM(B2) cleaned up entries like " Samuel Mwangi".
PROPER() capitalizes the first letter of each word and lowercases the rest, standardizing casing. =PROPER(B2) turned "ALICE wambui" into "Alice Wambui".

I combined both in a single formula

=PROPER(TRIM(B2))

After applying this down the column, I copied the results and used Paste Special → Values to replace the original messy column with the cleaned, static text, otherwise the formulas would keep referencing the original (still-messy) column.

Removing Duplicate Rows

Two orders, 1006 and 1013, appeared twice each, with completely identical values across every column. Duplicate rows like this are especially misleading in a sales dataset because they don't just clutter the sheet, they distort any totals or counts calculated from it. A SUM() of the Total column, or a count of orders per region, would both come out inflated if duplicates aren't caught first.

Excel has a built-in tool for this rather than requiring a manual scan: Data → Remove Duplicates. Selecting the full dataset and running this tool opens a dialog where you choose which columns to check for duplication.

Basic Calculations, Formulas, and AutoSum

The Total column had been left empty on purpose, meant to be calculated as Quantity × Unit Price for each order. Rather than typing that out manually 40 times, I used a formula referencing the two relevant cells:

=E2*F2

I then dragged this formula down the entire column, and Excel automatically adjusted the cell references for each row (E3*F3, E4*F4, and so on), calculating every order's total in seconds.

For a grand total across all orders, I used AutoSum, one of Excel's most basic but genuinely time-saving tools. Selecting the cell directly below the last Total value and pressing Alt + = automatically inserted a SUM() formula covering the entire column, without me needing to type the range manually:

=SUM(G2:G40)

Basic arithmetic operators worked exactly as expected once the data was clean: + for addition, - for subtraction, * for multiplication, / for division.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push

asma Salah — Thu, 27 Aug 2026 09:38:57 +0000

Working directory

This is the local project folder; you can create it in the Git terminal by using the following:

mkdir folderName command, for example, mkdir luxDev

switching to that folder, we use

cd folderName command.
To create a file in the folder, we use type nul > fileName

Then in the event of making changes, we use Git to track the changes.

git status command. This command will help us find the untracked changes in files.

Staging

Staging lets you choose what exactly goes into your next commit.

git add . is used to stage all changes while,
git add fileName if you want to save a single file or 2 to 3 files

Commit

This is where git records your staged changes to be permanent point in project history.

git commit -m "add folder setup and structured the first comment"
A commit doesn't affect your working directory or your staging area; rather, it takes a snapshot of what's currently staged and locks it into history

Git also gives you tools to actually see what's happening at each stage, which helped me trust the process instead of just following steps blindly.

git log shows the history of commits

This prints each commit's unique ID, author, date, and message

git diff shows exactly what changed in a file, line by line, before you commit it. This is what finally made the working directory.

git diff— shows changes in your working directory that are not yet staged
git diff --staged — shows changes that are staged, waiting to be committed

Push

Everything above happens locally, on your own machine. Push is the step where your commit history gets sent to a remote repository (like GitHub), so it's backed up and shareable.

git push origin main

This is the step that made Git "real" for me because before pushing, all my work exists only on my laptop. After pushing, it's on GitHub, visible to anyone I share the repo with, and safe even if my laptop dies tomorrow.

Each stage answer a different question

Working directory: What am I currently changing?
Staging: What do I want to include in my next save point?
Commit: What's now permanently recorded in history?
Push: What's now shared with others?