DEV Community: Naomi Kwamboka Onderi

Joins In SQL

Naomi Kwamboka Onderi — Sat, 17 Oct 2026 23:38:41 +0000

Introduction-SQL window functions

A window function calculates a value for each row using a set of related rows, called the window, and it keeps every input row. GROUP BY is the other aggregate tool: it collapses each group into one row. Use a window when the hospital still needs the visit, and also needs a rank, a running total, or a comparison with the previous visit.

OVER(), PARTITION BY, ORDER BY, and window frames

-OVER() is what makes an aggregate or ranking function a window function. Everything inside OVER() defines which rows are visible to the current row.

PARTITION BYsplits the result into independent groups.PARTITION BY department_idrestarts the calculation for each department. With noPARTITION BY`, the window is the whole result.
ORDER BY inside OVER() puts rows in a sequence inside each partition. Ranking functions need it. Aggregates use it to define a running calculation.
A frame is the slice of the partition that the current row can see. It is written as ROWS or RANGE plus a start and an end, for example UNBOUNDED PRECEDING, CURRENT ROW, and UNBOUNDED FOLLOWING.

Two defaults matter in practice:

An aggregate with an ORDER BY and no frame uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is a running total from the first row through the current row.
An aggregate with no ORDER BY uses the whole partition. Each row in the department then sees the same department total.
ROWS counts physical rows. RANGE also pulls in peer rows that share the same ORDER BY value. Two Cardiology visits both billed at KES 30,000 are peers if the window is ordered only by total_bill_kes.

sql SELECT visit_id, department_id, visit_date, total_bill_kes, SUM(total_bill_kes) OVER ( PARTITION BY department_id ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_department_bill FROM visits_fact;

This lists every visit and adds a running bill within its department, in date order. Cardiology comes out as 45,000, then 57,000, then 87,000, then 117,000.

Ranking functions

These four answer “where does this visit sit relative to the others?” They need ORDER BY inside OVER().

ROW_NUMBER() assigns a unique sequence 1, 2, 3… inside the partition. Ties still get different numbers. The extra sort key decides which tied row comes first. Use it to keep one row per patient, such as the latest visit.

RANK() gives tied rows the same rank and then skips. Two visits tied for 3rd both get 3, and the next visit gets 5. Use it for published league tables, where a shared place should open a gap.

DENSE_RANK() gives tied rows the same rank and does not skip. The next visit gets 4. Use it when the next band must stay consecutive, such as fee bands.

NTILE(n) splits the ordered partition into n buckets that are as equal as possible. Extra rows go into the earlier buckets. It splits rows, so two visits with the same bill can fall into different buckets. Use it to flag the top quarter of bills for audit.

sql SELECT visit_id, total_bill_kes, ROW_NUMBER() OVER (ORDER BY total_bill_kes DESC, visit_id) AS row_num, RANK() OVER (ORDER BY total_bill_kes DESC) AS rank_bill, DENSE_RANK() OVER (ORDER BY total_bill_kes DESC) AS dense_rank_bill, NTILE(4) OVER (ORDER BY total_bill_kes DESC, visit_id) AS bill_quartile FROM visits_fact;
ROW_NUMBER() uses visit_id as a tie-break so the two KES 30,000 visits (106 and 109) have a stable order. RANK() and DENSE_RANK() are ordered only by bill, so those two visits share a rank.
| visit_id | bill | row_num | rank | dense_rank | quartile |
|---|---:|---:|---:|---:|---:|
| 105 | 60,000 | 1 | 1 | 1 | 1 |
| 101 | 45,000 | 2 | 2 | 2 | 1 |
| 106 | 30,000 | 3 | 3 | 3 | 1 |
| 109 | 30,000 | 4 | 3 | 3 | 2 |
| 107 | 18,000 | 5 | 5 | 4 | 2 |
| 104 | 15,000 | 6 | 6 | 5 | 3 |
| 102 | 12,000 | 7 | 7 | 6 | 3 |
| 108 | 9,000 | 8 | 8 | 7 | 4 |
| 103 | 8,000 | 9 | 9 | 8 | 4 |

Visit 109 shows the difference: RANK() jumps from 3 to 5, DENSE_RANK() continues at 4, and NTILE(4) places 106 in quartile 1 and 109 in quartile 2 even though the bill is the same. Nine rows into four tiles gives sizes 3, 2, 2, 2.

Within a department, the same pattern marks the single most expensive visit:

sql SELECT visit_id, department_id, total_bill_kes FROM ( SELECT visit_id, department_id, total_bill_kes, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY total_bill_kes DESC, visit_id ) AS rn FROM visits_fact ) ranked WHERE rn = 1;

Expected rows: visit 101 for Cardiology (45,000), 104 for General Medicine (15,000), 105 for Orthopedics (60,000), and 107 for Pediatrics (18,000).

LAG() and LEAD()
LAG(column, offset, default) reads a value from an earlier row in the ordered window. LEAD() reads a later row. The offset defaults to 1. The default value is returned when that row does not exist; otherwise the result is NULL.
Finance uses this to see whether a return visit cost more than the previous one for the same patient.

sql SELECT patient_id, visit_date, total_bill_kes, LAG(total_bill_kes) OVER ( PARTITION BY patient_id ORDER BY visit_date ) AS previous_bill, total_bill_kes - LAG(total_bill_kes) OVER ( PARTITION BY patient_id ORDER BY visit_date ) AS change_from_previous, LEAD(visit_date) OVER ( PARTITION BY patient_id ORDER BY visit_date ) AS next_visit_date FROM visits_fact;

Patient 1:

visit_date	bill	previous_bill	change	next_visit_date
2026-01-05	45,000	NULL	NULL	2026-01-12
2026-01-12	12,000	45,000	−33,000	2026-02-02
2026-02-02	30,000	12,000	+18,000	NULL

The first visit has no previous bill. The last visit has no next date. Patient 2’s second visit rises by 7,000 (8,000 to 15,000). Patient 4’s second visit falls by 9,000 (18,000 to 9,000).

`FIRST_VALUE()` and `LAST_VALUE()`

FIRST_VALUE() returns the first value in the ordered frame. LAST_VALUE() returns the last value in the frame. Both depend on the frame. With the default frame, LAST_VALUE() stops at the current row, so it repeats the current visit and does not mean “the patient’s latest visit.” Extend the frame to the end of the partition when the latest value is what you want.
sql SELECT patient_id, visit_id, visit_date, total_bill_kes, FIRST_VALUE(total_bill_kes) OVER ( PARTITION BY patient_id ORDER BY visit_date ) AS first_visit_bill, 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_fact;

Every row for a patient shows that patient’s first and last bill. Patient 1 shows 45,000 and 30,000 on all three rows. Patient 2 shows 8,000 and 15,000. Patient 3 shows 60,000 and 30,000. Patient 4 shows 18,000 and 9,000. Drop the frame on LAST_VALUE() and visit 101 would show 45,000 as its “last” bill, because the frame would end on that row.

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

These are the same aggregates used with GROUP BY. Written with OVER(), they attach the aggregate back onto each visit.

sql SELECT visit_id, department_id, total_bill_kes, SUM(total_bill_kes) OVER (PARTITION BY department_id) AS department_bill, AVG(total_bill_kes) OVER (PARTITION BY department_id) AS department_avg, COUNT(*) OVER (PARTITION BY department_id) AS department_visits, MIN(total_bill_kes) OVER (PARTITION BY department_id) AS department_min, MAX(total_bill_kes) OVER (PARTITION BY department_id) AS department_max, total_bill_kes - AVG(total_bill_kes) OVER (PARTITION BY department_id) AS variance_from_department_avg FROM visits_fact;

There is no ORDER BY, so each function sees the whole department and repeats the same aggregate on every visit in that department.

department	visits	sum	avg	min	max
Cardiology	4	117,000	29,250	12,000	45,000
General Medicine	2	23,000	11,500	8,000	15,000
Orthopedics	1	60,000	60,000	60,000	60,000
Pediatrics	2	27,000	13,500	9,000	18,000

Visit 101 (45,000) is 15,750 above the Cardiology average. Visit 102 (12,000) is 17,250 below it. A GROUP BY department_id query would return the four summary rows and drop the visit identity. The window keeps both.

Add ORDER BY visit_date and a ROWS frame when the figure must accumulate. A running average of length of stay in Cardiology, ordered by date, is 3.0 after visit 101, then 2.0 after 102 ((3+1)/2), then 2.0 after 106 ((3+1+2)/3), then 2.0 after 109 ((3+1+2+2)/4).

SQL joins

A join combines rows from two tables by matching them. In this model the match is a key: visits_fact.department_id = dim_departments.department_id, and the same pattern for patients and doctors. The join type decides what happens to rows that have no match.

`INNER JOIN`

INNER JOIN returns only rows that match on both sides. A visit whose department id exists, and a department that has at least one visit, both appear. Maternity and patient 5 disappear.
sql SELECT v.visit_id, d.department_name, v.total_bill_kes FROM visits_fact AS v INNER JOIN dim_departments AS d ON v.department_id = d.department_id;

Nine rows, one per visit, with Cardiology, General Medicine, Orthopedics, or Pediatrics. Use this when an unmatched row would be an incomplete fact, such as a bill that must belong to a known department.

`LEFT JOIN`

LEFT JOIN keeps every row from the left table. Columns from the right table are NULL when nothing matches. Put the table you must not lose on the left.
sql SELECT d.department_name, v.visit_id, v.total_bill_kes FROM dim_departments AS d LEFT JOIN visits_fact AS v ON d.department_id = v.department_id;

The nine visits appear, plus one extra row: Maternity with a NULL visit and a NULL bill. Use this for “show every department, including those with no activity this period.” Putting visits_fact on the left instead would keep every visit and drop Maternity, which is a different question.

`RIGHT JOIN`

RIGHT JOIN keeps every row from the right table. It is the same match rule as LEFT JOIN with the tables swapped. This query returns the same rows as the LEFT JOIN above:
sql SELECT d.department_name, v.visit_id, v.total_bill_kes FROM visits_fact AS v RIGHT JOIN dim_departments AS d ON v.department_id = d.department_id;
LEFT JOIN is the usual way to write this, because reading from the preserved table on the left is easier to follow. RIGHT JOIN is the right tool when a query is already built from the fact table and you need to preserve the dimension without rewriting the FROM clause.

`FULL OUTER JOIN`

FULL OUTER JOIN keeps unmatched rows from both tables. A patient with no visit and a visit with no patient would both appear. In this extract every visit has a patient, so the only extra row is patient 5.
sql SELECT p.patient_id, p.county, v.visit_id, v.total_bill_kes FROM dim_patients AS p FULL OUTER JOIN visits_fact AS v ON p.patient_id = v.patient_id;

Nine visit rows, plus patient 5 (Kiambu) with NULL visit columns. Use it for a reconciliation: registered patients who never attended, and visit records that do not match a patient.

MySQL writes the same result as a union of both outer joins:

sql SELECT p.patient_id, p.county, v.visit_id, v.total_bill_kes FROM dim_patients AS p LEFT JOIN visits_fact AS v ON p.patient_id = v.patient_id UNION ALL SELECT p.patient_id, p.county, v.visit_id, v.total_bill_kes FROM dim_patients AS p RIGHT JOIN visits_fact AS v ON p.patient_id = v.patient_id WHERE p.patient_id IS NULL;

The second leg is empty on this extract, because no visit lacks a patient.

## CROSS JOIN
CROSS JOIN matches every row on the left with every row on the right. There is no ON. Five departments and six doctors produce 30 rows. Use it only when the product is the real question, such as a blank roster of every doctor against every department before you mark who is actually assigned.
sql SELECT doc.doctor_name, dep.department_name FROM dim_doctors AS doc CROSS JOIN dim_departments AS dep;

Crossing visits_fact with dim_departments would invent department combinations the patient never attended. That is the failure mode to avoid: a missing ON clause in an old comma-style join behaves as a cross join.

`SELF JOIN`

A self join uses the same table twice, under two aliases, and relates rows to other rows in that table. Follow-up visits are a visit matched to an earlier visit for the same patient.

sql SELECT earlier.patient_id, earlier.visit_id AS first_visit, earlier.visit_date AS first_date, later.visit_id AS follow_up_visit, later.visit_date AS follow_up_date FROM visits_fact AS earlier INNER JOIN visits_fact AS later ON earlier.patient_id = later.patient_id AND earlier.visit_date < later.visit_date;

patient	first visit	follow-up
1	101 on 5 Jan	102 on 12 Jan
1	101 on 5 Jan	109 on 2 Feb
1	102 on 12 Jan	109 on 2 Feb
2	103 on 6 Jan	104 on 20 Jan
3	105 on 8 Jan	106 on 15 Jan
4	107 on 9 Jan	108 on 18 Jan

Patient 1 has three pairs because three visits can be ordered in three earlier/later combinations. The date condition stops a visit from matching itself and stops each pair from appearing twice.

Which join answers which hospital question

Join	Rows that survive	Hospital use
`INNER JOIN`	Matches only	Bills that must have a known department and doctor
`LEFT JOIN`	All of the left table, plus matches	Every department, including those with zero visits
`RIGHT JOIN`	All of the right table, plus matches	Same as `LEFT JOIN`, with the preserved table written on the right
`FULL OUTER JOIN`	Matches plus unmatched rows from both sides	Patients with no visit and visits with no patient, together
`CROSS JOIN`	Every combination	A blank coverage grid, not a list of real visits
`SELF JOIN`	Rows related to other rows in the same table	A visit compared with the same patient’s earlier visit

## Common table expressions

A CTE is a named result defined with WITH and used by the main query that follows. It lasts for that one statement. It does not create a table. The reason to use one is to name an intermediate result, reuse it, and keep a multi-step hospital question readable.

A basic CTE

This names each department’s revenue, then keeps departments above the average department revenue.

sql WITH department_revenue AS ( SELECT d.department_id, d.department_name, SUM(v.total_bill_kes) AS revenue FROM dim_departments AS d INNER JOIN visits_fact AS v ON v.department_id = d.department_id GROUP BY d.department_id, d.department_name ) SELECT * FROM department_revenue WHERE revenue > (SELECT AVG(revenue) FROM department_revenue);

The four department revenues are 117,000, 23,000, 60,000, and 27,000. Their average is 56,750. Cardiology (117,000) and Orthopedics (60,000) are returned. The same CTE is referenced twice: once as the source, once inside the average. A subquery would have to repeat the aggregation text.

Multiple CTEs

Separate steps by commas. Later CTEs may use earlier ones.

sql WITH visit_detail AS ( SELECT v.visit_id, v.total_bill_kes, p.county, p.patient_gender, d.department_name FROM visits_fact AS v INNER JOIN dim_patients AS p ON p.patient_id = v.patient_id INNER JOIN dim_departments AS d ON d.department_id = v.department_id ), county_summary AS ( SELECT county, COUNT(*) AS visits, SUM(total_bill_kes) AS revenue FROM visit_detail GROUP BY county ) SELECT c.county, c.visits, c.revenue, vd.department_name, vd.total_bill_kes FROM county_summary AS c INNER JOIN visit_detail AS vd ON vd.county = c.county ORDER BY c.revenue DESC, vd.visit_id;
visit_detail joins visits to patients and departments. county_summary aggregates that result. The main query joins the summary back to the detail so each visit sits beside its county total.
| county | visits | revenue | visit bills |
|---|---:|---:|---|
| Nairobi | 3 | 87,000 | 45,000, 12,000, 30,000 |
| Mombasa | 2 | 90,000 | 60,000, 30,000 |
| Kisumu | 2 | 23,000 | 8,000, 15,000 |
| Nakuru | 2 | 27,000 | 18,000, 9,000 |

Mombasa sorts first on revenue. Kiambu is absent because patient 5 never reaches visit_detail.

A recursive CTE

A recursive CTE has an anchor member, UNION ALL, and a recursive member that reads the CTE itself. The anchor starts the set. Each pass adds the next level until no new rows appear. The Cardiology roster is a tree: Achieng, then Otieno, then Mwangi.

`sql
WITH RECURSIVE reporting_line AS (
SELECT
doctor_id,
doctor_name,
reports_to_doctor_id,
0 AS level
FROM dim_doctors
WHERE doctor_name = 'Dr Achieng'

UNION ALL

SELECT
    doc.doctor_id,
    doc.doctor_name,
    doc.reports_to_doctor_id,
    line.level + 1
FROM dim_doctors AS doc
INNER JOIN reporting_line AS line
    ON doc.reports_to_doctor_id = line.doctor_id

)
SELECT *
FROM reporting_line
ORDER BY level;
`

level	doctor	reports to
0	Dr Achieng	nobody (anchor)
1	Dr Otieno	Dr Achieng
2	Dr Mwangi	Dr Otieno

SQL Server writes WITH reporting_line without the word RECURSIVE. The query needs a condition that eventually returns no rows. A cycle in reports_to_doctor_id would run until the engine’s recursion limit.

How CTEs differ from subqueries

Both can sit in FROM and both can be joined and aggregated. The CTE name is the practical difference.

A CTE is declared before the main query and can be joined more than once under one name. department_revenue was used as the list and as the input to AVG.
A subquery is written where it is used. The logic is local, and repeating it means copying the SQL.
A recursive walk of the doctor tree can be expressed as a CTE. A subquery in FROM cannot call itself.
The optimizer may inline either form. Readability and reuse are the reasons to choose; one is not automatically faster.

Use a subquery for a short, single-use test. Use a CTE when the statement has several named steps, or when the step is recursive.

## SQL subqueries

A subquery is a query inside another query. Its place in the outer query, and whether it refers to the outer query, decide its type.

Where a subquery can sit

In SELECT. A scalar subquery returns one column and one row, and it is printed beside each outer row. This attaches the patient’s visit count to every visit:

sql SELECT v.visit_id, v.patient_id, v.total_bill_kes, ( SELECT COUNT(*) FROM visits_fact AS v2 WHERE v2.patient_id = v.patient_id ) AS visits_for_this_patient FROM visits_fact AS v;

Visits 101, 102, and 109 show 3. Visits 103 and 104 show 2. Visits 105 and 106 show 2. Visits 107 and 108 show 2. More than one row, or more than one column, in this position is an error.

In FROM. The inner query is a derived table and must have an alias. This builds department totals, then keeps those at or above KES 50,000:

sql SELECT dept.department_name, dept.revenue FROM ( SELECT d.department_name, SUM(v.total_bill_kes) AS revenue FROM dim_departments AS d INNER JOIN visits_fact AS v ON v.department_id = d.department_id GROUP BY d.department_name ) AS dept WHERE dept.revenue >= 50000;

Cardiology (117,000) and Orthopedics (60,000). General Medicine and Pediatrics fall below the cut. Maternity never enters the inner join.

In WHERE. The subquery filters the outer row. The IN, EXISTS, ANY, and ALL examples below all sit in WHERE. A scalar comparison sits there too: total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits_fact). The overall average is 227,000 / 9 = 25,222.22. Visits above it are 101 (45,000), 105 (60,000), 106 (30,000), and 109 (30,000).

##Scalar subqueries

A scalar subquery returns a single value. The average above is scalar and non-correlated: it does not mention the outer query, so the engine computes it once. A scalar subquery in SELECT that filters on the outer row, such as the visit count, is correlated and runs per outer row.

##Correlated and non-correlated subqueries

A correlated subquery refers to a column from the outer query. It is re-evaluated for each outer row. This keeps visits that cost more than that same patient’s own average:

sql SELECT v.patient_id, v.visit_id, v.total_bill_kes FROM visits_fact AS v WHERE v.total_bill_kes > ( SELECT AVG(v2.total_bill_kes) FROM visits_fact AS v2 WHERE v2.patient_id = v.patient_id );

patient	average	visits above that average
1	29,000	101 (45,000), 109 (30,000)
2	11,500	104 (15,000)
3	45,000	105 (60,000)
4	13,500	107 (18,000)

v2.patient_id = v.patient_id is the correlation. Visit 102 is under patient 1’s average, so it is excluded even though 12,000 would be above patient 2’s average.
A non-correlated subquery can run on its own. This list of active department ids does not look at the outer row:

sql SELECT department_name FROM dim_departments WHERE department_id IN ( SELECT DISTINCT department_id FROM visits_fact );

Cardiology, General Medicine, Orthopedics, and Pediatrics. Maternity is excluded because its id is not in visits_fact.

Nested subqueries

A nested subquery is a subquery inside another subquery. Here the innermost query finds departments whose average bill is above the hospital average (25,222.22). Those departments are Cardiology (29,250) and Orthopedics (60,000). The middle query turns those ids into doctor ids. The outer query prints the doctors.

sql SELECT doctor_name FROM dim_doctors WHERE department_id IN ( SELECT department_id FROM visits_fact GROUP BY department_id HAVING AVG(total_bill_kes) > ( SELECT AVG(total_bill_kes) FROM visits_fact ) );

Dr Achieng, Dr Otieno, Dr Mwangi, and Dr Kamau. Dr Wanjiku and Dr Njeri work in departments below the hospital average.

IN, EXISTS, ANY, and ALL

IN keeps the outer row when its value equals any value in the subquery list. Patients who had a Cardiology visit:

sql SELECT patient_id, county FROM dim_patients WHERE patient_id IN ( SELECT patient_id FROM visits_fact WHERE department_id = 1 );

Patients 1 (Nairobi) and 3 (Mombasa). NOT IN means “none of these values.” It becomes unsafe if the subquery can return NULL, because a comparison with NULL is unknown. Prefer NOT EXISTS for the negative test.

EXISTS keeps the outer row when the subquery returns at least one row. It does not care what the columns contain. Departments that raised at least one bill over KES 40,000:

sql SELECT d.department_name FROM dim_departments AS d WHERE EXISTS ( SELECT 1 FROM visits_fact AS v WHERE v.department_id = d.department_id AND v.total_bill_kes > 40000 );

Cardiology (visit 101, 45,000) and Orthopedics (visit 105, 60,000). This subquery is correlated through d.department_id. EXISTS is the usual choice for a yes-or-no check. IN is the usual choice when you already have a list of keys.

ANY compares the outer value with each subquery value and succeeds if the comparison is true for at least one of them. > ANY (...) means “greater than the smallest value in the list.” Pediatrics bills are 18,000 and 9,000, so > ANY of those bills means greater than 9,000.

sql SELECT visit_id, total_bill_kes FROM visits_fact WHERE total_bill_kes > ANY ( SELECT total_bill_kes FROM visits_fact WHERE department_id = 4 );

Every visit except 103 (8,000) and 108 (9,000). Visit 108 is not greater than either Pediatrics bill.

ALL succeeds only when the comparison is true for every subquery value. > ALL of the Pediatrics bills means greater than 18,000, the larger one.

sql SELECT visit_id, total_bill_kes FROM visits_fact WHERE total_bill_kes > ALL ( SELECT total_bill_kes FROM visits_fact WHERE department_id = 4 );

Visits 101 (45,000), 105 (60,000), 106 (30,000), and 109 (30,000). Visit 107 equals 18,000, so it fails a strict > test.
IN is the same test as = ANY. NOT IN matches <> ALL only when the list contains no NULL.

##How the four tools fit one hospital question

A monthly review can use all four in one statement: a CTE names the joined visit, a window ranks bills inside each department, and a subquery keeps departments that actually billed.

sql WITH billed_visits AS ( SELECT v.visit_id, v.visit_date, v.total_bill_kes, d.department_name, doc.doctor_name FROM visits_fact AS v INNER JOIN dim_departments AS d ON d.department_id = v.department_id INNER JOIN dim_doctors AS doc ON doc.doctor_id = v.doctor_id ) SELECT department_name, doctor_name, visit_id, total_bill_kes, RANK() OVER ( PARTITION BY department_name ORDER BY total_bill_kes DESC ) AS bill_rank_in_department FROM billed_visits WHERE department_name IN ( SELECT d.department_name FROM dim_departments AS d WHERE EXISTS ( SELECT 1 FROM visits_fact AS v WHERE v.department_id = d.department_id ) );

The inner joins attach a department and a doctor to each real visit. EXISTS limits the list to departments with activity, so Maternity stays out. RANK() marks the most expensive visit in each department as 1. Cardiology’s rank 1 is visit 101. The two KES 30,000 Cardiology visits share rank 2, and visit 102 is rank 4.

Power BI Data Modelling, Relationships & Joins

Naomi Kwamboka Onderi — Sun, 13 Sep 2026 15:00:32 +0000

Introduction

This week we got started on learning Power BI its uses and how its applied in data analysis. When working with Power BI, the way data is organized and connected has a major impact on how easily reports can be created, how calculations work, and how well a Power BI solution performs.
Data modelling in Power BI involves organizing data into tables and defining relationships between those tables so that the data can be analyzed effectively. A well-designed data model makes reports easier to understand, DAX calculations easier to write, and Power BI solutions easier to maintain and scale.

Data Modelling in Power BI

Data modelling is the process of organizing data into structured tables and establishing relationships between those tables so that Power BI can understand how the data is connected.
Instead of keeping all this information in one huge table, the data can be separated into different tables and connected using common fields.
A well-designed data model is important because it can improve:

Model readability
Maintainability
Filter propagation
Overall reporting experience
DAX calculations
Data organization
Scalability
Report performance

There are several ways data can be structured. The three common modelling approaches are flat tables, star schemas, and snowflake schemas.

Flat Tables

A flat table stores all the required information in a single table. For simple datasets, a flat table may be sufficient, but it is generally less suitable for large and complex business intelligence solutions.

Advantages of a Flat Table

A flat table is relatively easy to understand because there are no relationships between multiple tables.
It can be appropriate when there are few repeated values, a quick analysis is required, the analysis is simple and dataset is small.

Disadvantages of a Flat Table

The major problem is data redundancy which can result in repeated data, larger datasets, more difficult maintenance, potential inconsistencies and less flexible modelling.

Star Schema

A star schema separates the data into a main fact table and several surrounding tables. The structure resembles a star, which is where the name comes from. The main fact table contains business events or transactions, while the dimension tables contain descriptive information about those events.
It is particularly useful for business intelligence and reporting solutions where users need to analyze business events across different dimensions.
For example, a company could use a star schema to analyze:

Sales by product
Sales by product category
Sales by customer
Sales by location
Sales by month

Advantages of a Star Schema:

It is generally well suited to Power BI's analytical model.
It is relatively simple to understand.
It reduces unnecessary data duplication.
It supports efficient reporting and analysis.
It makes DAX calculations easier to manage.
It provides a clear structure for filter propagation.
It is easier to maintain and extend. ## Disadvantages of the Star Schema Incorrect relationships or poorly designed keys can result in incorrect calculations or unexpected filtering it also requires a proper understanding of relationships between tables for everything to be correct.

Snowflake Schema

A snowflake schema is similar to a star schema, but dimension tables are further divided into additional related tables.
Instead of storing all product-related information in one dimension table, information can be separated into multiple related tables.
It may be appropriate when the underlying data contains complex hierarchical structures or when dimensions are large and naturally divided into related entities. However, unless it's necessary it's advised to keep the dimensions simpler to make the Power BI model easier to work with.

Advantages of a Snowflake Schema

Represent complex hierarchies.
Organize highly structured source data.
Useful when dimensions contain multiple levels.
Reduce repeated information within dimensions. ##Disadvantages of a Snowflake Schema It can be more difficult to navigate than a straightforward star schema because of the extra tables, extra relationships and more filter paths. This makes it more complex. It has a steeper learning curve. The report development can be complicated.

Comparing the Three Approaches

The key difference is how the data is organized. A flat table keeps everything together, a star schema separates facts from dimensions, while a snowflake schema takes normalization further by splitting dimensions into additional related tables.
The star schema provides a practical balance between performance, simplicity, and maintainability.
Power BI models consists of tables, keys, relationships, and rules that determine how information flows between those tables.

Fact Tables and Dimension Tables

A fact table stores measurable business events or transactions. It answers questions such as how many?, how much?and how many transactions.
Dimension tables contain descriptive information used to analyze the facts. It answers questions such as who?, when? and where?

Grain or Granularity

The grain describes exactly what one row in the fact table represents. Clearly defining the grain is important because it prevents confusion and incorrect calculations. For example For example, a sales fact table should not have one row representing a whole order in some cases and one row representing an individual product in other cases.

Relationships in Power BI

A relationship connects two tables using a common column. Relationships are necessary because business data is often distributed across multiple tables. For example it shows how a customer relates to a product or different products.

Relationship Cardinality

Cardinality describes how many records in one table can be related to records in another table. Power BI supports several relationship types such as:

One-to-Many (1:*)- This is normally the preferred relationship between a dimension and a fact table. For example, A customer can make many sales, but each sales record belongs to one customer.
One-to-One (1:1)- A one-to-one relationship means that each record in one table corresponds to only one record in the other table. For example, Every employee has their own unique employee details such as their email or ID.
Many-to-Many (:)- A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another table for example, students can enroll in many courses, and each course can have many students. Many-to-many relationships can be useful, but they should be handled carefully because they can create ambiguous filtering and unexpected results.

Primary Keys and Foreign Keys

A primary key uniquely identifies a record in a table, for example employee ID, Student ID.
A foreign key is a column used to reference a record in another table for example, a customer ID column can be found in different tables since it's possible for customers to make multiple purchases. The relationship works because the dimension contains unique values while the fact table can contain repeated values.

Referential Integrity

Referential integrity means that foreign-key values should correspond to valid records in the related dimension. If the column does not exist in the dimension, the model may produce unexpected results when filtering or calculating data.
Ensuring that keys are clean and valid is therefore an important part of data preparation.

Active and Inactive Relationships

An active relationship is the default relationship used when filters travel between tables. Only one relationship between the same two tables can normally be active at a time. An inactive relationship can be used in specific DAX calculations when required.

Filter Direction

Relationships can determine how filters move between tables.

Single-Direction Filtering- In a typical star schema, filtering usually flows from the dimension to the fact table. This is generally the preferred approach for a star schema because the filter path is clear and predictable.
Bidirectional Filtering- This is when filters flow in both directions. It is recommended to use the single-direction filtering unless there is a specific reason to use bidirectional filtering.

Joins in Power Query

Power Query provides a feature called Merge Queries, which allows tables to be joined during the data transformation stage therefore relationships in the Power BI model are not the same thing as joins in Power Query:

** Left Outer Join**- This keeps all records from the left table and matching records from the right table.
** Right Outer Join**- This keeps all records from the right table and matching records from the left table.
** Full Outer Join**- This keeps all records from both tables. ALL LEFT records + ALL RIGHT records
** Inner Join**- This keeps only records that have a match in both tables. Inner joins are useful when we only want records that exist in both datasets.
** Left Anti Join**- This returns records that exist in the left table but do not have a match in the right table. This can be useful for data quality checks and finding missing relationships.
** Right Anti Join**- This returns records that exist in the right table but do not have a match in the left table.

Power Query Joins vs Power BI Relationships

A Power Query merge combines information from two tables during the data preparation/transformation stage. A Power Query merge can physically bring columns from one table into another table.
A relationship in Power BI is created later in the data model. Instead of physically combining the tables, we keep them separate. This means creating a relationship in Power BI does not physically merge the tables together.
The relationship tells Power BI how filters and calculations should move between the tables.

When Should You Use a Merge Instead of a Relationship?

A merge can be appropriate when information genuinely belongs together or when data transformation requires combining columns. However, excessive merging can create very large and complicated flat tables. For analytical reporting, keeping fact and dimension tables separate is often preferable.

Why Keep Fact and Dimension Tables Separate?

Reduced Redundancy
Easier DAX-Measures can be written against a clear fact table while dimensions provide filtering context.
Better Model Readability- A developer can easily understand which tables contain facts and which contain dimensions.
Easier Reporting
Better Scalability

Conclusion

Data modelling is one of the foundations of building effective Power BI solutions. Creating attractive visualizations is only one part of the reporting process. If the underlying model is poorly designed, calculations can become difficult, filters can behave unexpectedly, and maintaining the report can become increasingly complicated. For a typical business intelligence project, I would recommend a star schema. A well-designed star schema provides a relatively simple model with clear relationships between fact and dimension tables. This can support efficient filtering and analytical queries. And use one-to-many relationships between dimensions and the fact table and single-direction filtering, where filters flow from the dimension to the fact table. I would avoid unnecessary bidirectional relationships because they can introduce ambiguous filter paths and make the model harder to understand. Many-to-many relationships would also only be used when the business requirement genuinely requires them. Where possible, a bridge table can be used to create a clearer model.

Jumia Product Performance Analysis & Pricing Dashboard

Naomi Kwamboka Onderi — Mon, 07 Sep 2026 18:04:57 +0000

Introduction

As part of understanding how Microsoft Excel is used in E-commerce. I analyzed a dataset of products listed on Jumia to understand how product performance from pricing to discount affect sales and customer engagement. The first step was to clean and prepare the raw dataset. Then perform data analysis to create visualizations, and later developed an interactive dashboard that presented the findings.
These analysis aim to provide useful insights that can support better pricing strategies, promotional decisions, and customer engagement for Jumia.

Cleaning the Data

Before cleaning I copied the worksheet to a new sheet for reference purposes or incase something goes wrong or I accidentally delete data.

Missing Values- I checked every column for empty cells. Which were plenty in the ratings column. I wroteNot available in the blanks spaces for accurate output during analysis.
** Duplicates**- Searched and removed duplicates and fixed other inconsistencies.
Numeric transformation-Fixed the review column data as it was stored as negative numbers. Also removed the Ksh and commas in the Current price and old price columns to numeric values. Lastly was removing the out of 5in the ratings column. After cleaning this is what was displayed:

The new calculated columns

At this point I added the new columns which are:

Discount Amount-Discount Amount = Old Price − Current Price.=C2-B2
Rating Category- =IF(G2="","",IF(G2<3,"Poor",IF(G2<=4,"Average",IF(G2<=4.5,"Good","Excellent"))))
Discount Category-=IF(D2="","",IF(D2<20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount")))
This means below 20%-Low, 20%-40%=Medium and above 40% is high
Price Category- =IF(B2<1000,"Low Price",IF(B2<=2000,"Medium Price","High Price"))
This means that Below Kes 1000=Low price, Kes 1000-2000= Medium Price and above Kes 3000.

Data Analysis

For the KPIs I used the following formulas

Total Products-=COUNTA('Excel_jumia_dataset'!A2:A113)
Average Current Price-=AVERAGE('Excel_jumia_dataset'!B2:B113)
Average Old Price-=AVERAGE('Excel_jumia_dataset'!C2:C113)
Average Discount-=AVERAGE('Excel_jumia_dataset'!E2:E113)
Average Rating-=AVERAGE('Excel_jumia_dataset'!I2:I113)
Total Reviews-=SUM('Excel_jumia_dataset'!H2:H113)
Most Expensive Product-=XLOOKUP(B9,'Excel_jumia_dataset'!B2:B113,'Excel_jumia_dataset'!A2:A113)
Least Expensive Product-=XLOOKUP(B11,'Excel_jumia_dataset'!B2:B113,'Excel_jumia_dataset'!A2:A113)
This results to:

Dashboard creation process

Putting everything together to create a dashboard I used
KPIs-The screenshot above to be specificthat shows total products, average price, average discount, average rating, and total reviews.
Pivot charts- I created multiple pivot charts to showcase the top 10 products by discount, rating and reviews.
Slicers- To connect all PivotTables/charts for Rating Category, Discount Category, and Price Category, so the whole dashboard can be filtered interactively.

Correlation Analysis

Used CORREL() to test three relationships:
Discount vs Reviews--0.1368, This means that higher discounts don't influence higher customer engagement.
Rating vs Reviews-0.0572, So a product having a high rating doesn't necessarily mean it has lots of reviews.
Current Price vs Rating-0.1080, Expensive products are not clearly rated higher than cheaper products.
Key Insights and Business Recommendations

Prioritize highly reviewed products when evaluating customer engagement because review volume varies substantially across products.
Monitor products with low ratings and identify opportunities to improve product quality, descriptions, or customer experience.
Focus promotional efforts on products where discounts can attract attention without compromising product quality.
Use the dashboard's slicers to compare product performance across different price, discount, and rating categories.
Investigate products with high discounts but poor ratings, as large discounts may not compensate for poor customer satisfaction.

Conclusion

The analysis showed that discounts, ratings, and customer reviews did not have strong relationships in this dataset. In particular, the weak correlation between discount percentage and reviews suggests that offering larger discounts does not necessarily result in higher customer engagement. The dashboard also made it easier to identify highly reviewed products, highly discounted products, and products that may require attention due to low ratings. The interactive dashboard brings these findings together through KPI cards, charts, PivotTables, and slicers, allowing users to explore product performance across different rating, discount, and price categories.
Overall, this project strengthened my understanding of Excel for data cleaning, analysis, visualization, and dashboard development, while also demonstrating how data-driven insights can support better e-commerce business decisions.
You can find the README.md and workbook that contains the clean dataset, pivot charts, pivot tables, Excel formulas, slicers and the dashboard on my GitHub link below:

https://github.com/naomionderi-codes/Jumia-Product-Performance-Dashboard

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

Naomi Kwamboka Onderi — Sat, 29 Aug 2026 19:56:21 +0000

Introduction

Excel is a powerful tool used in data analytics, it is one of the most used tools for working with data. You can use Excel to collect, organize, clean, analyze, visualize, and present data without needing to write code. It is also the easiest way you can learn important analytical concepts while working with an actual spreadsheet.

Definition of Excel and Data Analytics

Excel is a spreadsheet that allows you to store information in rows and columns.
Data analytics refers to the process of examining data to discover useful information, patterns, and insights that can help with decision-making.
For example a company like LC Waikiki has thousands of sales records. The raw data might contain:

Customer names
Sales representatives
Store Locations
Product Names
Sales etc.

Understanding the Excel Interface

Workbook- This is basically an excel file.
Worksheet- This is an individual spreadsheet inside a workbook. The worksheet tabs are found at the bottom.
For example:Sheet1 | Sheet2 | Sheet3
Rows- They run horizontally across the spreadsheet. They are identified by numbers.
Columns- They run vertically. They are identified by letters.
Cell- This is the intersection of a row and a column.
Cell Range- This is a group of cells. A1:C10

Example of a Worksheet

Formatting data

Formatting makes data easier to read. Correct formatting becomes especially important when analyzing dates and numerical values.
You can use the following formatting options:

Date
Currency
Time
Percentage
Number
Fraction
Text

Basic Excel Formulas

SUM adds numbers together. =SUM(F2:F6)
AVERAGE calculates the mean. =AVERAGE(F2:F6)
COUNT counts cells containing numbers. =COUNT(F2:F6)
COUNTA counts cells that are not empty. =COUNTA(B2:B6)
MAX returns the largest value. =MAX(F2:F6)
MIN returns the smallest value. =MIN(F2:F6)
IF function allows Excel to make decisions. =IF(F2>500,"High","Low") This type of logic becomes very useful when analyzing large datasets.

Sorting Data

Sorting allows you to arrange your data according to a particular order. Like Largest → Smallest ,or alphabetically from A-Z,Z-A etc

Filtering Data

Filtering allows you to display only the records that meet specific conditions.

What is Data Cleaning?

Data cleaning is the process of identifying and correcting problems in a dataset so that the data is accurate, consistent, complete, and suitable for analysis and decision-making.
Some of the issues one can encounter in a large data set may include:

Invalid values
Inconsistent capitalization- These can be standardized by using functions like =UPPER(A2) to change characters to uppercase, =LOWER(A2) to change characters to lowercase, =PROPER(A2) to change characters to proper capitalization.
Missing values
Duplicate records- This is when the same record appears more than once.
Spelling mistakes- Find & Replace (ctrl+H) can be used to fix spelling mistakes
Incorrect dates- You should make sure dates are stored consistently and recognized as actual dates rather than text.
Extra spaces- Excel can treat them as different text values. The **TRIM **function helps remove the unnecessary spaces. =TRIM(A2)
Wrong currencies
Numbers stored as text- =VALUE(A2) can be used to fix this.

Before analyzing data, these issues should be addressed.

How to use Find & Replace for Data Cleaning

Find & Replace is one of the simplest and most useful Excel cleaning tools. You can open it using Ctrl + H. It is useful in correcting abbreviations, unwanted characters, spelling errors, inconsistent labels and category names.

Data Validation

Data validation helps control what users can enter into a spreadsheet. This reduces the likelihood of users entering inconsistent values. This is particularly useful when creating spreadsheets that other people will use to enter data.

Conclusion

When you receive a new dataset, carefully go through the entire worksheet before creating charts and calculating averages. Understand and inspect the dataset for irregularities and any inconsistencies. And after fixing the errors review the clean dataset again just incase. Learning Excel for data analytics is a gradual process and you don't need to learn every Excel function before you can start analyzing data. The skills you develop in Excel can be transferred to different areas of data analytics.

How To Upload Your First Project From a Local Folder to GitHub Using Git and SSH.

Naomi Kwamboka Onderi — Sat, 22 Aug 2026 21:51:01 +0000

Introduction

If you want to upload a project from your computer to your GitHub account, it can be done by simply using Git and SSH. This is one of the most important technical skills you can learn as a beginner because you will use this every time you want to create and or update an existing project. For a person using Windows OS, follow these simple steps as illustrated below.

1. Download Git and create a GitHub account.

The GitHub account can be generated easily on the browser by using your email/username and a password. Git can be downloaded from Microsoft Store and installed in your computer.

2. Set up your Identity.

Since it's your first time you are required to configure your name and email after installation.
Run the following commands on Gitbash
git config --global user.name "Your Name" Insert your name
Then run
git config --global user.email "your-email@example.com"Insert your email, the one used to create your GitHub account

3. Check whether you already have an SSH key

Run:
ls -al ~/.ssh
You'll see
id_ed25519 id_ed25519.pub
id_ed25519refers to the private key and id_ed25519.pub refers to the public key.

4. Generate a new SSH key

Run:
ssh-keygen -t ed25519 -C "your-email@example.com"Replace your email with your GitHub email
You'll then see Generating public/private ed25519 key pair.
Press Enter since it's your first key.
Next you'll see
Enter passphrase (empty for no passphrase):Enter passphrase (password) for security
You'll be asked to confirm your passphrase just enter it again.
You'll see messages indicating the private and public keys are being generated.
On Windows open PowerShell as an Administrator and run:
Get-Service -Name ssh-agent | Set-Service -StartupType Manual
Then:
Start-Service ssh-agent
Return to Gitbash and add your private SSH key:
ssh-add ~/.ssh/id_ed25519
It should indicate that the Identity was added.

5. Add your public SSH key to Github

Run:
cat ~/.ssh/id_ed25519.puband press Enter. Copy the entire line that pops up upto the .pub
On GitHub open Settings → SSH and GPG keys then select New SSH Key give it a title and paste the public key then press add key.

6. Locate your project folder and adding it to GitHub

Locate your project it may be in the documents folder on your desktop. Open Gitbash and navigate into the project folder
cd ~/Documents/Loan Report
You can use ls confirm
Run:
git init -b main initializing an untracked local project.
Then run:
git status this shows the untracked files.
Then run:
git add . this adds the file in the current directory. Rerun git status again.
Then run:
git commit -m "Initial commit"

7. Create a new Repository on GitHub

Create a new repository with a new name preferably the same name as the file Loan Report and choose whether it is public or private.
After creating the repository, you will see the setup information and select and copy the SSH URL. Paste the link in Gitbash then press enter.
Whatever comes up replace the name section with your GitHub username and press enter.
Run:
git remote -v this confirms that Git knows where the GitHub repository is
Then run:
git push -u origin main This pushes a new repository to GitHub.
Afterwards go to GitHub and confirm if the project is there.

Conclusion

This is the basic Git and GitHub steps that you'll have to use repeatedly while working on different kind of projects and with time all of these will become easier.

DEV Community: Naomi Kwamboka Onderi

Joins In SQL

Introduction-SQL window functions

Ranking functions

FIRST_VALUE() and LAST_VALUE()

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

SQL joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

SELF JOIN

A basic CTE

A recursive CTE

How CTEs differ from subqueries

Nested subqueries

Power BI Data Modelling, Relationships & Joins

Introduction

Data Modelling in Power BI

Flat Tables

Advantages of a Flat Table

Disadvantages of a Flat Table

Star Schema

Advantages of a Star Schema:

Snowflake Schema

Advantages of a Snowflake Schema

Comparing the Three Approaches

Fact Tables and Dimension Tables

Grain or Granularity

Relationships in Power BI

Relationship Cardinality

Primary Keys and Foreign Keys

Referential Integrity

Active and Inactive Relationships

Filter Direction

Joins in Power Query

Power Query Joins vs Power BI Relationships

When Should You Use a Merge Instead of a Relationship?

Why Keep Fact and Dimension Tables Separate?

Conclusion

Jumia Product Performance Analysis & Pricing Dashboard

Introduction

Cleaning the Data

The new calculated columns

Data Analysis

Dashboard creation process

Correlation Analysis

Conclusion

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

Introduction

Definition of Excel and Data Analytics

Understanding the Excel Interface

Example of a Worksheet

Formatting data

Basic Excel Formulas

Sorting Data

Filtering Data

What is Data Cleaning?

How to use Find & Replace for Data Cleaning

Data Validation

Conclusion

How To Upload Your First Project From a Local Folder to GitHub Using Git and SSH.

Introduction

1. Download Git and create a GitHub account.

2. Set up your Identity.

3. Check whether you already have an SSH key

4. Generate a new SSH key

5. Add your public SSH key to Github

6. Locate your project folder and adding it to GitHub

7. Create a new Repository on GitHub

Conclusion

`FIRST_VALUE()` and `LAST_VALUE()`

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

`INNER JOIN`

`LEFT JOIN`

`RIGHT JOIN`

`FULL OUTER JOIN`

`SELF JOIN`