DEV Community: PRUDENCE KORIR

Mastering Intermediate & Advanced SQL: A Practical Healthcare Analytics Guide

PRUDENCE KORIR — Mon, 19 Oct 2026 05:11:18 +0000

In clinical operations and healthcare administration, raw relational tables alone cannot answer complex analytical questions like tracking readmission trends, analyzing department staffing hierarchies, or auditing medication billing variances.

This guide demonstrates how to apply Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries using a standard hospital database schema.

We explore four foundational SQL areas:

Window Functions
SQL Joins
Common Table Expressions (CTEs)
SQL Subqueries

Dataset Schema Reference: `kenyan_hospital_health_records`

All practical queries in this article target the table below, representing real clinical visits, billing records, and attending personnel:

Column Name	SQL Type	Description
`patient_id`	`INT PRIMARY KEY`	Unique 4-digit identifier for the patient encounter.
`first_name`	`VARCHAR(50)`	Patient's given name.
`last_name`	`VARCHAR(50)`	Patient's surname.
`gender`	`VARCHAR(10)`	Biological sex (`Female`, `Male`).
`age`	`INT`	Patient age in years (1 to 85).
`phone_number`	`BIGINT`	Telephone contact formatted as Kenyan country code `254...`.
`date_of_visit`	`DATE`	Date of registration/admission (2020-01-01 to 2025-04-03).
`diagnosis`	`VARCHAR(50)`	Clinical condition (e.g., `Asthma`, `Diabetes`, `Hypertension`, `Typhoid`).
`treatment_plan`	`VARCHAR(50)`	Protocol prescribed (e.g., `Admission`, `Surgery`, `Medication`).
`doctor_name`	`VARCHAR(100)`	Attending physician (e.g., `Dr. Kiplagat`, `Dr. Atieno`, `Dr. Mutua`).
`hospital_department`	`VARCHAR(50)`	Clinical unit (`Cardiology`, `Oncology`, `Pediatrics`, `Neurology`, etc.).
`insurance_provider`	`VARCHAR(50)`	Insurer (`NHIF`, `Jubilee`, `AAR Healthcare`, `Britam`, `APA Insurance`, or `NULL`).
`bill_amount_kes`	`NUMERIC(10,2)`	Incurred medical charges in Kenyan Shillings.
`payment_status`	`VARCHAR(20)`	Settlement status (`Paid`, `Partially Paid`, `Pending`).

1. SQL Window Functions

Concept Overview

A window function performs calculations across a set of table rows that are related to the current query row. Unlike standard GROUP BY aggregations—which collapse multiple rows into a single summary row—window functions preserve individual row identities while displaying aggregate or relative metrics alongside them.

Window Mechanics: `OVER()`, `PARTITION BY`, `ORDER BY`, and Frames

Every window function uses the OVER() clause:

