DEV Community: Dorcas Chebet

SQL FOR ANALYTICS:WINDOW FUNCTIONS,JOINS,CTEs AND SUBQUERIES

Dorcas Chebet — Sun, 18 Oct 2026 13:25:03 +0000

Introduction

The Hospital Dataset is Used in this Article
The dataset is structured as a star schema created from a single flat file containing hospital visit data called hospital_visits_flat_original. A main fact table keeps track of every visit, and six dimension tables explain who was involved, where the visit took place, and why it happened. Queries are written using PostgreSQL syntax, and there are notes explaining any differences when the same query would work in MySQL or SQL Server.

SQL Window Functions

Window function explanation
An average calculation like AVG(total_bill_kes) along with GROUP BY combines multiple rows into a single row for each group. A window function does a similar calculation but retains each original row, adding the result as a new column. It responds to the question “how does this visit’s bill compare with its department’s average?” without loosing the visit itself.

Group by: one rows per department
select department_id,avg(total_bill_kes) as avg_bill
from visits_fact
Group by department_id;

select visit_id,department_id,total_bill_kes,
Avg(totall_bill_kes) Over (partition by department_id) as department_avg_bill
From visits_facts;

The first query gives one row for each department. The second one brings back every visit, and each row shows the average for its department.
The anatomy of a window: OVER, PARTITION BY, ORDER BY and frames.

function_name(...)over(
partition by column_list
order by column_list
rows between -- and --

OVER() is what changes a regular function into a window function. It determines the range of rows that the function is able to view. An empty OVER () means the window is the entire result set.
Partition by splits rows into separate groups, similar to group by, but it doesn't combine the rows into a single result. The function restarts for each partition. Partitioning by department_id means that each department is ranked or summed up individually.
ORDER BY, which is used inside OVER, determines the order of rows within each group or partition. It is needed for ranking functions and for LAG and LEAD, and when used with aggregates, it helps create running calculations. It is different from the ORDER BY clause at the end of the query, which is used to sort the final result.
Window frames limit the window to a sliding set of rows around the current row. The frame is defined as ROWS BETWEEN AND , with the boundaries being:

unbounded preceding -the first row of the partition
n preceding - n rows before the current row
current row - the current row
n following - n rows after the current row
unbounded following - the last row of partition

Two frame facts

ROWS vs RANGE. ROWS counts physical rows. RANGE treats rows that have the same ORDER BY values (peers) as a single group, so rows that are tied are all included together.
The default frame. When a window has an ORDER BY clause but doesn't specify a frame, SQL uses the RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW by default. This is why a running total works automatically, and it's also why LAST_VALUE behaves incorrectly by default ### Ranking functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE

Ranking functions assign a position to each row within its partition. They are different only in the way they handle ties.

Scenario: The management wishes to sort doctors based on the amount of revenue they generate within each department.
SELECT d.department_name,
doc.doctor_name,
SUM(f.total_bill_kes) AS total_billed,
ROW_NUMBER() OVER (PARTITION BY d.department_name ORDER BY SUM(f.total_bill_kes) DESC) AS row_num,
RANK() OVER (PARTITION BY d.department_name ORDER BY SUM(f.total_bill_kes) DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY d.department_name ORDER BY SUM(f.total_bill_kes) DESC) AS dense_rnk
FROM visits_fact f
JOIN dim_doctors doc ON doc.doctor_id = f.doctor_id
JOIN dim_departments d ON d.department_id = f.department_id
GROUP BY d.department_name, doc.doctor_name
ORDER BY d.department_name, total_billed DESC;

What it does: GROUP BY first calculates the total billing for each doctor within each department. The window functions then operate on those totals, starting fresh in each department (PARTITION BY) and sorting the highest total first. All three ranking functions are shown together so you can compare how ties are handled.

SELECT visit_id,
total_bill_kes,
NTILE(4) OVER (ORDER BY total_bill_kes DESC) AS cost_quartile
FROM visits_fact;
It organizes all visits by cost, starting with the most expensive and ending with the least, then divides the list into four equal parts. Quartile 1 includes the most expensive visits, while quartile 4 has the least costly ones. If the number of rows doesn't split evenly into the buckets, like 10 rows into 4 buckets, the first few buckets get an extra row.For example, it would be 3, 3, 2, 2.

Department name	Doctor_name	totall_billed	row number	Rank	Dense_rank
Cardiology	Dr.Otieno	900000	1	1	1
Cardiology	Dr.Wanjiru	900000	2	1	1
Cardiology	Dr.Kamau	700000	3	3	2
Cardiology	Dr.Achieng	500000	4	4	3

Otieno and Wanjiru tie. ROW_NUMBER still separates them (1 and 2). RANK starts at 1 and then goes to 3. DENSE_RANK assigns a 1 to both and then continues with 2.
When to use which: use ROW_NUMBER to select exactly one row from each group, such as one row per patient.
most recent visit, or removing duplicate records). Use RANK to show competition-style rankings where there can be gaps between positions. Use DENSE_RANK for “top N levels” questions.
Top-N per group. Window functions cannot be used with filters in the WHERE clause, so you should put the query inside a common table expression.
WITH doctor_revenue AS (
SELECT d.department_name, doc.doctor_name,
SUM(f.total_bill_kes) AS total_billed,
DENSE_RANK() OVER (PARTITION BY d.department_name ORDER BY SUM(f.total_bill_kes) DESC) AS dr
FROM visits_fact f
JOIN dim_doctors doc ON doc.doctor_id = f.doctor_id
JOIN dim_departments d ON d.department_id = f.department_id
GROUP BY d.department_name, doc.doctor_name
)
SELECT department_name, doctor_name, total_billed
FROM doctor_revenue
WHERE dr <= 2;

This shows the top two billing levels in each department, including all doctors who are tied at those levels.
NTILE(n)
NTILE(n) splits the ordered rows in a group into n nearly equal parts and assigns each row a number based on which part it belongs to. It is used for quartiles, deciles, and other types of segmentation.
Scenario: The finance department wants to divide visits into four cost groups to identify the top 25% that are the most expensive.
Expected result (sample):
| visit_id | Total_bill_kes | Cost_quartile |
| ---|---|---:|
| 4120 | 980000 | 1 |
| 3771 | 760000 | 1 |
| 3050 | 410000 | 2 |
| 1204 | 9500 | 4 |

Offset functions: LAG and LEAD

LAG(column, offset, default) retrieves a value from a row that comes before the current row within the same partition. LEAD reads data from a row that comes after the current row, with an option to specify how many rows to skip and a default value if no data is found. The offset starts at 1, and if no row is found, the default value is set to NULL. They eliminate the need to join a table to itself in order to compare consecutive records.
Scenario (LAG): The quality team monitors readmissions, which are cases where patients come back to the hospital shortly after they were discharged. For every visit, we need the gap because that patient's previous stay has already ended. A stay is considered to begin on the visit date and continue for the number of days specified as the length of stay.

SELECT patient_id,
visit_id,
visit_date,
LAG(visit_date + length_of_stay_days)
OVER (PARTITION BY patient_id ORDER BY visit_date) AS previous_discharge,
visit_date - LAG(visit_date + length_of_stay_days)
OVER (PARTITION BY patient_id ORDER BY visit_date) AS days_since_last_stay
FROM visits_fact;

For each patient, the visits are arranged in order based on the date. LAG(...) gets the end date of the patient's last stay, and by taking the current visit date and subtracting that end date, it calculates the number of days between them. PostgreSQL gives an integer when you subtract dates; in MySQL, you can use DATEDIFF(visit_date, LAG(...)), and in SQL Server, you use DATEDIFF(day, LAG(...), visit_date), and if you need to add days, you can use DATEADD.
The first visit of each patient does not have an earlier stay, so it is shown as NULL. Wrapping the query in a CTE and filtering based on days since the last stay.

