DEV Community: obed marabu

Mastering SQL: Window Functions, Joins, CTEs and Subqueries

obed marabu — Mon, 19 Oct 2026 18:11:59 +0000

Introduction

Basic SELECT, WHERE and GROUP BY statements answer simple questions: how many visits happened, or how much money a department earned. Real hospital analytics asks harder ones. Which doctor’s most expensive visit was it? How long was it between a patient’s first and second visit? Which diagnoses appeared one week and vanished the next? Which doctors earn more per visit than the hospital average?
Four families of SQL tools answer these questions: window functions, joins, Common Table Expressions (CTEs) and subqueries. This article explains each one, shows why it exists, and demonstrates it on our hospital database with a query, an explanation of what the query does, and the expected result.
All results below were worked out by hand from the data in the creation script, so you can run each query and check it against the output shown.

SQL Window Functions

What is a window function?
A window function performs a calculation across a set of rows that are related to the current row, without collapsing those rows into one.
This lets you compare a row with its group, rank rows, look at the previous or next row, and compute running totals, all in a single query. Window functions are used for ranking, top-N per group, period-over-period comparison, running totals, moving averages and percentile bucketing.
The anatomy of a window: OVER, PARTITION BY, ORDER BY, and the frame

SELECT doctor_id,
       visit_id,
       visit_date,
       total_bill_kes,
       SUM(total_bill_kes) OVER (
           PARTITION BY doctor_id                     
           ORDER BY visit_date, visit_id              
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND CURRENT ROW                   
       ) AS running_revenue_kes
FROM visits
ORDER BY doctor_id, visit_date, visit_id;

OVER() is what turns an ordinary function into a window function. It defines the window, the set of rows the function can see. An empty OVER () means the window is the whole result set.
PARTITION BY splits the rows into independent groups, like GROUP BY does, but without merging the rows. The function restarts for every partition. For example, PARTITION BY doctor_id means each doctor is calculated separately.
ORDER BY inside OVER sorts the rows within each partition. It is essential for ranking functions and for LAG/LEAD, and for aggregates it turns the calculation into a running one. It is separate from the ORDER BY at the end of the query, which only sorts the final output.
The window frame narrows the window further, relative to the current row. It is written as:
ROWS BETWEEN <start> AND <end>
where the boundaries can be UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING.

Ranking functions ROW_NUMBER():assigns a unique sequential number (1, 2, 3 …) to each row in a partition, following the ORDER BY. Ties still get different numbers. Why it is used: top-N per group, removing duplicates, and pagination. Scenario: finance wants each doctor’s single most expensive visit.

SELECT doctor_name, visit_id, total_bill_kes
FROM (
    SELECT d.doctor_name,
           v.visit_id,
           v.total_bill_kes,
           ROW_NUMBER() OVER (PARTITION BY v.doctor_id
                              ORDER BY v.total_bill_kes DESC) AS rn
    FROM visits v
    JOIN doctors d ON d.doctor_id = v.doctor_id
) ranked
WHERE rn = 1;

The inner query numbers each doctor’s visits from the highest bill down. Because window functions cannot be used in WHERE, the outer query keeps only rn = 1.

RANK() and DENSE_RANK() What they do: both give tied rows the same rank. They differ in what happens next: • RANK() skips numbers after a tie (1, 2, 2, 4). • DENSE_RANK() does not skip (1, 2, 2, 3). Why they are used: leaderboards where ties are real, for example ranking admissions by length of stay. Scenario: rank admitted patients by length of stay and compare all three numbering functions side by side.

SELECT visit_id,
       length_of_stay_days,
       ROW_NUMBER() OVER (ORDER BY length_of_stay_days DESC, visit_id) AS row_num,
       RANK()       OVER (ORDER BY length_of_stay_days DESC)           AS rnk,
       DENSE_RANK() OVER (ORDER BY length_of_stay_days DESC)           AS dense_rnk
FROM visits
WHERE length_of_stay_days > 0;

Only the ten visits with a hospital stay are kept. Several have the same number of days, so the three functions behave differently.

5._ NTILE(n)_
What it does: splits the ordered rows into n roughly equal buckets and returns the bucket number. When rows do not divide evenly, the earlier buckets get one extra row.
Why it is used: quartiles, deciles and tiering customers or patients by spend.
Scenario: find the top quartile of bills, the highest-spending 25% of visits.

SELECT visit_id, total_bill_kes
FROM (
    SELECT visit_id,
           total_bill_kes,
           NTILE(4) OVER (ORDER BY total_bill_kes DESC) AS quartile
    FROM visits
) q
WHERE quartile = 1
ORDER BY total_bill_kes DESC;

30 visits divided by 4 is 7 remainder 2, so buckets 1 and 2 hold 8 rows each and buckets 3 and 4 hold 7 each. Bucket 1 therefore contains the 8 largest bills.