FUNCTION(...) OVER (
    PARTITION BY partition_column
    ORDER BY sort_column
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

OVER(): Signals to the database engine that the preceding function operates over an analytical window rather than a collapsed group. An empty OVER() treats the entire result set as one single partition.
PARTITION BY: Divides the rows into logical subsets or "partitions" (e.g., by hospital_department or doctor_name). The calculation runs independently within each subset and resets at partition boundaries.
ORDER BY: Dictates the sequence in which rows are processed and evaluated inside each individual partition.
Window Frame (ROWS / RANGE BETWEEN ...): Defines the physical or logical boundary of rows relative to the current row included in the computation.
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: From the start of the partition up to the active row (standard for running totals).
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: A sliding 3-row moving window (the previous row, current row, and next row).

Ranking Functions: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, and `NTILE()`

Ranking functions assign an ordered integer to each row within a partition.

ROW_NUMBER(): Generates a strict, non-repeating sequence (1, 2, 3, 4,) for each row in a partition. If two rows have identical values in the sort column, they are assigned consecutive numbers arbitrarily.
RANK(): Assigns sequential ranks but leaves gaps after ties (e.g., 1, 2, 2, 4).
DENSE_RANK(): Assigns sequential ranks without gaps after ties (e.g., 1, 2, 2, 3).
NTILE(n): Divides an ordered partition as evenly as possible into n ranked buckets (e.g., quartiles where n=4).

Practical Query: Department Billing Tier Audit

SELECT 
    patient_id,
    hospital_department,
    first_name || ' ' || last_name AS patient_name,
    bill_amount_kes,
    ROW_NUMBER() OVER (
        PARTITION BY hospital_department 
        ORDER BY bill_amount_kes DESC
    ) AS row_num,
    RANK() OVER (
        PARTITION BY hospital_department 
        ORDER BY bill_amount_kes DESC
    ) AS rnk,
    DENSE_RANK() OVER (
        PARTITION BY hospital_department 
        ORDER BY bill_amount_kes DESC
    ) AS dense_rnk,
    NTILE(4) OVER (
        PARTITION BY hospital_department 
        ORDER BY bill_amount_kes DESC
    ) AS cost_quartile
FROM kenyan_hospital_health_records;

What the Query Does: Partitions all 672 records by hospital_department and sorts them from highest to lowest bill amount. It tags each record with its row position, ranking, dense ranking, and cost quartile.
Expected Result: In Cardiology, Patient 9656 (KES 49,732.40) and Patient 7362 (KES 49,720.43) receive ranks 1 and 2. Patients falling into cost_quartile = 1 represent the top 25 most expensive cases in that specific department, enabling targeted insurance audits.

Value Functions: `LAG()`, `LEAD()`, `FIRST_VALUE()`, and `LAST_VALUE()`

Value functions allow rows to read values from neighboring rows without requiring complex self-joins.

LAG(col, offset): Reads data from n rows behind the current row within the partition.
LEAD(col, offset): Reads data from n rows ahead of the current row within the partition.
FIRST_VALUE(col): Returns the value of the specified column from the very first row in the window frame.
LAST_VALUE(col): Returns the value from the final row in the window frame. (Note: Must be paired with ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING or ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to prevent the default frame from terminating at the current row).

Practical Query: Tracking Chronological Doctor Encounters and Cost Variances

SELECT 
    patient_id,
    doctor_name,
    date_of_visit,
    bill_amount_kes,
    LAG(bill_amount_kes, 1) OVER (
        PARTITION BY doctor_name 
        ORDER BY date_of_visit ASC
    ) AS previous_patient_bill,
    LEAD(bill_amount_kes, 1) OVER (
        PARTITION BY doctor_name 
        ORDER BY date_of_visit ASC
    ) AS subsequent_patient_bill,
    FIRST_VALUE(bill_amount_kes) OVER (
        PARTITION BY doctor_name 
        ORDER BY date_of_visit ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_recorded_bill
FROM kenyan_hospital_health_records
WHERE doctor_name = 'Dr. Kiplagat';

What the Query Does: Orders Dr. Kiplagat's 52 consultations chronologically. It pulls the bill from the preceding patient visit and the bill from the subsequent visit onto the active record.
Expected Result: For Dr. Kiplagat's first visit on 2020-02-28 (Patient 5098), previous_patient_bill displays NULL. The subsequent visit on 2020-06-19 displays KES 26,535.91 in previous_patient_bill and KES 31,284.09 in subsequent_patient_bill, allowing revenue analysts to identify fluctuations between sequential encounters.

Aggregate Window Functions: `SUM()`, `AVG()`, `COUNT()`, `MIN()`, and `MAX()`

When combined with an OVER() clause, standard aggregate functions produce running totals, departmental moving averages, and cohort extremes directly alongside individual patient rows.

Practical Query: Running Revenue and Department Benchmarking

SELECT 
    patient_id,
    hospital_department,
    date_of_visit,
    bill_amount_kes,
    -- Running revenue total across time within the department
    SUM(bill_amount_kes) OVER (
        PARTITION BY hospital_department 
        ORDER BY date_of_visit ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_dept_revenue,
    -- Department average benchmark
    ROUND(AVG(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ), 2) AS dept_average_bill,
    -- Department maximum charge
    MAX(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS dept_max_bill,
    -- Department minimum charge
    MIN(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS dept_min_bill,
    -- Cumulative count of patients treated
    COUNT(patient_id) OVER (
        PARTITION BY hospital_department 
        ORDER BY date_of_visit ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_patient_count
FROM kenyan_hospital_health_records;

What the Query Does: Retains every encounter while calculating a cumulative running total of revenue and patient throughput over time, juxtaposed against the overall department average, floor, and ceiling.
Expected Result: A finance dashboard showing how each incoming patient increments total department cash flow, alongside an immediate visual check of whether an individual bill exceeded dept_average_bill.

2. SQL Joins

Concept Overview

Joins combine columns from two or more tables or subquery projections based on logical relationships between shared keys. In healthcare databases, choosing the appropriate join type guarantees data integrity and prevents dropping clinical records or introducing duplicate entries.

INNER JOIN       LEFT JOIN        RIGHT JOIN       FULL OUTER JOIN
  [ A ∩ B ]        [ A + (A ∩ B) ]  [ (A ∩ B) + B ]  [ A ∪ B ]

Join Types & Healthcare Applications

Join Type	Mathematical Set Behavior	Healthcare Use Case
`INNER JOIN`	Returns only records where the join predicate matches in both tables.	Linking patient consultations strictly to verified hospital departments or active payment records.
`LEFT JOIN`	Returns all records from the left table, and matched records from the right table (unmatched right rows become `NULL`).	Detecting departments or insurance providers with zero recorded claims.
`RIGHT JOIN`	Returns all records from the right table, and matched records from the left table (unmatched left rows become `NULL`).	Auditing master clinical catalogs against active patient utilization.
`FULL OUTER JOIN`	Returns all records when there is a match in either the left or right table. Unmatched attributes on either side display `NULL`.	Reconciling patient registers during hospital mergers or clinic branch synchronizations.
`CROSS JOIN`	Computes the Cartesian product; pairs every row from Table A with every row from Table B .	Constructing full clinical scheduling grids (e.g., Doctors ,times Departments ,times On-Call Shifts).
`SELF JOIN`	Joins a table to itself using aliasing (`t1` and `t2`).	Finding clinical co-occurrences (e.g., patients treated in the same department on the same date).

Normalized Staging Tables for Joins

To demonstrate relational joins using the hospital records, we project two normalized dimension tables from the dataset alongside the encounter fact table:

-- Dimension Table 1: Master Hospital Departments
CREATE TEMPORARY TABLE master_departments AS
SELECT DISTINCT hospital_department AS department_name
FROM kenyan_hospital_health_records
UNION ALL
SELECT 'Radiology' AS department_name; -- Deliberately unassigned department for testing outer joins

-- Dimension Table 2: Registered Insurance Payers
CREATE TEMPORARY TABLE master_insurers AS
SELECT DISTINCT insurance_provider AS insurer_name
FROM kenyan_hospital_health_records
WHERE insurance_provider IS NOT NULL
UNION ALL
SELECT 'Madison Insurance' AS insurer_name; -- Deliberately unassigned insurer

Practical Queries: Joins in Action

1. `INNER JOIN`: Matching Verified Insured Encounters

SELECT 
    k.patient_id,
    k.first_name || ' ' || k.last_name AS patient_name,
    k.bill_amount_kes,
    m.insurer_name
FROM kenyan_hospital_health_records k
INNER JOIN master_insurers m 
    ON k.insurance_provider = m.insurer_name;

Explanation: Returns only the 536 records that have an active insurance plan matching the insurer directory. Uninsured/cash patients (NULL) are excluded.

2. `LEFT JOIN`: Identifying Inactive Departments

SELECT 
    md.department_name,
    COUNT(k.patient_id) AS total_encounters,
    COALESCE(SUM(k.bill_amount_kes), 0) AS total_billed_kes
FROM master_departments md
LEFT JOIN kenyan_hospital_health_records k 
    ON md.department_name = k.hospital_department
GROUP BY md.department_name;

Explanation: Evaluates every department in the hospital. Active units (e.g., Cardiology, 104 encounters) display their counts, while the newly introduced Radiology department displays 0 encounters and KES 0.00 billed. An INNER JOIN would have omitted Radiology.

3. `FULL OUTER JOIN`: Complete Operational Ledger Reconciliation

SELECT 
    md.department_name AS catalog_department,
    k.hospital_department AS record_department,
    k.patient_id,
    k.bill_amount_kes
FROM master_departments md
FULL OUTER JOIN kenyan_hospital_health_records k 
    ON md.department_name = k.hospital_department
WHERE k.patient_id IS NULL OR md.department_name IS NULL;

Explanation: Surfaces edge cases on both ends: cataloged departments that treated zero patients, and clinical records tied to unrecognized department labels.

4. `CROSS JOIN`: Generating Coverage Matrices

SELECT 
    d.department_name,
    s.shift_slot
FROM (SELECT DISTINCT hospital_department AS department_name FROM kenyan_hospital_health_records) d
CROSS JOIN (
    VALUES ('Morning (07:00 - 15:00)'), 
           ('Evening (15:00 - 23:00)'), 
           ('Night (23:00 - 07:00)')
) AS s(shift_slot)
ORDER BY d.department_name, s.shift_slot;

Explanation: Generates a 21-row scheduling grid (7 departments ,times 3 duty shifts) to seed hospital on-call rosters.

5. `SELF JOIN`: Cross-Infection Contact Tracing

SELECT 
    a.hospital_department,
    a.date_of_visit,
    a.patient_id AS patient_a_id,
    a.diagnosis AS patient_a_diagnosis,
    b.patient_id AS patient_b_id,
    b.diagnosis AS patient_b_diagnosis
FROM kenyan_hospital_health_records a
JOIN kenyan_hospital_health_records b 
    ON a.hospital_department = b.hospital_department 
   AND a.date_of_visit = b.date_of_visit
   AND a.patient_id < b.patient_id -- Prevents self-matching and duplicate inverted pairs
WHERE a.diagnosis = 'Typhoid' OR b.diagnosis = 'Typhoid';

Explanation: Joins the dataset to itself to detect instances where multiple patients shared a department on the same calendar day with a communicable condition (Typhoid), supporting hospital infection-control audits.

3. Common Table Expressions (CTEs)

Concept Overview

A Common Table Expression (CTE) is a temporary, named result set defined using the WITH keyword that exists only for the duration of the query. CTEs replace deeply nested subqueries with linear, top-down modules.

Basic and Multiple CTEs

Practical Query: Benchmarking Department Collections Against Regional Targets

WITH DepartmentSummary AS (
    -- CTE 1: Compute total billing and patient volume per department
    SELECT 
        hospital_department,
        COUNT(patient_id) AS total_patients,
        SUM(bill_amount_kes) AS department_revenue,
        AVG(bill_amount_kes) AS avg_patient_bill
    FROM kenyan_hospital_health_records
    GROUP BY hospital_department
),
FacilityBenchmark AS (
    -- CTE 2: Calculate facility-wide average department revenue
    SELECT 
        AVG(department_revenue) AS mean_dept_revenue
    FROM DepartmentSummary
)
-- Main Outer Query: Combine CTEs with Joins to highlight high-performing units
SELECT 
    ds.hospital_department,
    ds.total_patients,
    ROUND(ds.department_revenue, 2) AS department_revenue_kes,
    ROUND(ds.avg_patient_bill, 2) AS avg_patient_bill_kes,
    ROUND(ds.department_revenue - fb.mean_dept_revenue, 2) AS revenue_variance_kes
FROM DepartmentSummary ds
CROSS JOIN FacilityBenchmark fb
ORDER BY department_revenue_kes DESC;

What the Query Does: Defines two sequential CTEs (DepartmentSummary and FacilityBenchmark) and references both in the final SELECT block.
Expected Result: A comparative executive summary showing each department's total revenue alongside its variance from the facility-wide department mean text{KES 2,513,809.84).

Recursive CTEs

Recursive CTEs reference themselves in an iterative loop to traverse hierarchical, parent-child, or sequential data until a termination condition is reached.

A recursive CTE consists of:

The Anchor Member: Initial query that sets the base result set.
UNION ALL: Operator combining iterations.
The Recursive Member: Query referencing the CTE name, repeated until it returns an empty set.

Practical Query: Generating a Step-Down Patient Discharge Schedule

WITH RECURSIVE PatientFollowUpSchedule AS (
    -- Anchor Member: Identify critical admitted patients requiring follow-up
    SELECT 
        patient_id,
        first_name || ' ' || last_name AS patient_name,
        date_of_visit AS scheduled_review_date,
        1 AS review_cycle
    FROM kenyan_hospital_health_records
    WHERE treatment_plan = 'Admission' AND diagnosis = 'Diabetes'
      AND date_of_visit >= '2025-01-01'

    UNION ALL

    -- Recursive Member: Project subsequent reviews at 30-day intervals (up to cycle 4)
    SELECT 
        p.patient_id,
        p.patient_name,
        (p.scheduled_review_date + INTERVAL '30 days')::DATE AS scheduled_review_date,
        p.review_cycle + 1
    FROM PatientFollowUpSchedule p
    WHERE p.review_cycle < 4
)
SELECT 
    patient_id,
    patient_name,
    review_cycle,
    scheduled_review_date
FROM PatientFollowUpSchedule
ORDER BY patient_id, review_cycle;

What the Query Does: Uses recursion to project a structured 4-cycle, 120-day outpatient management roadmap for diabetic patients admitted in 2025.
Expected Result: Four sequential rows per candidate patient, stepping forward 30 days per review cycle to automate preventative care scheduling.

CTEs vs. Subqueries

Feature	Common Table Expressions (CTEs)	Subqueries
Execution Flow	Declared upfront; reads top-to-bottom like modular code.	Nested inside-out; can be difficult to read when deeply indented.
Reusability	Defined once in the `WITH` clause and referenced multiple times.	Must be copied and pasted if needed in multiple clauses.
Recursion	Native support via `WITH RECURSIVE`.	Cannot perform recursive operations.
Placement Flexibility	Only declared at the start of a statement (`SELECT`, `INSERT`, `UPDATE`).	Can be embedded anywhere in `SELECT`, `FROM`, `WHERE`, or `HAVING`.

4. SQL Subqueries

Concept Overview

A subquery is a SELECT statement nested inside another SQL statement. Subqueries can execute independently or evaluate iteratively against outer query attributes.

Subquery Classifications

Scalar Subquery: Returns exactly one row and one column (a single atomic value).
Non-Correlated Subquery: Completely self-contained. Executes once; its output is handed off to the outer query.
Correlated Subquery: References columns from the outer query row. Executes once for every candidate row evaluated by the outer query.
Nested Subquery: A subquery enclosed within another subquery.
Multi-Row Set Operators (IN, EXISTS, ANY, ALL):
- IN: Tests if a candidate value matches any value in a list or subquery set.
- EXISTS: Evaluates boolean presence (True/False). Short-circuits immediately upon discovering the first matching record.
- > ANY: Matches if the value is greater than the minimum value returned by the subquery.
- > ALL: Matches only if the value is greater than the maximum value returned by the subquery.

Subquery Structural Placement

1. In the `SELECT` Clause (Scalar Subquery)

SELECT 
    patient_id,
    first_name || ' ' || last_name AS patient_name,
    bill_amount_kes,
    hospital_department,
    ROUND(
        (SELECT AVG(bill_amount_kes) 
         FROM kenyan_hospital_health_records 
         WHERE hospital_department = k.hospital_department), 
        2
    ) AS department_mean_bill,
    ROUND(
        bill_amount_kes - (SELECT AVG(bill_amount_kes) 
                           FROM kenyan_hospital_health_records 
                           WHERE hospital_department = k.hospital_department), 
        2
    ) AS variance_from_dept_mean
FROM kenyan_hospital_health_records k;

Explanation: Evaluates the department average as a scalar metric for each patient, displaying each bill's variance against its departmental mean.

2. In the `FROM` Clause (Derived Table)

SELECT 
    derived.doctor_name,
    derived.total_patients_seen,
    ROUND(derived.total_billed_kes, 2) AS total_billed_kes
FROM (
    SELECT 
        doctor_name,
        COUNT(patient_id) AS total_patients_seen,
        SUM(bill_amount_kes) AS total_billed_kes
    FROM kenyan_hospital_health_records
    GROUP BY doctor_name
) AS derived
WHERE derived.total_patients_seen > 50;

Explanation: Aggregates doctor throughput inside an in-memory virtual table (derived), allowing the outer query to filter for high-volume physicians.

3. In the `WHERE` Clause (`EXISTS` vs. `IN` vs. `ALL`)

-- Query A: Correlated Subquery using EXISTS
-- Identifies doctors who have handled high-complexity surgeries exceeding KES 45,000
SELECT DISTINCT 
    k1.doctor_name
FROM kenyan_hospital_health_records k1
WHERE EXISTS (
    SELECT 1 
    FROM kenyan_hospital_health_records k2
    WHERE k2.doctor_name = k1.doctor_name 
      AND k2.treatment_plan = 'Surgery'
      AND k2.bill_amount_kes > 45000.00
);

-- Query B: Non-Correlated Subquery using IN
-- Finds patients treated in departments that experienced pending bills exceeding KES 49,000
SELECT 
    patient_id,
    first_name || ' ' || last_name AS patient_name,
    hospital_department,
    bill_amount_kes
FROM kenyan_hospital_health_records
WHERE hospital_department IN (
    SELECT DISTINCT hospital_department
    FROM kenyan_hospital_health_records
    WHERE payment_status = 'Pending' 
      AND bill_amount_kes > 49000.00
);

-- Query C: Subquery using > ALL
-- Identifies encounters with bills greater than ALL pediatric encounters
SELECT 
    patient_id,
    first_name || ' ' || last_name AS patient_name,
    hospital_department,
    bill_amount_kes
FROM kenyan_hospital_health_records
WHERE bill_amount_kes > ALL (
    SELECT bill_amount_kes 
    FROM kenyan_hospital_health_records
    WHERE hospital_department = 'Pediatrics'
);

Explanation:
- EXISTS halts processing for that doctor as soon as a single match is found, making it more efficient than scanning every row.
- IN builds a discrete list of departments and checks for membership.
- > ALL finds outlier bills across adult wards that exceed the single highest bill recorded anywhere in Pediatrics (KES 49,855.09).

Comparative Summary: CTEs vs. Window Functions vs. Subqueries

-- Analytical Need: Calculate Department Average Billing and Row Variances

-- Window Function Approach (Most concise, 1 table scan):
SELECT bill_amount_kes, AVG(bill_amount_kes) OVER(PARTITION BY hospital_department) FROM records;

-- CTE Approach (Modular, reusable in subsequent joins):
WITH DeptAvg AS (SELECT hospital_department, AVG(bill_amount_kes) AS avg_b FROM records GROUP BY 1)
SELECT r.bill_amount_kes, d.avg_b FROM records r JOIN DeptAvg d ON r.hospital_department = d.hospital_department;

-- Scalar Correlated Subquery Approach (Repeated execution per row):
SELECT bill_amount_kes, (SELECT AVG(bill_amount_kes) FROM records r2 WHERE r2.dept = r1.dept) FROM records r1;

Conclusion & Practical Engineering Best Practices

Window Functions replace complex self-joins when computing cumulative sums, rankings, or relative row metrics without collapsing the result set.
Inner vs. Outer Joins must be chosen deliberately to avoid dropping unmatched records (such as uninsured cash patients or newly added empty departments).
CTEs make multi-stage transformations easier to read and maintain compared to deeply nested subqueries, and enable tree traversal via recursion.
EXISTS is generally preferable to IN for correlated subqueries over large datasets because it short-circuits as soon as the first match is identified.

End-to-End Power BI Architecture: Transforming JCars Logistics from Dirty Flat Data to Executive Decision Support

PRUDENCE KORIR — Sat, 26 Sep 2026 14:17:21 +0000

Transforming Dirty Automotive Logistics Data into Executive Decision Support in Power BI

Building business intelligence dashboards for messy, real-world data requires far more than dropping charts onto a canvas. It demands rigorous exploratory auditing, robust Power Query ETL pipelines, structured DAX modeling, and executive-level storytelling.

In this project, I engineered an end-to-end Power BI reporting solution for JCars, a multi-branch automotive dealership and logistics network. Here is how I took an uncleaned transactional flat file and transformed it into a decision-support platform tracking KES 1.905B in revenue across 276 customer orders.

1. The Challenge: Uncleaned Flat File Data

The source data (Jcars_data.csv) captured transaction-level vehicle sales, delivery tracking, and financing data. However, the raw data presented major data quality challenges:

Mixed Currencies: Monetary columns contained mixed string prefixes (USD, KES) alongside bare numbers.
Inconsistent Categorical Text: Branch locations, vehicle makes, and delivery statuses suffered from varied casing (toyota, TOYOTA) and split synonyms (Delivered, completed, Done).
Locale-Sensitive Dates: Conflicting DD/MM/YYYY and MM/DD/YYYY formats caused parse errors and nulls.
Data Governance Traps: Dispatched units showing delivery timestamps prior to order placement dates, and cancelled orders retaining "Paid" settlement statuses.

2. Power Query ETL & Data Standardization

To ensure the downstream semantic model remained performative and reliable, all cleaning logic was addressed upfront:

Currency Normalization

All values were standardized to Kenya Shillings (KES). Text extraction logic stripped currency prefixes, applied an exchange rate baseline of 1 USD = 130 KES, and cast columns to native currency data types:

Final KES = Raw Numeric Value * Exchange Rate

Date Engine Resiliency

To resolve locale parsing errors across operating systems, I implemented locale-aware date parsing with conditional fallbacks, preserving chronological continuity across all order and delivery timelines.

Categorical Bucketing

Using DAX and M transformations, over a dozen fragmented delivery labels were mapped into four unified operational states:

Delivered (encompassing "Delivered", "Completed", "Done")
In Transit (encompassing "On the way", "At the yard", "Dispatched")
Cancelled
Returned

3. Dimensional Modeling & DAX Architecture

Rather than computing ad-hoc aggregations inside visual fields, I established a dedicated _Measures table housing normalized DAX logic:

// 1. Top-Line Turnover
Total Orders = DISTINCTCOUNT(Jcars_data[Order ID])
Total Units Sold = SUM(Jcars_data[Units Sold])
Total Revenue = SUMX(Jcars_data, Jcars_data[Units Sold] * Jcars_data[Unit Selling Price])

// 2. Cost & Profitability Mechanics
Total Cost = SUMX(Jcars_data, (Jcars_data[Units Sold] * Jcars_data[Unit Cost]) + Jcars_data[Clean_Logistics_Cost])
Gross Profit = [Total Revenue] - [Total Cost]
Gross Profit Margin = DIVIDE([Gross Profit], [Total Revenue], 0)

// 3. Operational Friction
Return Rate = 
DIVIDE(
    CALCULATE([Total Units Sold], Jcars_data[Clean_Delivery_Status] = "Returned"),
    [Total Units Sold],
    0
)

Cancellation Rate = 
DIVIDE(
    CALCULATE([Total Orders], Jcars_data[Clean_Delivery_Status] = "Cancelled"),
    [Total Orders],
    0
)

Core Benchmark Results:

Total Revenue: KES 1,904,596,846.00

Total Gross Profit: KES 393,327,843.00

Gross Profit Margin: 20.65%

Units Sold: 466 Units across 276 Orders

Return Rate: 31.88%

Cancellation Rate: 13.41%

4. The 3-Page Executive Dashboard

The report was organized into three purpose-built views with persistent button navigation:
1. Executive Overview: 6 high-level KPI cards, revenue ranking by vehicle make, branch turnover distribution, and monthly sales trends.

2. Vehicle Performance: A hierarchical drill-down matrix (Make, Model), vehicle body type share, and a Margin % vs. Return Rate scatter chart with reference quadrants separating star performers from high-risk liabilities.

3. Operations & Logistics: Detailed operational metrics comparing order delivery statuses, branch order volumes, and a clustered column chart evaluating Collected Delivery Fees vs. Actual Logistics Costs.

5. Key Strategic Takeaways

The High-End Return Paradox: While luxury brands like BMW drive healthy unit margins, they exhibit an alarming 55.56% return rate. Recommending mandatory pre-delivery mechanical inspections (PDI) before customer dispatch.

Logistics Subsidy Leakage: Upcountry and high-volume branches frequently incur logistical costs that exceed the flat delivery fees billed to customers. Introducing dynamic, distance-based freight pricing will protect operating margins.

Pre-Delivery Churn: Extended fulfillment delays account for a 13.41% cancellation rate, indicating an urgent need to automate inventory release workflows at regional holding yards.

Conclusion

A successful analytics project connects low-level data transformation directly to executive decision-making. By pairing resilient ETL processes with intentional visual design, this dashboard turns fragmented logistics records into actionable operational clarity.

Making Sense of Power BI: Data Modelling, Relationships, and Joins

PRUDENCE KORIR — Mon, 14 Sep 2026 15:25:41 +0000

When raw data hits Power BI, it’s rarely clean or ready for reporting. Data modeling is the process of structuring and connecting your tables so your reports load fast, your DAX measures calculate accurately, and your visuals actually make sense.

Getting your data model right early saves hours of DAX troubleshooting down the road. Here is a breakdown of the core concepts you need to build robust Power BI reports.

1. Data Modeling Architecture

How you arrange your tables determines how efficient your report will be.

Flat Table Approach: Combining everything into a single, massive spreadsheet-style table.

Pros: Quick to set up for a fast sanity check.

Cons: Tons of redundant data, massive file sizes, and terrible DAX performance.

Star Schema (Best Practice): Placing a central fact table (containing numerical metrics) surrounded by descriptive lookup tables.

Pros: Blazing-fast engine performance, clean DAX, and clear reporting paths.

Cons: Requires upfront work to break down flat tables.

Snowflake Schema: Similar to a Star Schema, but dimension tables branch out into further sub-dimension tables (e.g., a Product table linking to a SubCategory table, which links to a Category table).

Pros: Slightly reduces data storage redundancy.

Cons: Creates a complex model, makes DAX harder to write, and slows down report filtering.

2. Fact Tables vs. Dimension Tables

Building a Star Schema requires dividing your data into two distinct table types:

Fact Tables

What they are: Log files of business events or transaction records.

Contents: Numeric measurements, monetary values, timestamps, and foreign keys.

Examples: FactSales, FactInventoryLogs, FactOrders.

Grain: Defines what a single row represents (e.g., one row per line item on a customer receipt).

Dimension Tables

What they are: Context tables that provide descriptive details for your metrics.

Contents: Text, attributes, names, dates, and categories.

Examples: DimCustomer, DimProduct, DimStore, DimDate.

Example: In an electronics store, FactSales logs that on Friday, CustomerID #402 bought ProductID #88 for $1,200. Power BI uses DimCustomer to see that #402 is Sarah, and DimProduct to look up that #88 is a 4K Monitor.

3. Relationships & Directionality

Relationships serve as the connectors between tables so filters apply across visuals.

One-to-Many (1:*): The standard relationship pattern. One unique ID in a Dimension table connects to multiple entries in a Fact table.

One-to-One (1:1): Rare. Used mainly when splitting large tables for privacy or optimization.

Many-to-Many (:): Complex and risky. Can cause ambiguous filter behavior and inaccurate total calculations.

Key Mechanics:

Keys: Dimension tables use a Primary Key (strictly unique values). Fact tables use a Foreign Key (repeated values).
Active vs. Inactive: Only one active relationship (solid line) can exist between two tables at a time. Alternate dates (like OrderDate vs. ShipDate) require inactive relationships (dotted lines) activated via DAX (USERELATIONSHIP).
Filter Direction: Stick to single-direction filtering (Dimension $\rightarrow$ Fact). Avoid bi-directional filtering unless absolutely necessary, as it introduces circular paths and degrades performance.

4. Power Query Joins vs. Power BI Relationships

It is common to confuse Power Query Merges with Data Model Relationships, but they run at completely different stages:

Feature	Power Query Merges (Joins)	Power BI Data Model Relationships
When it runs	During data transformation (before load).	After data is loaded into memory.
How it works	Combines columns physically into one wider table.	Keeps tables separate, connected via virtual relationships.
Best Used For	Consolidation of messy dimension tables.	90% of your reporting and metric modeling.

Power Query Join Types:

Inner Join: Keeps only matching rows from both tables.

Left Outer Join: Keeps all rows from the primary table, adding matching details from the secondary table.

Left Anti Join: Keeps only rows from the primary table that have no match in the secondary table (great for identifying unassigned IDs).

For standard analytics, target a Star Schema with One-to-Many (1:*) relationships and Single Directional filters. Power BI’s engine is engineered specifically for this structure, giving you fast load times, concise DAX, and scalable reports.

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

PRUDENCE KORIR — Sun, 23 Aug 2026 02:50:10 +0000

When I first walked through the gates of LuxDevHq, my first thought was "endless possibilities". However, I was intimidated. I did not think I would measure up. But when I attended my first class, I learned that building something great starts with a great foundation and at LuxDevHq, that means mastering all the basics.

In the last week, I worked on setting up a local project on my desktop which I then pushed to a live repository on GitHub using the command line.

I was working with a "Kenya_Hospital_Health_Records" csv dataset which was to be stored in a sub-folder called "data" within the main folder on the desktop which is named after the file.

These are the steps I took:

1. Local Project Set Up

Open the bash terminal and navigate to the desktop using the cd command and create the main folder along with the sub-folder.

cd Desktop
mkdir -p Kenya_Hospital_Health_Records_Project/Data
cd Kenya_Hospital_Health_Records_Project

Copy and paste the file into the sub-folder.

Creating the README.md File

echo "# KENYA HEALTH RECORDS ANALYSIS" > README.md
echo "##Project Overview" >> README.md
echo "This is a project analysis for a hospital health records" >> README.md
cat README.md

> operator adds new text to the file.
>> operator adds text to already written text.
cat README.md prints the contents of the file for review.
nano README.md edits the file.

2. Creating a Tracked Git Repository

git init creates and empty repository.
git status checks the status of the repository.
git add . adds everything to the current repository.
git commit -m "Kenya Hospital Records Project the commit message helps to remember whatever it entails.
git remote add origin https://github.com/prudencejeronokorir-code/Git-Commands-Class.git this is the SSH key that links the local repository to the empty GitHub repository.
ssh -T git@github.com to confirm that the SSH key works
git push -u origin main pushes the local main branch to the remote origin on GitHub
*-u command helps avoid typing out the branch name upon every update.

And with that you are done!!!

DEV Community: PRUDENCE KORIR

Mastering Intermediate & Advanced SQL: A Practical Healthcare Analytics Guide

Dataset Schema Reference: kenyan_hospital_health_records

1. SQL Window Functions

Concept Overview

Window Mechanics: OVER(), PARTITION BY, ORDER BY, and Frames

Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()

Practical Query: Department Billing Tier Audit

Value Functions: LAG(), LEAD(), FIRST_VALUE(), and LAST_VALUE()

Practical Query: Tracking Chronological Doctor Encounters and Cost Variances

Aggregate Window Functions: SUM(), AVG(), COUNT(), MIN(), and MAX()

Practical Query: Running Revenue and Department Benchmarking

2. SQL Joins

Concept Overview

Join Types & Healthcare Applications

Normalized Staging Tables for Joins

Practical Queries: Joins in Action

1. INNER JOIN: Matching Verified Insured Encounters

2. LEFT JOIN: Identifying Inactive Departments

3. FULL OUTER JOIN: Complete Operational Ledger Reconciliation

4. CROSS JOIN: Generating Coverage Matrices

5. SELF JOIN: Cross-Infection Contact Tracing

3. Common Table Expressions (CTEs)

Concept Overview

Basic and Multiple CTEs

Practical Query: Benchmarking Department Collections Against Regional Targets

Recursive CTEs

Practical Query: Generating a Step-Down Patient Discharge Schedule

CTEs vs. Subqueries

4. SQL Subqueries

Concept Overview

Subquery Classifications

Subquery Structural Placement

1. In the SELECT Clause (Scalar Subquery)

2. In the FROM Clause (Derived Table)

3. In the WHERE Clause (EXISTS vs. IN vs. ALL)

Comparative Summary: CTEs vs. Window Functions vs. Subqueries

Conclusion & Practical Engineering Best Practices

End-to-End Power BI Architecture: Transforming JCars Logistics from Dirty Flat Data to Executive Decision Support

Transforming Dirty Automotive Logistics Data into Executive Decision Support in Power BI

1. The Challenge: Uncleaned Flat File Data

2. Power Query ETL & Data Standardization

Currency Normalization

Date Engine Resiliency

Categorical Bucketing

3. Dimensional Modeling & DAX Architecture

Core Benchmark Results:

4. The 3-Page Executive Dashboard

5. Key Strategic Takeaways

Conclusion

Making Sense of Power BI: Data Modelling, Relationships, and Joins

1. Data Modeling Architecture

2. Fact Tables vs. Dimension Tables

3. Relationships & Directionality

4. Power Query Joins vs. Power BI Relationships

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

1. Local Project Set Up

Creating the README.md File

2. Creating a Tracked Git Repository

Dataset Schema Reference: `kenyan_hospital_health_records`

Window Mechanics: `OVER()`, `PARTITION BY`, `ORDER BY`, and Frames

Ranking Functions: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, and `NTILE()`

Value Functions: `LAG()`, `LEAD()`, `FIRST_VALUE()`, and `LAST_VALUE()`

Aggregate Window Functions: `SUM()`, `AVG()`, `COUNT()`, `MIN()`, and `MAX()`

1. `INNER JOIN`: Matching Verified Insured Encounters

2. `LEFT JOIN`: Identifying Inactive Departments

3. `FULL OUTER JOIN`: Complete Operational Ledger Reconciliation

4. `CROSS JOIN`: Generating Coverage Matrices

5. `SELF JOIN`: Cross-Infection Contact Tracing

1. In the `SELECT` Clause (Scalar Subquery)

2. In the `FROM` Clause (Derived Table)

3. In the `WHERE` Clause (`EXISTS` vs. `IN` vs. `ALL`)