SELECT patient_id,
visit_date,
LEAD(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit_date
FROM visits_fact;

The expected result is that each row displays a visit date along with the date of the patient’s next visit, and for the patient’s last visit, it shows NULL. LAG looks back and LEAD looks ahead; besides the direction, they are the same.

FIRST_VALUE and LAST_VALUE

FIRST_VALUE(col) gets the value from the first row in the window frame, and LAST_VALUE(col) gets the value from the last row. They help in comparing every row with a beginning point or an ending point.
For each visit, display the patient's first and latest diagnosis, allowing clinicians to understand the patient's medical journey from the beginning up to the present.

SELECT f.patient_id,
f.visit_date,
dg.diagnosis_name,
FIRST_VALUE(dg.diagnosis_name) OVER (
PARTITION BY f.patient_id ORDER BY f.visit_date
) AS first_diagnosis,
LAST_VALUE(dg.diagnosis_name) OVER (
PARTITION BY f.patient_id ORDER BY f.visit_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS latest_diagnosis
FROM visits_fact f
JOIN dim_diagnosis dg ON dg.diagnosis_id = f.diagnosis_id;

What it does: FIRST_VALUE uses the default frame since the first row in a partition is always part of it. LAST_VALUE requires a clearly defined frame that extends to UNBOUNDED FOLLOWING.
The classic LAST_VALUE trap. With the default frame, which stops at the current row, LAST_VALUE just gives back the value from the current row, making it seem like the function isn't working properly. Always include ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to correctly identify the actual last row within the partition.
Expected result (sample, patient 101):

patient_id	visit_date	Diagnosis_name	First_diagnosis	latest_diagnosis
101	2026-01-05	Hypertension	Hypertension	Malaria
101	2026-01-25	Pneumonia	Hypertension	Malaria
101	2026-06-02	Malaria	Hypertension	Malaria

Aggregate window functions include SUM, AVG, COUNT, MIN, and MAX.These functions are used to perform calculations across a set of rows that are related to the current row.They help in analyzing data by providing summary statistics within a specific window of data.
The familiar aggregates become window functions when they are followed by OVER. Three patterns cover most real-world uses:

Whole-partition value (OVER (PARTITION BY ...) with no ORDER BY): each row sees the total, average, count, minimum, or maximum of the entire group.
Running calculation (ORDER BY added): the aggregate adds up each row one at a time.
Moving calculation, which uses an explicit window of rows between a certain number of preceding rows and the current row, is known as a sliding window. Scenario A: whole-partition statistics. Compare every visit with its department’s profile.

SELECT visit_id,
department_id,
total_bill_kes,
COUNT(*) OVER (PARTITION BY department_id) AS dept_visits,
SUM(total_bill_kes) OVER (PARTITION BY department_id) AS dept_total_billed,
ROUND(AVG(total_bill_kes) OVER (PARTITION BY department_id), 2) AS dept_avg_bill,
MIN(total_bill_kes) OVER (PARTITION BY department_id) AS dept_min_bill,
MAX(total_bill_kes) OVER (PARTITION BY department_id) AS dept_max_bill,
total_bill_kes - AVG(total_bill_kes) OVER (PARTITION BY department_id) AS diff_from_avg
FROM visits_fact;

Each time you visit, it keeps a separate bill and displays five statistics at the department level. The final column indicates whether a visit is more (positive) or less (negative) than the average for its department, highlighting visits that are unusually costly and not connected through a join or GROUP BY clause.

Window functions vs GROUP BY, and a few rules

GROUP BY
Window function
Rows returned
One per group
One per original row
Detail columns
Lost unless grouped
Fully retained
Best for
Summary reports
Rankings, running totals, comparisons with a group
Three important rules to keep in mind: window functions can only be used in the SELECT clause or the ORDER BY clause, so you should use a CTE or a subquery to filter them first; they are processed after the WHERE, GROUP BY, and HAVING clauses; and ranking functions require an ORDER BY statement inside the OVER clause to work properly.

SQL Joins

A join combines rows from two tables by matching a join condition, which is usually a foreign key to a primary key. In this star schema, the data is intentionally split into different tables, like visits stored in visits_fact and patient information in dim_patients, so you have to use joins to put everything back together and see the complete picture.
To make the differences clearer, imagine these small examples:
dim_patients: (1, Wanjiku, Nairobi), (2, Otieno, Kisumu), (3, Halima, Mombasa), (4, Kiprono, Nakuru)
Visits fact: (V1, patient 1, Cardiology), (V2, patient 1, Orthopaedics), (V3, patient 2, Paediatrics), (V4, patient 99, Cardiology)
Halima, patient 3, and Kiprono, patient 4, have never had any visits recorded.The visit V4 is linked to patient 99, who doesn't exist, which is due to a data entry mistake that created an orphan record.

INNER JOIN

Returns only the rows that have a match in both tables. Unmatched rows on either side disappear.
SELECT p.county, p.patient_gender, f.visit_id, f.total_bill_kes
FROM visits_fact f
INNER JOIN dim_patients p ON p.patient_id = f.patient_id;
The results are based on the samples: V1, V2 (Wanjiku), and V3 (Otieno). Halima and Kiprono disappear because they have no visits, and V4 disappears because it has no patient.
Use this when you only want records that are present on both sides, like "total billing by county," because a visit without a known patient can't be assigned to a county in the first place. A standard star-schema report connects multiple inner joins from the fact table.
SELECT d.department_name, doc.doctor_name, p.county, f.total_bill_kes
FROM visits_fact f
JOIN dim_departments d ON d.department_id = f.department_id
JOIN dim_doctors doc ON doc.doctor_id = f.doctor_id
JOIN dim_patients p ON p.patient_id = f.patient_id;
This provides one readable row for each visit, including the names of the department, doctor, and county.(Joining on its own refers to an INNER JOIN.)

LEFT JOIN (LEFT OUTER JOIN)

Returns every row from the left table along with any matching rows from the right table. When there's no match, the columns on the right side will show as NULL.
SELECT p.patient_id, p.county, f.visit_id
FROM dim_patients p
LEFT JOIN visits_fact f ON f.patient_id = p.patient_id;
Anti-join pattern. Add WHERE f.visit_id IS NULL to keep only the unmatched left rows. This answers “which registered patients have never had a visit?”:

SELECT p.patient_id, p.county
FROM dim_patients p
LEFT JOIN visits_fact f ON f.patient_id = p.patient_id
WHERE f.visit_id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image: all rows from the right table, with matching left rows or NULLs.

SELECT p.county, f.visit_id, f.total_bill_kes
FROM dim_patients p
RIGHT JOIN visits_fact f ON f.patient_id = p.patient_id;

FULL OUTER JOIN

Returns all rows from both tables, matching where possible and filling NULLs on whichever side lacks a partner.

SELECT p.patient_id AS dim_patient_id,
f.patient_id AS fact_patient_id,
f.visit_id
FROM dim_patients p
FULL OUTER JOIN visits_fact f ON f.patient_id = p.patient_id;

CROSS JOIN

Returns the Cartesian product by combining each row from the first table with every row from the second table, without any specific condition for matching rows. Five departments and two genders result in ten different rows.

SELECT d.department_name, g.patient_gender, COUNT(f.visit_id) AS visits
FROM dim_departments d
CROSS JOIN (SELECT DISTINCT patient_gender FROM dim_patients) g
LEFT JOIN dim_patients p ON p.patient_gender = g.patient_gender
LEFT JOIN visits_fact f ON f.patient_id = p.patient_id
AND f.department_id = d.department_id
GROUP BY d.department_name, g.patient_gender
ORDER BY d.department_name, g.patient_gender;

The cross join builds a complete department-by-gender grid first. The LEFT JOINs then attach the actual visits, so a combination with no visits still appears with a count of 0 instead of being missing. This “report skeleton” use is the most practical reason to cross join. Use it deliberately, because crossing two large tables produces enormous results.

SELF JOIN

Not a separate keyword but a technique: a table joined to itself under two aliases. It suits comparisons between rows of the same table. The hospital tables have no supervisor column, so the natural self join here is on the fact table, to find repeat visits by the same patient within 30 days.

SELECT a.patient_id,
a.visit_id AS first_visit,
a.visit_date AS first_date,
b.visit_id AS repeat_visit,
b.visit_date AS repeat_date,
b.visit_date - a.visit_date AS days_apart
FROM visits_fact a
JOIN visits_fact b
ON b.patient_id = a.patient_id
AND b.visit_date > a.visit_date
AND b.visit_date <= a.visit_date + 30
ORDER BY a.patient_id, a.visit_date;

Alias A represents the earlier visit, and alias B represents the later visit by the same patient. The condition b.visit_date > a.visit_date prevents a visit from being matched with itself and ensures each pair is not reported twice, and + 30 limits the time difference to 30 days. Every row returned is a repeat visit.

Common Table Expressions (CTEs)

CTEs is a common whose table expression is a named, temporary set of results that is created at the beginning of a query using the WITH keyword. It lasts only as long as that single statement is made. Think of it as giving a name to an intermediate step so the main query can use it just like a table. Common Table Expressions (CTEs) are important because they improve the readability of complex queries by organizing steps in a top-to-bottom flow rather than having nested structures.They also allow for reusability, as a single CTE can be used multiple times within a query.Additionally, CTEs make debugging easier since each step can be executed separately to test and verify its functionality.

WITH cte_name AS (
SELECT ...
)
SELECT ... FROM cte_name;

Basic CTE

Scenario: Find departments whose average bill per visit is above the hospital-wide average.

WITH dept_avg AS (
SELECT department_id,
AVG(total_bill_kes) AS avg_bill
FROM visits_fact
GROUP BY department_id
)
SELECT d.department_name, ROUND(da.avg_bill, 2) AS avg_bill
FROM dept_avg da
JOIN dim_departments d ON d.department_id = da.department_id
WHERE da.avg_bill > (SELECT AVG(total_bill_kes) FROM visits_fact)
ORDER BY da.avg_bill DESC;

What it does: The CTE dept_avg calculates one average for each department. The main query connects that small result with dim_departments to get the department names in a readable format, then filters to include only those departments with bills higher than the overall average. Without the CTE, you would have to repeat the AVG in a HAVING clause or nest the query inside another one.

Multiple CTEs

Several CTEs can be chained under a single WITH, separated by commas. Each can refer to the ones defined before it, so a complex analysis reads like a sequence of steps.
Scenario: For each county, compare total billing with average length of stay, and show each county’s share of total revenue.

WITH county_revenue AS (
SELECT p.county,
COUNT(*) AS visits,
SUM(f.total_bill_kes) AS total_billed
FROM visits_fact f
JOIN dim_patients p ON p.patient_id = f.patient_id
GROUP BY p.county
),
county_stay AS (
SELECT p.county,
ROUND(AVG(f.length_of_stay_days), 1) AS avg_stay_days
FROM visits_fact f
JOIN dim_patients p ON p.patient_id = f.patient_id
GROUP BY p.county
),
grand_total AS (
SELECT SUM(total_billed) AS all_billed FROM county_revenue
)
SELECT r.county,
r.visits,
r.total_billed,
s.avg_stay_days,
ROUND(100.0 * r.total_billed / g.all_billed, 1) AS pct_of_revenue
FROM county_revenue r
JOIN county_stay s ON s.county = r.county
CROSS JOIN grand_total g
ORDER BY r.total_billed DESC;

What it does: county_revenue and county_stay each capture one specific aspect for each county.grand_total is based on county_revenue, which is a CTE that refers back to an earlier CTE. The last query combines them and uses the overall total to calculate each county's share as a percentage. A single-row CTE like grand_total is a clean method to include a single number in every row.

Common Table Expressions with joins, aggregations, and window functions.

Common Table Expressions (CTEs) are the standard method for filtering within a window function, and they cannot be used in the WHERE clause. This example identifies each patient's latest visit, including the doctor and the department involved.

WITH numbered AS (
SELECT f.patient_id,
f.visit_date,
f.doctor_id,
f.department_id,
f.total_bill_kes,
ROW_NUMBER() OVER (PARTITION BY f.patient_id ORDER BY f.visit_date DESC) AS rn
FROM visits_fact f
)
SELECT p.patient_id, p.county, n.visit_date AS last_visit,
doc.doctor_name, d.department_name, n.total_bill_kes
FROM numbered n
JOIN dim_patients p ON p.patient_id = n.patient_id
JOIN dim_doctors doc ON doc.doctor_id = n.doctor_id
JOIN dim_departments d ON d.department_id = n.department_id
WHERE n.rn = 1;
What it does: The CTE lists each patient's visits starting with the most recent one and moving to the oldest. The main query retains only the most recent visit, where rn equals 1, and incorporates the descriptive names through joins. The result shows one row for each patient who has ever been visited.

Recursive CTEs

A recursive CTE references itself. It is built for data that is organized in a hierarchy or sequence, and it has two sections connected by UNION ALL.

Anchor member: the starting rows (runs once).
A recursive member is a query that uses the results from the CTE itself to generate the next set of rows, and this process keeps repeating until there are no more rows to return. In PostgreSQL and MySQL, the keyword is WITH RECURSIVE; in SQL Server, it is simply WITH. The hospital tables don't have a parent-child structure like a doctor-to-supervisor hierarchy, so the best recursive CTE to use here creates a continuous list of dates. This solves an actual reporting issue: days with no visits are not included in the GROUP BY visit_date, which affects trends and the 7-day moving average in Section 1.6.

WITH RECURSIVE calendar AS (
-- Anchor: the first date in the data
SELECT MIN(visit_date)::date AS day
FROM visits_fact
SELECT c.day,
COUNT(f.visit_id) AS visits,
COALESCE(SUM(f.total_bill_kes), 0) AS revenue
FROM calendar c
LEFT JOIN visits_fact f ON f.visit_date::date = c.day
GROUP BY c.day
ORDER BY c.day;

What it does: The anchor returns the earliest visit date. Each recursion adds one day, stopping when the latest visit date is reached. The calendar is then LEFT JOINed to the visits, so a quiet day shows 0 visits and 0 revenue instead of disappearing. (In MySQL use day + INTERVAL 1 DAY; in SQL Server use DATEADD(day, 1, day).)

CTEs vs subqueries

Both let you use the result of one query inside another, and for simple needs they give the same answer. The differences are about structure and capability.
The same question written both ways, “visits that cost more than the hospital average”:

SELECT visit_id, total_bill_kes
FROM visits_fact
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits_fact);

-- CTE
WITH avg_bill AS (SELECT AVG(total_bill_kes) AS overall_avg FROM visits_fact)
SELECT f.visit_id, f.total_bill_kes
FROM visits_fact f
CROSS JOIN avg_bill a
WHERE f.total_bill_kes > a.overall_avg;

What it does: The CTE lists each patient's visits starting with the most recent one and moving to the oldest. The main query retains only the most recent visit, where rn equals 1, and incorporates the descriptive names through joins. The result shows one row for each patient who has ever been visited.

Recursive CTEs

A recursive CTE references itself. It is built for data that is organized in a hierarchy or sequence, and it has two sections connected by UNION ALL.

Anchor member: the starting rows (runs once).
A recursive member is a query that uses the results from the CTE itself to generate the next set of rows, and this process keeps repeating until there are no more rows to return. In PostgreSQL and MySQL, the keyword is WITH RECURSIVE; in SQL Server, it is simply WITH. The hospital tables don't have a parent-child structure like a doctor-to-supervisor hierarchy, so the best recursive CTE to use here creates a continuous list of dates. This solves an actual reporting issue: days with no visits are not included in the GROUP BY visit_date, which affects trends and the 7-day moving average in Section 1.6. ### SQL Subqueries A subquery is a SELECT statement that is placed inside another SQL statement. The inner query executes, and its outcome is used by the outer query. Subqueries allow you to answer questions that rely on the result of another question.For example, to find out which visits cost more than the average, you first need to know what the average cost is.

Scalar subqueries

A scalar subquery gives back just one row and one column, which is a single value, so it can be used in any place where a single value is needed. If the query brings back more than one row, the database will show an error.
Scenario: Show all visits where the cost is higher than the average bill for the entire hospital.

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

What it does: The inner query returns one number (say 21,500). The outer query keeps only visits above it. The same idea works in the SELECT list:
SELECT visit_id,
total_bill_kes,
total_bill_kes - (SELECT AVG(total_bill_kes) FROM visits_fact) AS diff_from_hospital_avg
FROM visits_fact;

Non-correlated (independent) subqueries

A subquery that isn't related can work by itself; it doesn't need to look at the main query. It runs once and the result is used again. The scalar examples above are non-correlated,

Correlated subqueries

A correlated subquery refers to a column of the outer query, so it is logically re-evaluated once per outer row. It cannot run on its own.

Example: Find visits that cost more than the average for their own department.

SELECT f.visit_id, f.department_id, f.total_bill_kes
FROM visits_fact f
WHERE f.total_bill_kes > (
SELECT AVG(f2.total_bill_kes)
FROM visits_fact f2
WHERE f2.department_id = f.department_id -- correlation with the outer row
);

Nested subqueries

Subqueries can be placed inside other subqueries to any depth. Scenario: Find patients who were treated in the department with the highest total billing.

SELECT patient_id, county
FROM dim_patients
WHERE patient_id IN (
SELECT patient_id
FROM visits_fact
WHERE department_id = (
SELECT department_id
FROM visits_fact
GROUP BY department_id
ORDER BY SUM(total_bill_kes) DESC
LIMIT 1
)
);

What it does, from the inside out: first, it identifies the department that has the highest total billing; then, it lists all the patients who have visited that department; finally, it provides the details of those patients. It works, but reading through three levels already requires some effort, and that's when using a CTE or a join would be a better option.(LIMIT 1 is used in PostgreSQL and MySQL; SQL Server uses TOP 1 instead.)

Subqueries with IN, EXISTS, ANY and ALL

IN checks if a value is found in the list of results from the subquery, as shown in section 4.2.
EXISTS returns TRUE when the subquery has at least one row; it only checks if a match is present, not what the actual values are. It often goes along with that and stops at the first match.

-- Patients who have at least one visit longer than 7 days
SELECT p.patient_id, p.county
FROM dim_patients p
WHERE EXISTS (
SELECT 1
FROM visits_fact f
WHERE f.patient_id = p.patient_id
AND f.length_of_stay_days > 7
);
The use of SELECT 1 is a common practice, as the actual value being selected does not matter. NOT EXISTS reverses the test and identifies patients who do not have such a visit.
IN vs EXISTS, and the NOT IN trap. For positive checks, the two usually bring back the same rows. Using NOT IN with a subquery that might return NULL can be risky.If the subquery gives a NULL, the comparison becomes unknown, and the entire query ends up returning no rows. "NOT EXISTS is safe when dealing with NULLs, so it's better to use it for questions about things that do not exist."

-- Patients who never had a visit (safe even if visits_fact.patient_id contains NULLs)
SELECT p.patient_id, p.county
FROM dim_patients p
WHERE NOT EXISTS (SELECT 1 FROM visits_fact f WHERE f.patient_id = p.patient_id);

Putting It All Together: A Real-World Hospital Question

Question: Which visits were readmissions within 30 days of the patient’s previous discharge, in which county and department, and how does each bill compare with its department average?

WITH stays AS (
SELECT patient_id, visit_id, department_id, visit_date, total_bill_kes,
LAG(visit_date + length_of_stay_days)
OVER (PARTITION BY patient_id ORDER BY visit_date) AS prev_discharge
FROM visits_fact
),
readmits AS (
SELECT *, visit_date - prev_discharge AS gap_days
FROM stays
WHERE prev_discharge IS NOT NULL
AND visit_date - prev_discharge <= 30
),
dept_avg AS (
SELECT department_id, AVG(total_bill_kes) AS avg_bill
FROM visits_fact
GROUP BY department_id
)
SELECT p.county,
d.department_name,
r.visit_id,
r.gap_days,
r.total_bill_kes,
ROUND(r.total_bill_kes - da.avg_bill, 2) AS vs_dept_avg
FROM readmits r
JOIN dim_patients p ON p.patient_id = r.patient_id
JOIN dim_departments d ON d.department_id = r.department_id
JOIN dept_avg da ON da.department_id = r.department_id
ORDER BY r.gap_days;
With the concepts combine: a window function (LAG) finds the previous discharge; a CTE chain keeps the steps readable and lets us filter on the window result; an aggregation inside dept_avg supplies a benchmark; and joins attach county and department names. The output is a ready-to-use quality-improvement list: one row per readmission with the gap in days and how costly the stay was relative to its department.

Building the JCars logistics Sale Dashboard in Power BI

Dorcas Chebet — Fri, 02 Oct 2026 11:19:34 +0000

Introduction

JCars Logistics sells vehicles out of nine yards around Kenya — Nairobi, Mombasa, Kisumu,
Eldoret, Kakamega and a few others. The business wanted answers to three plain
questions: where do our sales actually come from, what do they earn us, and can we trust vehicles to reach customers on time?
The data I was handed couldn’t answer any of that.It was 273 order rows across 55 columns, and when I checked, only 69 of those rows barely a quarter were clean enough to use.The rest had something wrong with them.

Investigating the data before touching it

I didn’t open the file and start cleaning. First I wanted to know whether I could trust any of it.
In Power Query I switched on Column quality, Column distribution and Column profile so I could see blanks and distinct values in every column at a glance. Then I started poking at the business logic. Does the recorded revenue match what the price, units and discount say
it should be? Do delivery dates come after order dates, like they obviously should? Does each branch stay inside one region?
Those checks surfaced five families of problems.
Identity. 19 rows had no Order ID at all, and the ones that did used five different prefixes ORD, CAR, LCL, LCL-, LC. Without a key you can rely on, you can’t spot duplicates and you can’t build safe relationships.Prices, costs and fees were sitting in four currencies: KES, USD, EUR and ZAR.
When I compared the converted figures against the originals, the implied rates were suspiciously constant (USD 129, EUR 151, ZAR 7.91), which told me someone had applied a fixed booking rate. Some rows had a currency I couldn’t identify, 10 showed negative revenue and 102 flat-out failed my reconciliation check. That check came straight from the
data: Net Sales = Selling Price × Units × (1 − Discount), and the recorded revenue should
equal either that or that plus the delivery fee. 102 rows matched neither.
Time. Dates were stored as long text, like “Wednesday, March 26, 2025”. Once I converted
them, the problems jumped out: 14 orders were apparently delivered before they were
ordered (one of them 290 days before), 23 dates wouldn’t parse at all, and 56 were simply missing.
Categories. The same thing was written several ways. Person and Individual. N.G.O and NGO, Saloon and Sedan, Wire, RTGS and Bank Transfer. Paid and Completed. Every spelling variant quietly splits a total that should have been one number.
Logic. Delivered orders marked “Returned”. Cancelled orders carrying a “Completed”
payment. Discounts as high as 50%. Vehicle years of 2026.

Cleaning and preparation:

what I did, and why For every fix I wrote down three things — what I did, why I did it and what I was assuming when I did it. The assumptions matter more than the clicks.

Steps What I did Why, and what I assumed.

Keys Standardized the Order IDs and added a surrogate key.I assumed the number identifies the order, so LC1000 and LCL1000 are the same order, just typed differently.Types Converted dates, money and flags to proper data types.Text dates break every time-intelligence function Currency Converted everything to KES at the rates the data implied.I assumed those were the business’s own booking rates.Revenue Rebuilt Revenue as
net sales plus delivery fee.The recorded figure failed reconciliation on 102 rows. ORD1009, for instance, had no recorded revenue, but the rule gives 2,920,000 × 0.93 +140,000 = 2,855,600
Dates Kept the impossible date rows but flagged them I assumed the order is real and the date is just wrong — deleting it would throw away a genuine sale Categories Merged the variants with a mapping table Cleaner slicers, and totals that actually add up Missing value State each choice and what it changed Unit economics Added Unit Selling
Price and Unit Cost columns needed them to work out cost and margin per
vehicle
A few of those decisions came back to bite me, and I caught them during validation:
Zeros aren’t blanks. A missing logistics cost that loads as 0 makes an order look more profitable than it really was. That’s not a rounding issue — it’s a wrong answer.
A currency bug hiding in plain sight. One delivery fee read 426.67 sitting right next to a
logistics cost of 74,000. That 426.67 is clearly a USD figure that never got converted
(426.67 × 129 ≈ 55,000 KES)
A duplicated customer ID. After one of the merges I ended up with both Customer ID and dim_customer.Customer ID in the fact table. I kept one and dropped the other.

Modelling the data

I built it as a star schema: one fact table (facts table, 276 rows) with five dimensions around
it — dim_customer, dim_date, dim_location, dim_sale rep and dim_vehicle.
Why a star schema rather than one big flat table?
• Attributes like region, make and rep fall in exactly one place, so when I fix something, I fix it once.
• The relationships are plain one-to-many, which keeps the DAX predictable and the reports quick.
• dim_date is marked as the official date table, so time intelligence works properly.
Delivery Date is a second date column, so it hangs off an inactive relationship and only wakes up inside measures that call USERELATIONSHIP.
One limitation I made a point of writing down: the customer ID seems to track the source
row number, which means a repeat buyer can get counted as several different customers.
There are 181 distinct names across the 273 rows. So read any customer count with that
caveat in mind — it’s an upper bound, not gospel.

The DAX behind the numbers

Each measure exists to answer one specific question. [Swap in your own measures.]
Total Revenue =1.35bn
CALCULATE
Total Sales =SUM('facts table'[sales])
Total= 1.82bn
SUMX(
FILTER('facts table', 'facts table'[Delivery Status] <>
"Cancelled"),
'facts table'[Units Sold] * 'facts table'[Unit Cost] + 'facts
table'[Logistics Cost]
)
Unit Cost is per vehicle, so just summing the column would be nonsense. SUMX multiplies
by units first, row by row.
Gross Profit = [Total Revenue] - [Total Cost]
total gross profit = 454.68m
Gross Margin % = DIVIDE([Gross Profit], [Total Revenue])
Gross Margin = 25%

Designing the report

I built the report around the three questions, one page each.
Executive overview. KPI cards for Total sales, Gross Margin %,total cost and Total revenue recorded.
Sales analysis. Revenue sliced by branch, sales rep, lead source, make and vehicle type,
with slicers for year, region and make. This is the “where does the money come from?”
Operations and delivery. Delivery days by branch, the spread of delivery times, and a table of the orders that ran late.
A few design calls I stuck to:
• A small palette, used consistently — the same colour always means the same thing.
• Bars sorted by value, never alphabetically.
• Drill-through from region down to branch down to the individual order.
• Chart titles that say what you’re looking at, e.g. Revenue, total cost

Insights

• Riftvalley region brought in Ksh.3,044,400 of revenue, while Western managed only Ksh.1,872,800
• online produced the most orders, but walk in had the highest average
order value.
• SUV gave the best gross margin at 14.72%, against 4.22% for Toyota.

Recommendations

Pin each recommendation to an insight above:
• Move marketing spend toward the lead sources with the best margins, not just the
most orders. Volume and profit aren’t the same thing.
• Dig into the slow branches. Delays at [branch] are probably costing repeat business.
• Revisit pricing and discounts on the lowest-margin makes.
• Fix the data at the point of capture. Making currency and Order ID mandatory fields
would have prevented most of what I found — remember that 75% of rows needed
correcting.
what i took away from it
Cleaning ate up most of the time, and that’s exactly where the real analytical decisions got
made. The assumptions I logged turned out to matter as much as any chart.
Checking my own work paid off. Validating the converted values and the filled-in blanks
caught errors that would otherwise have shipped without a sound.
I kept quality visible by holding on to an issues flag, so anyone can filter the report down to
clean records and judge for themselves.
And the big one: missing is not zero. Treat a blank cost or a blank revenue as 0 and you’ve silently poisoned every average built on top of it.
If I did this again, I’d fix the capture process first. Clean inputs mean far less cleaning later.

Conclusion

This project took the JCars Logistics dataset from a raw, inconsistent Excel file to an interactive Power BI report that management can use to make decisions. The process started with investigating and cleaning the data: fixing data types, handling missing values and duplicates, and standardizing text fields. I then shaped it into a star schema, with one facts table linked to the customer, date, location, sales rep and vehicle dimensions. That model made the DAX measures (revenue, gross profit, margin, logistics cost ratio, return rate and time comparisons) accurate and easy to reuse across every visual.

Assignment in power BI

Dorcas Chebet — Tue, 22 Sep 2026 09:16:17 +0000

Data modelling in power Bi

Data modelling is setting up tables, relationships, calculations and access for analysis scenarios.This helps so that data can be analyzed correctly and efficiently.
for example, a sales department can have information about,

-Order ID

-Order date

-Customer Name

-City

-Product

-Category

-Amount

instead of putting everything into one large table ,the data can be organized into related tables.
importance of a well designed model
1.Reporting and analytics
it helps in data consistency by standardizing definitions and metrics across different systems so everyone uses the same number.it ensures filters and slicers automatically propagate across related visuals without missing or duplicating records.
2.DAX calculations
3.It prevents incorrect results when writting time-intelligence or cross table measures.
4.it also simplifies code syntax.
Performance
5.A well-designed data model reduce unnecessary data duplication and improve power BI's ability to process queries efficiently.
6.Scalability.It accommodate additional products, customers transactions and other data without the need of the entire structure to be redesigned.
7.maintability.It makes it easier for another analyst to understand and modify the report.

Data modelling approaches

1.Flat table
it is a single ,wide table that contains all your data,e.g Order ID,Order date,Customer name,City,Product category and amount
Example

2.star schema
Definition: One central fact table surrounded by dimension tables, each joined directly to the fact table. It looks like a star.
Example,
dim_doctors
|
|
dim_patients --- Visits_fact --- dim_department
|
|
dim_diagnosis
|
dim_procedure
|
dim_wards
Advantages
• Simple, intuitive and the layout Power BI is optimized for.
• Fast: one hop from dimension to fact and efficient compression.
• Easy DAX and easy filtering.
• Easy for report authors to understand.
Advantages
• Dimensions are denormalised, so some repetition remains (for example, category name repeated per product).
• Requires upfront design and transformation effort.
When appropriate: Almost all analytical models. It is Microsoft's recommended default.
Performance/complexity: Best balance. Few relationships, short filter paths, low comp

3.Snowflake schema approach
It is a star schema where dimensions are normalised into further related tables, so dimensions have their own sub-dimensions.

advantages
• Less redundancy, so easier to maintain hierarchies (Category → Product).
• Mirrors normalised source databases.

Disadvantages

Easy to set up, with no need to handle any connections or relationships.
Fine for quick, one-off exploration.

Disadvantages

-Heavy repetition of words many times, which makes the files bigger and the system slower.
-Updating anomalies: when you rename a category, you have to change many rows.
-It's difficult to create accurate time-based analysis without having a proper date table.
Comparison
1.Feature flat table star schema and snowflake schema
Tables can have one fact along with several dimensions, or they can include a fact along with dimensions and sub-dimensions.
Redundancy High Moderate Low
Performance in Power BI is poor when handling large data sets, but it's best when dealing with smaller data. It’s good, but slightly slower than the best.
Model complexity Lowest Low Highest
DAX simplicity Awkward Easy Moderate
Recommended? Only small or ad hoc, yes (default), only when justified.
2.Fact table and Dimension Table

A fact table keeps track of business events along with the numerical outcomes of those events. It typically contains:

Foreign keys to dimensions (DateKey, ProductKey, CustomerKey)
Numeric measures: Quantity, SalesAmount, Discount, Cost
Sometimes degenerate dimensions such as an OrderNumber

Fact tables are long because they have many rows, but they are not wide since they only have a few columns. Examples: FactSales, FactOrders, FactTransactions.
Dimension tables

A dimension table holds descriptive information that helps in filtering, grouping, and labeling the facts. It has a special key called the primary key and also includes text and category columns. Dimension tables are not very long, having fewer rows, but they have many columns that provide detailed descriptions.

DimCustomer: CustomerKey, Name, Gender, Segment, Join Date
DimProduct: ProductKey, Product Name, Category, Brand, Unit Price.
DimDate: DateKey, Date, Month, Quarter, Year, Weekday
DimLocation: LocationKey, City, County, Country
Measures versus attributes
Measures are numerical outcomes from business events that you combine by adding, averaging, or counting.
Attributes are descriptive fields that you can use to break down or analyze your data, such as Category, City, or Month.

Grain
The grain refers to what is represented by one row in the fact table. Examples:

"One row for each product in each order line" (FactSales).
"One row per order" (FactOrders)
One entry for each account every day, which is a daily balance summary.

Define the grain first. Every measure and foreign key has to be accurate at that level of detail. Combining different types of grain values (such as order-level and line-level) in the same table leads to counting the same items twice.
FactSales

| Sales ID |Date key |Customer key | Product key |Location key | Quantity |Sales amount|
|---------|-----------|-------------|------------|--------------|
|1 |20260105 | 11 |501 |3 |

|1 |65000
|2 |20260105 |11 |502 |3 |
|2 |3000
|3 |20260106 |12 |501 |4 |
|1 |65000 |

Each foreign key refers to a dimension where the key is unique. Choosing "Mombasa" in the DimLocation or "Tech" in the DimProduct narrows down the FactSales data to only the rows that match those selections. This is the star schema presented in Section 1.

Relationships in Power BI What is a relationship?

A relationship connects two tables by using a column in each, which helps Power BI understand how they are linked and allows it to pass filters from one table to the other. Without relationships, data from different tables can't be connected: if you select "Nairobi" in a slicer, it won't change the sales totals because Power BI won't know which rows of data belong to Nairobi.

Keys, uniqueness and integrity

Primary key (PK): A column that uniquely identifies every row in a table, like CustomerKey in DimCustomer.
A foreign key is a column in one table that points to the primary key of another table, like the CustomerKey in the FactSales table.
Unique values: The "one" side of a relationship should have distinct, non-empty values.
Cardinality refers to the type of relationship between columns, such as one-to-many or other similar types.
Referential integrity means that every value in a foreign key of the fact table must be found in the corresponding dimension table. If FactSales includes CustomerKey 99 but DimCustomer does not, those rows appear under a (Blank) category in visuals, making reports misleading.

Why is CustomerID unique in DimCustomer but appears multiple times in FactSales: DimCustomer has one row for each customer, meaning each person is represented only once. FactSales records one entry for each time a customer makes a purchase, and a customer can make multiple purchases. CustomerID 11 is listed once in the DimCustomer table but appears many times in the FactSales table. That is exactly a one-to-many relationship.

_One-to-many (1:*)

How it works: A single row on the "one" side connects to multiple rows on the "many" side. Filters move from the single side to the multiple side.

Example: DimProduct (1) → FactSales (*).

DimProduct (1) ─────────────► (*) FactSales
ProductKey PK ProductKey FK

Use it: This is the standard, default relationship in star schemas. Use it whenever a dimension describes a fact.
Avoid it: When the "one" column has duplicate entries. Power BI will then force a many-to-many.

One-to-one (1:1)

How it works: Each row in one table can match only one row in the other table, and both columns are unique. Filters propagate in both directions.

Employees (EmployeeID) and EmployeeSensitiveDetails (EmployeeID), such as salary or national ID, are kept in a separate table for security.

Employees (1) ◄────────────► (1) EmployeeDetails
EmployeeID PK EmployeeID PK

Use it: Occasionally, such as when dividing a table for security reasons or when two sources refer to the same entity.
Usually, it's better to combine the two tables into one using Power Query, which makes the model easier to work with.

Many-to-many (:)

How it works: Neither column is unique, so there may be multiple rows that match on each side. Power BI doesn't require a unique key, but the results can vary based on how filters work and may be difficult to predict.

Students and Courses, where a student enrolls in multiple courses and a course is attended by multiple students. Another example is budget data at the category level connected to sales data at the product level.

Students () ◄────────► () Enrollments?

The recommended design is a bridge table:

DimStudent has a one-to-many relationship with BridgeEnrollment, which in turn has a one-to-many relationship with DimCourse.

Use it: Only when a bridge table isn't practical, or when you need to compare tables that have different levels of detail, like budget data grouped by month versus sales data grouped by day.
Avoid it: As a shortcut to skip cleaning your data. It causes ambiguity and unexpected totals.

Active and inactive relationships

At any given time, only one active connection can exist between two tables, and this is shown as a solid line in the Model view. Additional ones are inactive (dotted).

FactSales has OrderDate and ShipDate, both connecting to DimDate[Date]. OrderDate is the active relationship. To calculate sales by ship date, turn on the other one in DAX:

DAX
Sales by Ship Date =
CALCULATE (
SUM ( FactSales[SalesAmount] ),
USERELATIONSHIP ( FactSales[ShipDate], DimDate[Date] )
)

Filter Direction How filters propagate

Filters travel along relationships. In a standard star schema, when you apply a filter to a dimension, that filter is passed down to the fact table.

Single-direction filtering

Filters only move from the "one" side to the "many" side.
A user chooses the category "Tech" in the DimProduct slicer.

DimProduct is filtered to FactSales, and the Total Sales shows only Tech sales.
The slicer filters DimProduct to Tech products.
That filters FactSales to include only the rows where the ProductKey matches the specified products.
SUM(FactSales[SalesAmount]) returns Tech sales only.

This is the default and preferred behaviour.

Both (bidirectional) filtering

Filters work both ways: a filter applied to the fact table also affects the dimension.

DimProduct ◄──filter──► FactSales

Legitimate use: For instance, a slicer on DimProduct that displays only products with actual sales, or many-to-many relationships handled through a bridge table.

How to use it carefully
Ambiguous filter paths: When multiple dimensions are linked to a single fact table, bidirectional filters can lead to different paths connecting two tables. Power BI might not allow the relationship to be activated or could cause unexpected outcomes.
Unexpected results happen when filters affect the entire model, meaning a slicer applied to one dimension can also filter another.
Performance cost: Using filters can make calculations slower because they require more processing work.
The model becomes more difficult to understand and fix.

Performance/complexity: Lowest modelling complexity but poor scalability. Memory usage increases rapidly when text columns are repeated.

Star schema

Definition: There is one main fact table with dimension tables around it, and each dimension table is connected directly to the fact table. It looks like a star.

Best practice is to keep everything set to one direction by default, and only turn on two-way filtering when it's actually needed. Often the same outcome can be reached more safely in DAX by using CROSSFILTER() within a single measure.

Joins in Power Query A join combines rows from two tables by matching them on a common column. In Power Query, you perform this action by going to the Home tab and selecting Merge Queries, or choosing Merge Queries as New. You pick two tables, choose the matching column in each one, select the type of join you want, and then expand the joined table to include its columns. Example data

Customers table
|Customer ID| Name |City
|
|-----------|---------|-----
-|C1 | Brian |Nairobi
|
|C2 | Amos | Mombasa

|C3 |Dorcas |Kisumu

|C4 |Cynthia |Nakuru

Orders

OrderID CustomerID Amount
O101 C1 5,000
O102 C1 2,500
O103 C3 8,000
O104 C5 1,200

Note: Brian (C2) and David (C4) have no orders, and order O104 belongs to C5, who is not in Customers. Merges always start by using Customers as the first table on the left and Orders as the second table on the right, pairing them based on the CustomerID.

Left Outer Join

How it works: It keeps all the rows from the left table and only includes the matching rows from the right table.
Records kept: All customers and corresponding order information are retained. Unmatched customers get nulls.

Right Outer Join

How it works: It keeps all the rows from the right table and only the matching rows from the left.
Records kept: Every order along with the corresponding customer information.

CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C3 Cynthia Kisumu O103 8,000
null null null O104 1,200

Use case: Display all orders and mark those that don't have a corresponding customer record.

Full Outer Join

How it works: It keeps all the rows from both tables and matches them where possible.
Records are kept: all information is stored; missing data is represented by nulls where there is no matching information.

CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C2 Brian Mombasa null null
C3 Cynthia Kisumu O103 8,000
C4 David Nakuru null null
null null null O104 1,200

Use case: Combining two systems to identify all the matches and differences in a single outcome.
Inner Join

How it works: It keeps only the rows that are the same in both tables.
Records kept: Only customers who have placed orders, and orders that are linked to customers.

CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C3 Cynthia Kisumu O103 8,000

Use case: "Analyze customers who are currently active and have made a purchase."

Left Anti Join

How it works: It keeps the rows from the left table that do not have a matching row in the right table.
Records retained: Customers with no orders.

CustomerID Name City
C2 Brian Mombasa
C4 David Nakuru

Use case: Identifying customers who have not placed any orders yet in order to run a marketing campaign.

Right Anti Join

How it works: It keeps the rows from the right table that do not have a matching row in the left table.
Records retained: Orders with no matching customer.

OrderID CustomerID Amount
O104 C5 1,200

Use case: Identifying records that are no longer linked to any other data, which is a data quality check to ensure that all references between data sets are correct and complete.

Summary
Join type Keeps
Left Outer All left + matching right
Right Outer All right + matching left
Full Outer Everything from both
Inner Matching rows only
Left Anti Left rows with no match
Right Anti Right rows with no match

Power Query Joins vs Power BI Relationships

Both link tables together, but they function in different steps and serve different reasons.

Aspect Power Query join (Merge) Power BI relationship
When it happens, it can occur during data loading or refreshing (ETL stage) or when a report or query is run.
What it does is physically combines tables into a new table or adds columns. It keeps tables separate and links them logically.
Result: One wider table (rows may multiply or shrink) Filters propagate between tables; no data merged.
Join types include six kinds such as left, inner, anti, and others. Cardinality includes ratios like 1 to many, 1 to 1, and so on. Filter direction is also considered.
Impact on model size can lead to larger size and extra copies. It is efficient because each entity is stored only once.
Aggregation values are set at the row level. Dynamic: measures update based on the filter context.
Best for data preparation: adding more information, fixing errors, making data easier to use, handling complex structures, and finding records that don't match. Analytical modeling: breaking down data into different categories to analyze it effectively.

Rule of thumb:

Use Power Query joins to clean and structure your data: simplify a snowflaked dimension, add more information to a table using a lookup column, or find rows that don’t have matches with an anti join.
Use relationships to analyze data by linking a fact table to dimensions, allowing measures to react to slicers and filters.

Merge the DimProduct and DimCategory tables in Power Query to create a single DimProduct table, which flattens the snowflake schema. Link the DimProduct table to the FactSales table using a one-to-many relationship. Combining FactSales with all dimensions into a single flat table is possible, but it reduces the performance, flexibility, and ease of maintenance that a star schema provides.

Conclusion

A good Power BI model keeps facts like events and measurements separate from dimensions that provide descriptive details. It connects them with the right one-to-many relationships and uses filters that only go in one direction by default. The star schema is the preferred approach since it is efficient, straightforward, and works well with DAX. Power Query joins help organize the data and also show any issues with data quality by using anti joins, while relationships allow the final model to answer business questions in a flexible way.

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

Dorcas Chebet — Wed, 09 Sep 2026 11:06:44 +0000

Introduction

The case study is about jumia products which has generated large amount of data that include a certain number of products, prices, discounts, ratings and customer review .It contains information on 113 products .It also include their current prices, old prices, discount percentage s,number of customer review and ratings .Informations help the business to know the product performance and identify areas that requires attention.
The project involves transforming raw data into a structured dataset ,performing explolatory analysis and presenting the results through an interactive dashboard.

Project Objective.

The main project objective of this project is to analyze data from jumia.
the specific objectives are to analyze products by comparing current prices with the old prices.

-To identify the discount offered in different products

To identify products with high,medium and low discounts

To determine whether highly rated products receive more customer reviews

-To calculate descriptive statistics such as average,current price,average old price,average discount,total discount,total reviews and number of products.

which project generated more reviews

To use excel formulas to organize, categorise and interpret the Jumia product data.

Dataset and Business Queastions

The questions guiding the analysis were:

Does a higher discount correspond to more reviews?

2.Do highly rated products receive more reviews?

3.Is there a relationship between current price and ratings?

4.Which products have the highest rating?

5.Which products have the lowest discounts?

6.Which products have the highest number of reviews?

7.Are there products with high discount but relatively weak ratings or engagements?
The questions helped determine which calculations,pivot tables and visualization were needed.

Initial Data-Quality Audit

Before cleaning i identified the problems before making the changes or cleaning the dataset inorder to understand whether the data was complete ,consistent ,accurate and suitable for analysis.
For the jumia project the audit included:

1.Identifying if there was duplicate
2.Identify if there was missing values in important field such as price ,rating ,discount or reviews.
3.incorrect values :values that do not add up, such as current price being the same as the old price while a discount is still recorded.
4.Data type errors :to ensure prices are stored as numbers ,discounts as percentages/numbers, ratings as numbers and products names as text.

Excel techniques ,formulas and analysis performed.

Calculating the Discount amount

I calculated the discount amount by subtracting the current price from the old price.
formula:
=old price-current price
use of IF statements to classify products into different categories based on their ratings, discounts and prices.
I classified into categories such as:

Excellent
Average
Poor
Missing
Discounts were categorized as:
High discount
Medium discount
Low discount

Analysis performed

Rating analysis I analyzed and group product ratings into categories to determine which products have excellent, average, poor or missing ratings to help evaluate overall customer satisfaction.
Discount analysis I analyzed and categorized into high, medium and low discounts .I also compared the old prices with the current price to determine the amount customers could save.
Trend analysis The project was used to examine relationships between: -Discount percentage and number of customer review -Product rating and number of customer reviews -Product price and product rating. The analysis indicates that a higher discount percentage did not result in more customer reviews and highly rated products did not necessarily receive more reviews. #Dashboard creation process The final stage of the project was to transform the analysis into an interactive dashboard .Top most of the Dashboard was the heading written in bold. The key performance indicators were

Total products
Average Current price KSh 1,097
Average Discount 48%
Average rating 5 -Total reviews 723 Beneath this KPIs the slicers used were:

price category-it categorized data into High price ,Low price and Medium price. Discount category- it categorized data into High discount ,Low discount and medium discount. Rating Category-Categorized data into average, excellent ,poor and missing for missing values.

key insights and business recommendation.

1.Higher discounts do not necessarily lead to higher customer engagement .Products with higher discount percentages doo not consistently receive more customer review.
2.Customer reviews vary across products
3.Discounting should not be the only strategy
4.Products performance should consider both ratings and reviews.
5.Highly rated products do not necessarily receive more reviews.it does not guarantee strong customer engagement.

Recommendations

The sellers should focus on customer satisfaction by improving quality and customer service to maintain good ratings.
the sellers should use data to make pricing decisions
Review products with low engagement.
Use target discounts .Strategically on products where they are likely to improve sales or engagement.

Screenshot of original data, cleaned data ,pivot tables and dashboards

The Original Data

2.Cleaned data

3.Pivot tables

3.Dashboard

ASSIGNMENT WEEK 2

Dorcas Chebet — Sun, 30 Aug 2026 07:49:35 +0000

Getting Started with Excel For Data Analysis

Introduction

Excel is one of the tools commonly used for working data .It is used to enter, organize ,clean, calculate and analyze information using spreadsheets .learning excel gives good foundation on how data is handled before other advanced tools such as python ,SQL and Power BI are used.
In data analysis ,raw data can have errors, duplicates, missing values or unorganized information.

Excel Basics

Dataset- It is a collection of related information organized into rows and columns .Example,

Employee ID	Name	Department	Age	Salary
1102	Marion	Finance	28	45000
1103	Peter	IT	30	70000
1104	June	HR	34	60000
1105	Irene	Finance	32	80000
1106	Dennis	IT	29	65000

Explanation
_column-is a verticle line of data that runs straight from top to bottom
_row-it is a horizontal line of data that runs from left to right.
_cell-it is an intersection of a row and column
_Header-The name of a column.
entering data into Excel
for the case of our dataset, it is entered as,
A1:Employees ID
B2:Name
C1:Department
D1:Age
E1:Salary
It is important to organize data rather than putting two different pieces of information in the same column. For example:
Employee ID
1102,Marion,finance
instead;
Employee ID Name Department
1102 Marion Finance
Hence the data will be easier to analyze.

Formatting in Excel

Formatting in excel involves,

Bold headings
Number of formatting
Alignment
Adjusting column width
Dates
Borders
Percentages
Currency
formatting makes easy to interpret data but it doesn't clean the data.

Formulas applied in Excel.

A formula directs on how to perform a calculation .For example when you enter
=60+85
Excel calculates as 145
To calculate average salary the formula is
=AVERAGE(range criteria)
=AVERAGE(D2:D5)
To calculate the highest salary
=MAX(D2:D5)
To calculate the lowest salary
=MIN(D1:D5)
To count the number of employees
=COUNTA(A1:A5)

Sorting

sorting is organizing data in a particular order.
Example :Employees can be sort according to their departments,
Names Departments
Marion Finance
Peter IT
June HR
Irene Finance
Dennis IT
This sorted data enables the analyst to quickly identify an employee which department the employees comes from.

Filtering of data

Filtering hides data that does not meet a particular condition.
For example, since our dataset contains employees from different departments, we can filter the column to only show employees in IT departments ,filtering does not delete the other records .It only hides since it does not match the selected condition.

Data cleaning

It is the process of finding and correcting errors in a dataset.
Common errors in a dataset involves,
Missing values
Duplicate values
Inconsistent information
In data cleaning ,excel functions are:
UPPER
=UPPER(A2) It helps changes the text to uppercase
LOWER
=lOWER it help changes the text to lowercase
TRIM
=TRIM(A2) It help removes unnecessary spaces from text.

Preparation of data for analysis.

It is the process of cleaning, organizing raw data to be accurately ready for analysist avoid misleading results.
The analyst should ensure that the dataset is well structured, complete ,consistent ,accurate and identify missing values.

GitHub Repository

You can find my Excel data analysis project on GitHub:

[view my GitHub Repository

ASSIGNMENT WEEK 1

Dorcas Chebet — Sun, 23 Aug 2026 11:37:33 +0000

Understanding the Git workflow.

Introduction

Git is a system that is used incase one needs to track their projects or check if there was changes or need to get back to the initial project.
Git is important since it simplify the work of re-writting the project over again by keeping track on the initial project through tracking the changes in the file or checking on what has changed or under certain changes.
A complete work flow involves,

working directory
staging area
commit
push

working directory.

It is a folder in your laptop or computer where you work on your project .Working directory is where you:
create files
edit files
delete files
add data and make changes to your project.
The commands that are used in the working directory include,
pwd-it shows your current location
ls_it is used for listing files and a folder.
cd folder-it moves into a folder.
cd..-it is used if one needs to move back to the previous folder.
mkdir-it creates a new file folder.
touch file-wit creates a new empty folder.
git status-command used in tracking a folder.
git add-moves changes to staging area.

staging area.

It is where you put the changes you have chosen to save in your next commit in git.

The commands that are used in staging area:

git add- stage all the changes
git status- check what is staged
git diff--staged -sees what is exactly staged
git restore--staged-it removes a file from staging
git commit -m -it ensure you run git add before committing

Commit

It is a back up of the changes that have been placed in the staging area.
Good commit messages can tell what happened.

Push

Git push is a command that takes the commits made in a computer and send them to a repository such as Github.It is the final stage in working directory that sends the project to remote or local repository.

assignment

Dorcas Chebet — Thu, 20 Aug 2026 10:15:21 +0000

assinment week 1 article submission

DEV Community: Dorcas Chebet

SQL FOR ANALYTICS:WINDOW FUNCTIONS,JOINS,CTEs AND SUBQUERIES

Introduction

SQL Window Functions

Offset functions: LAG and LEAD

FIRST_VALUE and LAST_VALUE

Window functions vs GROUP BY, and a few rules

SQL Joins

INNER JOIN

LEFT JOIN (LEFT OUTER JOIN)

RIGHT JOIN (RIGHT OUTER JOIN)

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

Common Table Expressions (CTEs)

Basic CTE

Multiple CTEs

Common Table Expressions with joins, aggregations, and window functions.

Recursive CTEs

CTEs vs subqueries

Recursive CTEs

Scalar subqueries

Non-correlated (independent) subqueries

Correlated subqueries

Nested subqueries

Subqueries with IN, EXISTS, ANY and ALL

Putting It All Together: A Real-World Hospital Question

Building the JCars logistics Sale Dashboard in Power BI

Introduction

Investigating the data before touching it

Cleaning and preparation:

Steps What I did Why, and what I assumed.

Modelling the data

The DAX behind the numbers

Designing the report

Insights

Recommendations

Conclusion

Assignment in power BI

Data modelling in power Bi

Data modelling approaches

Keys, uniqueness and integrity

Conclusion

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

Introduction

Project Objective.

Dataset and Business Queastions

Initial Data-Quality Audit

Excel techniques ,formulas and analysis performed.

Calculating the Discount amount

Analysis performed

ASSIGNMENT WEEK 2

Getting Started with Excel For Data Analysis

Introduction

Excel Basics

Formatting in Excel

Formulas applied in Excel.

Sorting

Filtering of data

Data cleaning

Preparation of data for analysis.

GitHub Repository

ASSIGNMENT WEEK 1

Understanding the Git workflow.

Introduction

working directory.

staging area.

The commands that are used in staging area:

Commit

Push

assignment