Value (navigation) functions

LAG() and LEAD() What they do: LAG(col, n, default) reads the value from n rows before the current row within the partition; LEAD(col, n, default) reads n rows after. With no offset, n is 1. If no such row exists, the result is NULL (or the default you supply). Why they are used: comparing a row with its neighbour without a self join: change since the last period, time between events, readmission checks. Scenario: for patients who came back to the hospital, show the previous bill, the next visit date, and the number of days since the last visit.

SELECT patient_id,
       visit_id,
       visit_date,
       total_bill_kes,
       LAG(total_bill_kes)  OVER (PARTITION BY patient_id ORDER BY visit_date) AS previous_bill,
       LEAD(visit_date)     OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit_date,
       visit_date - LAG(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS days_since_last
FROM visits
WHERE patient_id IN (1, 2, 5, 7, 10)
ORDER BY patient_id, visit_date;

Each patient is its own partition, ordered by date. A patient’s first visit has no previous row, so previous_bill and days_since_last are NULL; their last visit has no next row, so next_visit_date is NULL. In PostgreSQL, subtracting two dates returns the number of days.

FIRST_VALUE() and LAST_VALUE() What they do: return the value of an expression from the first or last row of the window frame. Why they are used: compare every row to the beginning or end of a sequence, for example to a doctor’s first bill of the period. The classic trap: because the default frame stops at the current row, LAST_VALUE with only an ORDER BY simply returns the current row’s own value. You must widen the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Scenario: for Dr. Peter Otieno (103), show each visit’s bill alongside his first and last bill of the period.

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

The named window w (defined with the WINDOW clause) is reused to avoid repetition. last_bill_wrong uses the default frame; last_bill_correct uses the full partition.
A related function, NTH_VALUE(col, n), returns the n-th row of the frame and has the same frame caveat.

Aggregate window functions: SUM, AVG, COUNT, MIN, MAX

The familiar aggregates become window functions when followed by OVER. They calculate across the window but keep every row. Without ORDER BY they see the whole partition; with ORDER BY they become running calculations.
Per-partition aggregates (no ORDER BY)
Scenario: for Dr. Faith Chebet’s visits, show each bill next to her visit count, total, average, smallest and largest bill, plus each visit’s share of her total.

SELECT visit_id,
       total_bill_kes,
       COUNT(*) OVER (PARTITION BY doctor_id)                    AS doctor_visits,
       SUM(total_bill_kes) OVER (PARTITION BY doctor_id)         AS doctor_total,
       ROUND(AVG(total_bill_kes) OVER (PARTITION BY doctor_id), 2) AS doctor_avg,
       MIN(total_bill_kes) OVER (PARTITION BY doctor_id)         AS doctor_min,
       MAX(total_bill_kes) OVER (PARTITION BY doctor_id)         AS doctor_max,
       ROUND(100.0 * total_bill_kes
             / SUM(total_bill_kes) OVER (PARTITION BY doctor_id), 1) AS pct_of_doctor_total
FROM visits
WHERE doctor_id = 104
ORDER BY visit_id;

The five aggregates all see the same six rows (doctor 104’s visits), so every row repeats the same count, total, average, minimum and maximum. The percentage column shows why this is powerful: a row-level value divided by a group-level value, with no join or subquery.

SQL Joins

Why joins exist Our database is normalised: a visit stores only doctor_id, not the doctor’s name. To produce a readable report you must combine tables, and a join does that by matching rows on a related column, normally a foreign key to a primary key (visits.doctor_id = doctors.doctor_id). Join Returns Typical question

INNER JOIN
Only rows that match in both tables
Visits with their doctor and department
LEFT JOIN

All rows from the left table, plus matches from the right (NULL if none)
Every doctor, even those with no emergency visits

RIGHT JOIN
All rows from the right table, plus matches from the left
The mirror image of LEFT JOIN
FULL OUTER JOIN

All rows from both tables, matched where possible
What is in one data set but not the other

CROSS JOIN
Every combination of rows (Cartesian product)
Build a complete grid of doctors × admission types
SELF JOIN

A table joined to itself (any join type)
Compare rows of the same table, such as repeat visits

INNER JOIN What it does: keeps only rows that have a match on both sides. Rows without a match are dropped silently. Why it is used: it is the default way to attach descriptive information to a fact table when every row is expected to have a match. Scenario: hospital management wants visits and revenue by department and doctor.

SELECT dep.department_name,
       d.doctor_name,
       COUNT(*)                AS visits,
       SUM(v.total_bill_kes)   AS revenue_kes
FROM visits v
INNER JOIN doctors    d   ON d.doctor_id      = v.doctor_id
INNER JOIN department dep ON dep.department_id = v.department_id
GROUP BY dep.department_name, d.doctor_name
ORDER BY revenue_kes DESC;

Each of the 30 visits is matched to exactly one doctor and one department (both are NOT NULL foreign keys), then grouped. INNER is optional; plain JOIN means the same thing.

LEFT JOIN (LEFT OUTER JOIN) What it does: returns every row from the left table. Where the right table has no match, its columns are NULL. Why it is used: reports that must list all members of a group, including those with zero activity, and anti-joins that find rows with no match. Scenario 1: list every doctor with the number of emergency visits, including doctors with none.

SELECT d.doctor_name,
       COUNT(v.visit_id) AS emergency_visits
FROM doctors d
LEFT JOIN visits v
       ON v.doctor_id = d.doctor_id
      AND v.admission_type = 'Emergency'
GROUP BY d.doctor_name;

The emergency filter sits in the ON clause, not in WHERE. This matters: if you put WHERE v.admission_type = 'Emergency', doctors without emergencies would be removed and the query would behave like an inner join. We count v.visit_id rather than * because COUNT(*) would count the NULL placeholder row as 1.

Scenario 2 (anti-join): which patients have never been admitted as inpatients?

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

Patients with no inpatient visit get a NULL visit_id from the left join, and the WHERE keeps exactly those. Nine patients (3, 4, 10, 14, 15, 16, 22, 23, 24) have inpatient visits, so 16 patients are returned: Amina Otieno, John Kariuki, Esther Achieng, Mohamed Ali, Lucy Wambui, Samuel Kiptoo, Mary Wairumu, Brian Omondi, Catherine Musyoka, Daniel Maina, Isaac Kamau, Jane Muthoni, Kevin Barasa, Lilian Adhiambo, Moses Cheruiyot and Josephine Wekesa.

RIGHT JOIN (RIGHT OUTER JOIN) What it does: the mirror of LEFT JOIN. All rows from the right table are kept. Why it is used: rarely, because any right join can be rewritten as a left join by swapping the table order, and most people find left joins easier to read. It is useful when you are adding a join to an existing query and the table you want to preserve is already on the right.

SELECT d.doctor_name,
       COUNT(v.visit_id) AS emergency_visits
FROM visits v
RIGHT JOIN doctors d
        ON v.doctor_id = d.doctor_id
       AND v.admission_type = 'Emergency'
GROUP BY d.doctor_name;

Here doctors is on the right, so all four doctors are preserved. The result is identical to Scenario 1 above. Compare the two queries to see that A LEFT JOIN B and B RIGHT JOIN A are the same thing.

FULL OUTER JOIN What it does: keeps all rows from both tables. Matching rows are combined; unmatched rows from either side appear with NULLs for the other side. Why it is used: comparing or reconciling two sets, such as this week versus last week, or system A versus system B. Scenario: which diagnoses were seen in only one of the two weeks? Week 1 is 1–7 June and week 2 is 8–15 June.

SELECT w1.diagnosis_id AS week1_diagnosis,
       w2.diagnosis_id AS week2_diagnosis
FROM (SELECT DISTINCT diagnosis_id FROM visits
      WHERE visit_date BETWEEN '2026-06-01' AND '2026-06-07') w1
FULL OUTER JOIN
     (SELECT DISTINCT diagnosis_id FROM visits
      WHERE visit_date BETWEEN '2026-06-08' AND '2026-06-15') w2
  ON w1.diagnosis_id = w2.diagnosis_id
WHERE w1.diagnosis_id IS NULL OR w2.diagnosis_id IS NULL
ORDER BY COALESCE(w1.diagnosis_id, w2.diagnosis_id);

Each side is a de-duplicated list of diagnosis IDs for one week. The full join lines them up; the WHERE keeps only the rows where one side is missing. Week 1 has 11 diagnoses, week 2 has 15, and 8 appear in both.

CROSS JOIN

What it does: pairs every row of the first table with every row of the second. With 4 doctors and 3 admission types you get 12 rows. There is no ON clause.
Why it is used: generating complete grids (all combinations, including those with no data), calendars and scenario tables. It is dangerous on large tables, since 1,000 × 1,000 rows is a million.
Scenario: a complete doctor × admission-type matrix, with zeros where a combination never occurred.

SELECT d.doctor_name,
       t.admission_type,
       COUNT(v.visit_id) AS visits
FROM doctors d
CROSS JOIN (VALUES ('Outpatient'), ('Inpatient'), ('Emergency')) AS t(admission_type)
LEFT JOIN visits v
       ON v.doctor_id = d.doctor_id
      AND v.admission_type = t.admission_type
GROUP BY d.doctor_name, t.admission_type
ORDER BY d.doctor_name, t.admission_type;

The cross join builds all 12 combinations, then the left join counts the real visits for each. Without the cross join, combinations with zero visits would simply be missing from the report.

SELF JOIN What it does: joins a table to itself, using two aliases so the database treats them as two separate copies. It is not a different keyword, just any join where both sides are the same table. Why it is used: to compare rows within one table: employees and their managers, pairs of records with the same attribute, or an event and its previous event. Scenario 1: find repeat visits by the same patient and the number of days between them.

SELECT p.patient_name,
       v1.visit_id   AS first_visit,
       v2.visit_id   AS return_visit,
       v2.visit_date - v1.visit_date AS days_between
FROM visits v1
JOIN visits v2
  ON v1.patient_id = v2.patient_id
 AND v2.visit_date > v1.visit_date
JOIN patients p ON p.patient_id = v1.patient_id
ORDER BY p.patient_name;

v1 is the earlier visit and v2 the later one. The condition v2.visit_date > v1.visit_date stops a visit being paired with itself and prevents each pair appearing twice.
This gives the same facts as the LAG example in Section 3.4. Both approaches work; the window function is shorter and usually faster, while the self join is more flexible when the matching rule is complicated.
Scenario 2: pairs of patients who come from the same county.

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

Two pairs: Esther Achieng and Brian Omondi (Kisumu) and Amina Otieno and Isaac Kamau (Nairobi). The condition a.patient_id < b.patient_id removes mirror-image duplicates (A with B and B with A). This is also how you would query a hierarchy such as staff and their managers in a single table, which we revisit with recursion in the CTE section.

Choosing the right join

Need only complete matches, and every row should match? INNER JOIN.
Need every row of a main list, with optional details? LEFT JOIN (and WHERE right.key IS NULL to find the missing ones).
Need to compare two lists and see what is unique to each? FULL OUTER JOIN.
Need all combinations, or a skeleton to fill with counts? CROSS JOIN.
Need to relate rows of a table to other rows of the same table? SELF JOIN.
Think RIGHT JOIN? Swap the tables and use LEFT JOIN.
Common mistakes: filtering the right table in WHERE after a left join (turns it into an inner join), forgetting the join condition (accidental cross join), and joining on a column that is not unique (row multiplication, which inflates sums). Always check that totals still match after adding a join.

Common Table Expressions (CTEs)

_ What is a CTE?_

A Common Table Expression is a named, temporary result set defined with the WITH keyword at the start of a statement. You can then use that name in the main query as if it were a table. It exists only for the duration of that one statement; nothing is stored in the database.
CTEs are used to break a complex query into readable steps, to reuse a result several times in one query, to filter on window-function results, and to walk hierarchies with recursion. In PostgreSQL 12 and later, a non-recursive CTE that is referenced once is normally inlined by the planner (like a subquery), and you can force the behaviour with_ AS MATERIALIZED or AS NOT MATERIALIZED._

Basic CTE

Scenario: produce a doctor performance summary. First calculate the numbers per doctor, then attach names and specialties.

WITH doctor_summary AS (
    SELECT doctor_id,
           COUNT(*)                          AS visits,
           SUM(total_bill_kes)               AS revenue_kes,
           ROUND(AVG(total_bill_kes), 2)     AS avg_bill_kes
    FROM visits
    GROUP BY doctor_id
)
SELECT d.doctor_name,
       d.doctor_specialty,
       s.visits,
       s.revenue_kes,
       s.avg_bill_kes
FROM doctor_summary s
JOIN doctors d ON d.doctor_id = s.doctor_id
ORDER BY s.revenue_kes DESC;

The CTE doctor_summary reduces 30 visit rows to 4 doctor rows. The main query then joins those 4 rows to doctors. Aggregating first and joining second is cleaner and often faster than joining first.

Multiple CTEs

You can define several CTEs in one WITH, separated by commas. Later CTEs can refer to earlier ones, so a query reads as a pipeline of steps.
Scenario: which doctors charge more per visit than the hospital average, and by how much?

WITH doctor_stats AS (
    SELECT doctor_id, AVG(total_bill_kes) AS avg_bill
    FROM visits
    GROUP BY doctor_id
),
hospital_avg AS (
    SELECT AVG(total_bill_kes) AS avg_bill
    FROM visits
)
SELECT d.doctor_name,
       ROUND(s.avg_bill, 2)              AS doctor_avg,
       ROUND(h.avg_bill, 2)              AS hospital_avg,
       ROUND(s.avg_bill - h.avg_bill, 2) AS above_average_by
FROM doctor_stats s
CROSS JOIN hospital_avg h
JOIN doctors d ON d.doctor_id = s.doctor_id
WHERE s.avg_bill > h.avg_bill
ORDER BY above_average_by DESC;

Step one finds each doctor’s average bill; step two finds the single hospital-wide average (KES 16,266.67). Because hospital_avg has exactly one row, a CROSS JOIN safely attaches it to every doctor. The WHERE then keeps doctors above the average.

CTEs with joins and aggregations

Scenario: the hospital board wants revenue by diagnosis category. The CTE joins the fact table to its dimension first; the final query aggregates.

WITH visit_detail AS (
    SELECT v.visit_id,
           v.total_bill_kes,
           dg.diagnosis_category
    FROM visits v
    JOIN diagnosis dg ON dg.diagnosis_id = v.diagnosis_id
)
SELECT diagnosis_category,
       COUNT(*)            AS visits,
       SUM(total_bill_kes) AS revenue_kes
FROM visit_detail
GROUP BY diagnosis_category
ORDER BY revenue_kes DESC;

Recursive CTEs

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

The anchor member, which runs once and produces the starting rows.
The recursive member, which refers to the CTE’s own name and runs repeatedly on the rows produced in the previous round, until it returns no new rows. You must use the RECURSIVE keyword in PostgreSQL, and you m_ust make sure the recursion ends, either through a condition in the recursive member or a depth limit_, otherwise the query runs forever. Example 1: generating a calendar to find days with no visits. Our data stops on 15 June, so we extend the calendar to 17 June to show how gaps appear.

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

The anchor produces 1 June; each round adds one more day until 17 June is reached, giving 17 rows. A left join then counts visits per day, with 0 for days that have none.

CTEs versus subqueries

Both let you use the result of one query inside another, and for simple cases they give identical results and plans. They differ in readability, reuse and capability.
Performance
Usually the same; PostgreSQL 12+ may inline it, older versions always materialised it
Optimiser can always inline it
Rule of thumb: use a CTE when the logic has several steps, when you need the same result twice, or when you need recursion. Use a subquery for a short, single-use filter such as WHERE x IN (SELECT ...), or when you need a correlated check against each outer row.

SELECT d.doctor_name,
       ROUND(AVG(v.total_bill_kes), 2) AS doctor_avg
FROM visits v
JOIN doctors d ON d.doctor_id = v.doctor_id
GROUP BY d.doctor_name
HAVING AVG(v.total_bill_kes) > (SELECT AVG(total_bill_kes) FROM visits);

For a problem this small the subquery is more compact. The CTE wins as the number of steps grows.

SQL Subqueries

What is a subquery?

A subquery (inner query) is a SELECT statement nested inside another statement, always written in parentheses. The inner query produces a value, a list or a table, and the outer query uses it. Subqueries let you answer questions in two steps without creating temporary tables, for example: first find the average bill, then find the visits above it.
A subquery can appear in three places:
Clause
What the subquery returns
Example use
WHERE (or HAVING)
A single value, a list, or an existence test
Filter visits above the average bill
FROM
A table (called a derived table; it needs an alias)
Aggregate first, then aggregate the aggregates
SELECT
A single value per outer row (scalar)
Show a count beside each doctor

Scalar subqueries

What it is: a subquery that returns exactly one row and one column, so it can be used anywhere a single value is allowed. If it returns more than one row, PostgreSQL raises an error.
Scenario: list visits billed above the hospital average.

SELECT visit_id, total_bill_kes
FROM visits
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits)
ORDER BY total_bill_kes DESC;

The inner query returns one number, 16,266.67. The outer query compares each bill with it. This cannot be written as WHERE total_bill_kes > AVG(total_bill_kes), because aggregates are not allowed in WHERE.

Non-correlated (independent) subqueries

What it is: a subquery that does not refer to the outer query. It can run completely on its own, and the database evaluates it once. The scalar example above is non-correlated, and so is any IN (SELECT ...).
Scenario: list the patients who have ever been admitted as inpatients.

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

The inner query returns the list of patient IDs with an inpatient visit; the outer query keeps patients whose ID is in that list. Duplicates in the list do not matter.
Caution with NOT IN: if the inner list contains even one NULL, NOT IN returns no rows at all, because the comparison with NULL is unknown. Columns such as ward_id and diagnosis_id in visits are nullable, so prefer NOT EXISTS for “not in” logic.

Correlated subqueries

What it is: a subquery that refers to a column of the outer query, so it is conceptually re-evaluated for every outer row.
Scenario: find visits where the bill is higher than that same doctor’s own average bill.

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

For each outer visit v, the inner query calculates the average for that visit’s doctor (v2.doctor_id = v.doctor_id) and compares. The comparison value changes from doctor to doctor: 23,344.44 for Dr. Mwangi, 10,866.67 for Dr. Wanjiku, 17,533.33 for Dr. Otieno and 12,483.33 for Dr. Chebet.

Nested subqueries

What it is: a subquery inside another subquery. Each level feeds the one above it.
Scenario: list the patients treated by the highest-earning doctor.

SELECT patient_name
FROM patients
WHERE patient_id IN (
        SELECT patient_id
        FROM visits
        WHERE doctor_id = (
                SELECT doctor_id
                FROM visits
                GROUP BY doctor_id
                ORDER BY SUM(total_bill_kes) DESC
                LIMIT 1));

From the inside out: the innermost query ranks doctors by revenue and returns the top one (doctor 101, KES 210,100). The middle query returns the IDs of patients that doctor saw (1, 2, 5, 10, 12 and 16). The outer query turns those IDs into names.

Subqueries with IN, EXISTS, ANY and ALL

IN
expression IN (subquery) is true if the value equals any value the subquery returns.NOT IN is its negation, with the NULL caveat above.
EXISTS and NOT EXISTS
What it does: _EXISTS _(subquery) is true if the subquery returns at least one row; the columns selected do not matter (by convention SELECT

It is almost always correlated, and it stops as soon as one match is found. Scenario: which doctors have treated at least one Red-triage (critical) patient, and which have not?

SELECT d.doctor_name
FROM doctors d
WHERE EXISTS (SELECT 1
              FROM visits v
              WHERE v.doctor_id = d.doctor_id
                AND v.triage_level = 'Red');

SELECT d.doctor_name
FROM doctors d
WHERE NOT EXISTS (SELECT 1
                  FROM visits v
                  WHERE v.doctor_id = d.doctor_id
                    AND v.triage_level = 'Red');

ANY and ALL
What they do: compare a value with every value returned by a subquery.

ANY (subquery) is true if the value is greater than at least one returned value (equivalent to greater than the minimum).
ALL (subquery) is true if the value is greater than every returned value (equivalent to greater than the maximum).
= ANY (...) is the same as IN (...), and <> ALL (...) is the same as NOT IN (...). Scenario: compare General Medicine bills (department 20) with Orthopedics bills (department 30). Orthopedic bills range from KES 6,500 to KES 33,000.

Greater than at least one Orthopedics bill (i.e. above 6,500)

SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 20
  AND total_bill_kes > ANY (SELECT total_bill_kes FROM visits WHERE department_id = 30);
-- Greater than every Orthopedics bill (i.e. above 33,000)
SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 20
  AND total_bill_kes > ALL (SELECT total_bill_kes FROM visits WHERE department_id = 30);

Subqueries in SELECT, FROM and WHERE

In SELECT (scalar subquery per row):

SELECT d.doctor_name,
       (SELECT COUNT(*)
        FROM visits v
        WHERE v.doctor_id = d.doctor_id) AS visits
FROM doctors d
ORDER BY d.doctor_id;

This returns one row per doctor with 9, 9, 6 and 6 visits (Mwangi, Wanjiku, Otieno, Chebet). It is correlated, since it uses d.doctor_id. A join with GROUP BY is usually more efficient, but this form is convenient for adding one extra column to an existing query without disturbing its grouping.
*In FROM *(derived table):

SELECT ROUND(AVG(revenue), 2) AS avg_revenue_per_doctor
FROM (SELECT doctor_id, SUM(total_bill_kes) AS revenue
      FROM visits
      GROUP BY doctor_id) AS doctor_revenue;

The inner query produces four revenue totals; the outer query averages them. The result is 122000.00 (488,000 divided by 4 doctors). This “aggregate of an aggregate” cannot be written as AVG(SUM(...)) in a single SELECT, so a derived table (or CTE) is required. The alias doctor_revenue is mandatory.

Conclusion

Learning these four concepts changes what SQL feels like. It stops being a way to retrieve rows and becomes a way to ask questions about data.

From Data to Decisions: Mastering PBI Charts and Dashboards

obed marabu — Mon, 29 Jun 2026 10:36:32 +0000

Introduction

Power BI is one of the leading business intelligence platforms precisely because it simplifies that transformation, turning spreadsheets, databases, and data streams into visual stories that anyone can understand.

Core building blocks in PBI

Charts-Individual visual representations of data.
Dashboards-Curated collections of charts and visuals, assembled into a unified view that tells a broader story at a glance.

Together, Power BI charts and dashboards transform complex, multi-source data into interactive, real-time visuals that give every stakeholder, from analyst to executive, instant clarity and the confidence to act.

1. Charts in Power BI

Power BI charts are used to visualize and analyse data from different perspectives. When combined, users can understand trends and relationships in data. This helps to:

Easily compare different values
Observe patterns over time
Analyze connections between data
Combine charts for hierarchical or multi-dimensional data

Types of Charts

Stacked Bar Chart : A stacked bar chart in Power BI helps you compare totals while visualizing how individual subcategories contribute to those totals. To create one, you need to drag your categories to the Y-axis, numeric values to the X-axis, and the subcategory into the Legend field.
Column Chart : They display categorical data with vertical rectangular bars whose lengths are proportional to the values they represent.
100% Stacked Column Chart : Stretches the stacked bars vertically to fit 100% of the visual window to emphasize the relative percentage allocation
Bar Chart : They display categorical data with horizontal rectangular bars whose lengths are proportional to the values they represent.
Line Chart : Line charts plot data points connected by straight lines, typically used to show how a value changes over time. They are mostly used for visualising trends, seasonal patterns, and performance over a continuous period.
Area Chart : They are similar to line charts but with the area beneath the line filled in with colour. They emphasise the magnitude of change over time, making them useful for showing volume energy rather than just the rate of change.
Pie Chart : Pie charts divide a circle into slices, each representing a category's proportion of the whole. They are best for showing simple part-to-whole relationships when there are a small number of distinct categories.
Donut Chart : Similar to pie charts but with with a hollow centre, often used to display a summary metric in the middle.
Treemap : They display hierarchical data as a set of nested rectangles, where the size of each rectangle is proportional to its value. They are useful when you need to show both the size of categories and their contribution to a hierarchy simultaneously.
Scatter Chart : Scatter charts plot individual data points on a two-axis grid, each point representing the intersection of two numerical values. A bubble chart extends this by adding a third variable represented by the size of each point. They are used to explore correlations and relationships between two variables
Card (single metric) : This is used to highlight a single, key metric or KPI that responds dynamically to filters and slicers.

Dashboards in Power BI

A Power BI dashboard is a single-page canvas that brings together multiple visuals into a unified, interactive view of your data. Unlike a report page, a dashboard is designed for monitoring: it is meant to be glanced at, not studied at length, and it surfaces the most important metrics so that decision-makers can quickly assess the state of the business.
Dashboards in Power BI can pull visuals from multiple underlying reports and datasets, making them a powerful aggregation layer. They support real-time data refresh and can be shared across an organisation, making them central to collaborative data culture.

Types of Dashboards

Strategic Dashboards: Designed for executives and upper management. They provide a high-level overview of long-term business goals, company performance, and key performance indicators (KPIs) to aid in high-level decision making.
Characteristics:
Minimal and visually clean — low information density by design
Focus on a small number of carefully selected KPIs
Use large, bold metrics and simple colour coding (on track / at risk / off track)
Often include period-on-period comparison and target vs. actual indicators
Operational Dashboards: Focused on day-to-day operations and real-time monitoring. They help middle management and operational staff quickly identify bottlenecks or issues that require immediate action.
Characteristics:
Refreshed frequently — sometimes in real time
Focused on current status rather than historical trends
Use traffic-light indicators, gauges, and alert-style KPI cards
Simple and uncluttered to enable rapid reading
Analytical Dashboards: Built for data analysts and business intelligence professionals. They are complex, data-rich views designed to process large volumes of data, uncover deep trends, and identify the "why" behind business patterns.
Characteristics:
Rich with interactive filters, slicers, and drill-through capabilities
Include trend lines, comparisons to prior periods, and benchmarks
May feature more complex chart types (scatter, treemap, decomposition tree)
Often linked to detailed underlying reports for deeper investigation
Tactical Dashboards: Used primarily by department heads and middle managers. They bridge the gap between overarching strategy and day-to-day operations, tracking progress toward short-term goals.
Characteristics:
Balance summary KPIs with supporting trend data
May include both current status and recent historical context
Interactive enough to allow managers to investigate underperformance
Typically refreshed daily or weekly
Below is a visual example of a Power BI Dashbard:

Conclusion

Power BI charts and dashboards are not simply visual decoration for data. They are the primary interface through which organisations understand, communicate, and act on information. Choosing the right chart type ensures that your data is presented clearly and honestly. Designing the right kind of dashboard ensures that the right people see the right information at the right time.

Linux Fundamentals for Data Engineering

obed marabu — Fri, 12 Jun 2026 18:19:43 +0000

Introduction

From a hobby to one of the best operating systems, Linux is an important tool in the world of Data Engineering.Linux is a free, open-source operating system kernel first developed by Linus Torvalds in 1991. Built on the principles of Unix, it has since grown into the backbone of modern computing — powering everything from smartphones and personal laptops to enterprise servers, supercomputers, and cloud infrastructure.

LinuX Basic Commands

ls — The most frequently used command in Linux to list directories
pwd — Print working directory command in Linux
cd — Linux command to navigate through directories
mkdir — Command used to create directories in Linux
mv — Move or rename files in Linux
cp — Similar usage as mv but for copying files in Linux
rm — Delete files or directories
touch — Create blank/empty files
ln — Create symbolic links (shortcuts) to other files
cat — Display file contents on the terminal
clear — Clear the terminal display
echo — Print any text that follows the command
less — Linux command to display paged outputs in the terminal
man — Access manual pages for all Linux commands
uname — Linux command to get basic information about the OS
whoami — Get the active username
tar — Command to extract and compress files in Linux
grep — Search for a string within an output
head— Return the specified number of lines from the top
tail — Return the specified number of lines from the bottom
diff — Find the difference between two files
cmp — Allows you to check if two files are identical
comm — Combines the functionality of diff and cmp
sort — Linux command to sort the content of a file while outputting
export — Export environment variables in Linux
zip — Zip files in Linux
unzip — Unzip files in Linux
ssh — Secure Shell command in Linux
service — Linux command to start and stop services
ps — Display active processes
kill and killall — Kill active processes by process ID or name
df— Display disk filesystem information
mount — Mount file systems in Linux
chmod — Command to change file permissions
chown — Command for granting ownership of files or folders
ifconfig — Display network interfaces and IP addresses
traceroute — Trace all the network hops to reach the destination
wget — Direct download files from the internet
ufw— Firewall command
iptables — Base firewall for all other firewall utilities to interface with
apt, pacman, yum, rpm — Package managers depending on the distro
sudo — Command to escalate privileges in Linux
cal — View a command-line calendar
alias — Create custom shortcuts for your regularly used commands
dd — Majorly used for creating bootable USB sticks
whereis — Locate the binary, source, and manual pages for a command
whatis — Find what a command is used for
top — View active processes live with their system usage
useradd and usermod — Add new user or change existing users data
passwd — Create or update passwords for existing users

Practical Examples of Linux Commands

The `ls` command

The most frequently used command in Linux to list directories.
ls command options;

-l - Long listing format
-a - Include hidden files
-h - Human-readable sizes
-t - Sort by modification time
-r - Reverse order while sorting
-R - List subdirectories recursively
-S - Sort by file size
-1 - List one file per line
-d - List directories themselves, not their contents
-F - Append indicator (one of */=@|) to entries

Editors in Linux

We use text editors in Linux because "everything is a file" in the Linux philosophy, meaning system settings, hardware configurations, scripts, and logs are all stored as plain text files. Unlike word processors (like Microsoft Word) that embed hidden formatting code, text editors handle pure, raw text, which is required to safely modify system operations without corrupting data.
They include;

Vim(vi): A highly powerful, modal editor available by default on almost every Linux distribution. It utilizes specific modes for typing versus navigating.
Nano :A simple, beginner-friendly editor that displays helpful keyboard shortcuts at the bottom of the screen.

File Permissions in Linux

Reading the Permission String
This shows the permissions allowed to each user in a file.
Example: (-rw-r--r--)

First character(-): Shows that this is a file, if first character is (d), then it is a directory.
Characters 2-4(rw-): The owner has read and write access, but cannot execute it.
Characters 5-7(r--): Members of the group have read-only access it.
Characters 8-10(r--): All other users on the system have read-only access

Numeric Permission Values

0(---) :No permission
1(--x) :Execute only
2(-w-) :Write only
3(-wx) :Write + Execute
4(r--) :Read only
5(r-x) :Read + Execute
6(rw-) :Read + Write
7(rwx) :Read + Write + Execute

Chmod and Chown Commands

The chmod command modifies read, write, and execute permissions for files or directories, while the chown command modifies the user and/or group ownership of those files. Together, they form the core of the Linux file security and access control model.

Chmod examples

chmod 777 file :Everyone can read, write, execute
chmod 755 file :Owner full permission; others read + execute
chmod 644 file :Owner read/write; others read only
chmod 600 file :Private file (owner only)

Chown examples

chmod u+x file :Add execute for user
chmod g-w file :Remove write from group
chmod o+r file :Add read for others
chmod a+x file :Allow execute for everyone

Ownership commands

ls -l :View ownership and permissions
sudo chown user file :Change owner
sudo chown user:group file :Change owner and group
sudo adduser username :Create new user
sudo usermod -aG devops username :Add user to group

Conclusion

Learning and understanding the Linux fundamentals is necessary for all data engineer. It was the perfect start for my data engineering journey.

DEV Community: obed marabu

Mastering SQL: Window Functions, Joins, CTEs and Subqueries

Introduction

SQL Window Functions

Value (navigation) functions

Aggregate window functions: SUM, AVG, COUNT, MIN, MAX

SQL Joins

Common Table Expressions (CTEs)

_ What is a CTE?_

Basic CTE

Multiple CTEs

CTEs with joins and aggregations

Recursive CTEs

CTEs versus subqueries

SQL Subqueries

What is a subquery?

Scalar subqueries

Non-correlated (independent) subqueries

Correlated subqueries

Nested subqueries

Subqueries with IN, EXISTS, ANY and ALL

Subqueries in SELECT, FROM and WHERE

Conclusion

From Data to Decisions: Mastering PBI Charts and Dashboards

Introduction

Core building blocks in PBI

1. Charts in Power BI

Types of Charts

Dashboards in Power BI

Types of Dashboards

Conclusion

Linux Fundamentals for Data Engineering

Introduction

LinuX Basic Commands

Practical Examples of Linux Commands

The ls command

Editors in Linux

File Permissions in Linux

Numeric Permission Values

Chmod and Chown Commands

Chmod examples

Chown examples

Ownership commands

Conclusion

The `ls` command