Introduction to SQL Window Functions, CTEs, Joins & Subqueries

Introduction to SQL

SQL (pronounced "Sequel") stands for Structured Query Language and it is the standard programming language for managing, querying, and manipulating relational databases. SQL is declarative, meaning you tell the database what data you want, and then the database figures out the best way to get it.

What is SQL Used For in Data?

In data analytics, engineering, and science, SQL is used to:

• Query and Retrieve Data: Pull specific records out of millions of rows using filters, sorting, and conditional logic.

• Aggregate and Summarize: Calculate metrics like totals, averages, minimums, and maximums using functions like SUM(), AVG(), and COUNT().

• Join Disparate Datasets: Connect related tables (such as matching doctors to their patient visits or departments to revenue targets) using JOIN operations.

• Transform and Clean: Reshape raw data into clean, structured formats ready for reporting, machine learning, or dashboards.

The Basic Anatomy of a SQL Query

At its core, a standard SQL query follows a predictable logical structure.

SQL

SELECT column_1, SUM(column_2) AS total_metric
FROM table_name
WHERE condition = 'value'
GROUP BY column_1
ORDER BY total_metric DESC;

• SELECT: Specifies the exact columns or calculated values you want to see in your final output.

• FROM: Identifies the table or tables where the data lives.

• WHERE: Filters the rows before any groupings happen based on specific criteria.

• GROUP BY: Bundles rows together that share a common attribute so you can perform aggregate calculations on them.

• ORDER BY: Sorts your final results (e.g., highest revenue to lowest).

Window Functions

In SQL Window functions perform calculations across a set of table rows that are somehow related to the current row.

Window functions also keep all individual rows intact while adding the calculated result as a new column in each row.

A very important component of window functions is the OVER() function that tells PostgreSQL how to group and sort the rows for a specific operation.

We use two major tools Partition by: and Order by: inside the Over() function.

• Partition by:
  Divides rows into groups similar to GROUP BY without collapsing them.

• Order by:
  Sorts the rows within each partition.

Types of Window Functions

1. Ranking Functions

• ROW_NUMBER() Gives every row a unique sequential number even if the values are identical.

SCENARIO

The hospital's patient retention team wants to audit patient records and identify every patient's absolute most recent visit to the clinic.

QUERY

select
    v.patient_id,
    p.patient_name,
    v.visit_id,
    v.visit_date,
    row_number() over(
        partition by v.patient_id
        order by v.visit_date desc
    ) as visit_recency_rank
from visits v
join dim_patients p on v.patient_id = p.patient_id;

TASK
This query ranks patients visits chronologically, assigns 1 to the most recent visit, 2 to the second most recent (and so on) while pulling descriptive patient information from the patients dimension table, and joins the fact table (visits) with the dimension table patients.

• RANK()
  Gives identical values the same rank but leaves gaps in the numbering by skipping one value

• DENSE_RANK()
  Assigns sequential rank to rows within a partition without any gaps in the ranking number.
  The next rank is the immediate integer unlike rank() where it skips a number after a tie.

SCENARIO

The medical director is evaluating doctors based on their average patient satisfaction score. Where there's a tie, we want to see how standard ranking handles ties versus dense ranking.

QUERY

select
    d.doctor_name,
    ROUND(AVG(v.patient_satisfaction_score), 2) AS avg_satisfaction,
    RANK() OVER (ORDER BY AVG(v.patient_satisfaction_score) DESC) AS satisfaction_rank,
    DENSE_RANK() OVER (ORDER BY AVG(v.patient_satisfaction_score) DESC) AS satisfaction_dense_rank
FROM Visits v
JOIN Dim_Doctors d ON v.doctor_id = d.doctor_id
GROUP BY d.doctor_name;

TASK
This query calculates the average patient satisfaction score for each doctor, and then ranks the doctors from highest satisfaction to lowest. The query rolls up all individual patient visits per doctor, computes an aggregate average and then uses window functions RANK and DENSE_RANK to establish a leaderboard of physician performance.

• DENSE_RANK()
  Assigns sequential rank to rows within a partition without any gaps in the ranking number.
  The next rank is the immediate integer unlike rank() where it skips a number after a tie.

• NTILE(n)
  Distributes the rows in a partition into a specified number of roughly equal groups (buckets) and the designated number of buckets is passed as an argument e.g., n=4 for quartiles is NTILE(4)

SCENARIO
The finance department wants to segment all 30 hospital visits into 4 equal financial tiers (quartiles) based on total_bill_kes to identify top-tier high-billing visits versus routine lower-cost visits.

QUERY

SELECT 
    visit_id,
    patient_id,
    total_bill_kes,
    NTILE(4) OVER (ORDER BY total_bill_kes DESC) AS billing_quartile
FROM Visits;

TASK
This query segments all hospital visits into 4 equal financial tiers from highest billing (1) to lowest billing (4)

2. Aggregate Window Functions

When Over() is attached to an aggregate function it turns into a window function.

SCENARIO
Management wants a master ledger of all visits that shows individual transaction amounts alongside cumulative running totals and departmental averages without collapsing rows.

QUERY

SELECT 
    visit_id,
    visit_date,
    department_id,
    total_bill_kes,
    -- Running total of all hospital revenue chronologically
    SUM(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
    ) AS cumulative_revenue,
    -- Average bill within the same department
    ROUND(AVG(total_bill_kes) OVER (
        PARTITION BY department_id
    ), 2) AS dept_average_bill,
    -- Cumulative count of visits over time
    COUNT(visit_id) OVER (
        ORDER BY visit_date, visit_id
    ) AS running_visit_count,
    -- Min and Max bills recorded within that department so far
    MIN(total_bill_kes) OVER (
        PARTITION BY department_id 
        ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_min_dept_bill,
    MAX(total_bill_kes) OVER (
        PARTITION BY department_id 
        ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_max_dept_bill
FROM Visits;

TASK
Combines multiple aggregate functions with OVER() to show running totals, department visit counts, and departmental price extremes alongside every individual row.

3. Value Window Functions

Value window functions are analytical tools that let you copy a specific value from a different row and display it alongside a current row.

Value window functions look into a data set and pull out actual existing data values and allow us to perform comparative, time-based analysis without having to rely on complex self-joins or subqueries.

Types of Value Window Functions

• Lag()
  Allows us to access data from a previous row

• Lead()
  Allows us to access data from the following row.

SCENARIO
The hospital wants to look at a patient's sequential visit history to see how much time elapsed between their current visit and their previous visit, as well as how their total bill changed compared to the next visit.

QUERY

SELECT 
    patient_id,
    visit_id,
    visit_date,
    total_bill_kes,
    LAG(visit_date) OVER (
        PARTITION BY patient_id 
        ORDER BY visit_date ASC
    ) AS previous_visit_date,
    LEAD(total_bill_kes) OVER (
        PARTITION BY patient_id 
        ORDER BY visit_date ASC
    ) AS next_visit_bill
FROM Visits;

TASK

Use LAG() to look backward at the previous visit date for the same patient, and LEAD() to look forward at the next visit's total bill.

• First_Value()
  Pulls the value from the very first row of the current window frame

• Last_Value()
  Grabs the Value from the last row of the window frame

SCENARIO

Hospital administrators want to inspect every individual visit in each department alongside two benchmarks: the highest total bill generated in that department and the lowest total bill generated in that department.

QUERY

SELECT 
    v.visit_id,
    dept.department_name,
    v.total_bill_kes,
    FIRST_VALUE(v.total_bill_kes) OVER (
        PARTITION BY v.department_id 
        ORDER BY v.total_bill_kes DESC
    ) AS highest_bill_in_dept,
    LAST_VALUE(v.total_bill_kes) OVER (
        PARTITION BY v.department_id 
        ORDER BY v.total_bill_kes DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_bill_in_dept
FROM Visits v
JOIN Dim_Departments dept ON v.department_id = dept.department_id;

TASK

Pull the highest bill (FIRST_VALUE) and lowest bill (LAST_VALUE) for each department onto every individual visit row.

SQL Joins

In SQL, the join clause is used to combine rows from two or more tables based on a related column between them.

Types of Joins

Inner Join

This join returns only the rows that have matching values in both tables. If a record exists in one table but it doesn't have a match in another table, it is excluded entirely.

SCENARIO

List all completed patient visits, the name of the doctor who handled them and the department they visited.

QUERY

SELECT v.visit_id, p.patient_name, d.doctor_name, dept.department_name, v.total_bill_kes
FROM Visits v
INNER JOIN Patients p ON v.patient_id = p.patient_id
INNER JOIN Doctors d ON v.doctor_id = d.doctor_id
INNER JOIN Departments dept ON v.department_id = dept.department_id;

Left (Outer) Join

This join returns all rows from the left table and only the matched rows from the right table. If there is no match in the right table, the result is NULL for the right table's columns.

Right (Outer) Join

This join returns all rows from the right table and only the matched rows from the left table. If there is no match in the left table, the result is NULL for the left table's columns.

SCENARIO
List all doctors alongside any visits they managed.

QUERY

SELECT d.doctor_name, d.doctor_specialty, v.visit_id, v.total_bill_kes
FROM Visits v
RIGHT JOIN Doctors d ON v.doctor_id = d.doctor_id;

Full (Outer) Join

Returns all records when there is a match in either the left or the right table. Rows with no matching values on either side will show NULL values on the unmatched side.

Self Join

This is a regular join where a table is joined with itself and is used when a table contains hierarchical or comparative relationships within it's own rows.

SCENARIO
Compare doctors within the same specialty e.g., to pair up doctors who share the exact same medical specialty.

QUERY

SELECT 
    d1.doctor_name AS doctor_a,
    d2.doctor_name AS doctor_b,
    d1.doctor_specialty
FROM Doctors d1
INNER JOIN Dim_Doctors d2 
    ON d1.doctor_specialty = d2.doctor_specialty 
    AND d1.doctor_id < d2.doctor_id; -- Prevents duplicate reverse pairs and self-matching

Cross Join

This returns the cartesian product of two or more tables by combining every row from the first table with every row from the second table.

Common Table Expressions (CTEs)

Common Table expressions are temporary, named result sets created within the execution scope of a single SQL Statement using a WITH clause.

They act like temporary views that exist only for the duration of a specific query.

CTE Types

1. Basic CTEs

This is a single temporary result set right before the main SELECT, INSERT or UPDATE statement that makes queries much easier to read by breaking them into logical, readable blocks.

SCENARIO

Hospital management wants to review all patient visits where the total bill is greater than the average hospital bill.

QUERY

WITH AvgHospitalBill AS (
    SELECT AVG(total_bill_kes) AS avg_bill
    FROM Fact_Visits
)
SELECT v.visit_id, v.patient_id, v.total_bill_kes, a.avg_bill
FROM Visits v
CROSS JOIN AvgHospitalBill a
WHERE v.total_bill_kes > a.avg_bill;

TASK

Flag high cost visits relative to the hospital wide average by writing a CTE named AvgHospitalBill to calculate the average billing amount, then query visits exceeding that average.

2. Multiple CTEs

These allow is to define more than one temporary result set within a single SQL query. They are particularly useful when a problem requires multiple stages of data processing where each CTE represents a distinct stage.

There are two ways to use multiple CTEs.

Independent CTEs (Parallel Logic)

This approach involves defining multiple unrelated datasets to combine later into a final query e.g., by performing a JOIN or a UNION

SCENARIO

Hospital management wants to compare every individual visit's billing amount against the overall average hospital bill, while simultaneously comparing it against the overall average patient satisfaction score.

QUERY

WITH GlobalAverageBill AS (
    SELECT AVG(total_bill_kes) AS avg_hospital_bill
    FROM Visits
),
GlobalAverageSatisfaction AS (
    SELECT AVG(patient_satisfaction_score) AS avg_hospital_satisfaction
    FROM Visits
)
SELECT 
    v.visit_id,
    v.patient_id,
    v.total_bill_kes,
    ROUND(gab.avg_hospital_bill, 2) AS benchmark_avg_bill,
    v.patient_satisfaction_score,
    ROUND(gas.avg_hospital_satisfaction::numeric, 2) AS benchmark_avg_satisfaction,
    -- Compare visit bill to the independent average bill CTE
    CASE 
        WHEN v.total_bill_kes > gab.avg_hospital_bill THEN 'Above Average Bill'
        ELSE 'Below Average Bill'
    END AS billing_status
FROM Visits v
CROSS JOIN GlobalAverageBill gab
CROSS JOIN GlobalAverageSatisfaction gas;

TASK

Two separate non-dependent CTEs GlobalAverageBill which calculates the global average bill and GlobalAverageSatisfaction which calculates the average satisfaction score cross join the results against the main Visits table to benchmark every visit.

*Dependent / Chained CTEs (Sequential Logic)

This approach involves a CTE referencing a previously defined CTE in the same statement where each step refines the last.

An important caveat to note with this approach is that forward referencing. A CTE cannot reference another CTE defined after it. i.e. cte_1 can not read date from cte_2

SCENARIO

Isolate high-performing doctors and match them to their respective patient visits.

QUERY

WITH DoctorRevenue AS (
    SELECT doctor_id, SUM(total_bill_kes) AS total_revenue
    FROM Visits
    GROUP BY doctor_id
),
AverageRevenueBenchmark AS (
    SELECT AVG(total_revenue) AS avg_doctor_revenue
    FROM DoctorRevenue
)
SELECT dr.doctor_id, dr.total_revenue, arb.avg_doctor_revenue
FROM DoctorRevenue dr
CROSS JOIN AverageRevenueBenchmark arb
WHERE dr.total_revenue > arb.avg_doctor_revenue;

TASK

CTE_1 AverageRevenueBenchmark calculates total revenue per doctor and CTE_2 DoctorRevenue finds the average revenue across all doctors. A final query filters doctors above that threshold.

3. Recursive CTEs

A Recursive CTE calls itself repeatedly until it returns a result that no longer meets it's continuation condition.

It consists of two parts:

• Anchor Member - This is the base query that executes first and only once and establishes the starting dataset, baseline rows or data for the recursion. It does not reference the name of the CTE

• Recursive Member - This is the query that references the CTE itself to generate subsequent rows. It takes the output generated by the previous step, performs an operation on it, and feeds it back into the loop. It must also reference the CTE name and include a terminating condition for the loop.

SCENARIO

Generate a series of 7 consecutive dates for a weekly operational schedule report.

QUERY

WITH RECURSIVE DateSeries AS (
    -- Anchor Member: Runs once to set the Starting Point (June 1)
    SELECT DATE '2026-06-01' AS audit_date`

    UNION ALL

-- Recursive Member:  References 'DateSeries' to add 1 day iteratively.
    SELECT audit_date + INTERVAL '1 day'
    FROM DateSeries
    WHERE audit_date < DATE '2026-06-07'
)
SELECT audit_date FROM DateSeries;

TASK

A recursive CTE to increment dates day-by-day starting from June 1, 2026.

4. CTEs with Joins and Aggregations.

CTEs can also handle heavy aggregations first, and then join those summarized results cleanly with other dimension tables.

SCENARIO

A report showing each department's name, total patient visits, and total revenue generated, joined directly with department profiles.

QUERY

-- Scenario: Summarizing departmental metrics using a CTE

WITH DepartmentMetrics AS (
    SELECT 
        department_id,
        COUNT(visit_id) AS total_visits,
        SUM(total_bill_kes) AS department_revenue,
        ROUND(AVG(patient_satisfaction_score), 2) AS avg_satisfaction
    FROM Visits
    GROUP BY department_id
)
SELECT 
    d.department_name,
    dm.total_visits,
    dm.department_revenue,
    dm.avg_satisfaction
FROM DepartmentMetrics dm
INNER JOIN Dim_Departments d ON dm.department_id = d.department_id;

TASK

Aggregate visit metrics in CTE DepartmentMetrics and then INNER JOIN it with Departments table.

CTEs Vs Subqueries

Feature	Common Table Expressions (CTEs)	Subqueries (Inline / Derived Tables)
Readability	High. Defined at the top of the query block like a named variable.	Low to Moderate. Often nested deep inside WHERE, FROM, or SELECT clauses.
Reusability	Can be referenced multiple times within the main query body.	Must be re-written or duplicated if needed in multiple places.
Recursion	Supports recursion (vital for hierarchical data like organizational charts or date series).	Does not support recursion natively.
Performance	In modern PostgreSQL versions, CTEs can act as optimization fences or be automatically inlined depending on usage.	Highly optimized by the query planner as inline expressions.

SQL Subqueries

Subqueries, also called inner queries or nested queries, are queries nested inside larger SQL statements (such as SELECT , INSERT OR UPDATE)

Subquery Types

1. Non-Correlated Subqueries

These are independent subqueries that run independently on their own before the outer query runs. They evaluate once and its result is passed up to the outer query.

SCENARIO

Find all visits where the total bill is greater than the overall average hospital bill.

*QUERY

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

TASK

2. Correlated Subqueries

These are dependent subqueries that reference columns from the outer query. These subqueries cannot run independently and must re-evaluate row-by-row for every single record processed by the outer query.

SCENARIO

Find every visit where a patient's bill is higher than the average bill for that specific department.

QUERY

SELECT v1.visit_id, v1.department_id, v1.total_bill_kes
FROM Visits v1
WHERE v1.total_bill_kes > (
    SELECT AVG(v2.total_bill_kes)
    FROM Visits v2
    WHERE v2.department_id = v1.department_id
);

TASK

3. Scalar Subqueries

These are subqueries that return exactly one row and one column and can be used anywhere a single value is expected as a result.

SCENARIO

Display every visit's total bill alongside the absolute maximum hospital bill as a static comparison column.

QUERY

SELECT visit_id, total_bill_kes,
       (SELECT MAX(total_bill_kes) FROM Visits) AS max_hospital_bill
FROM Visits;

TASK

4. Nested Subqueries

These are subqueries nested deep inside other subqueries.

Subquery Operators.

1. IN This operator checks if a value matches any value returned by a list or a subquery.

QUERY

-- Find visits for patients who are from specific high-volume counties
SELECT * FROM Visits 
WHERE patient_id IN (SELECT patient_id FROM Patients WHERE county = 'Nairobi');

2. `EXISTS`
Tests for the *existence* of any rows returned by the subquery and returns ***true*** as soon as it finds at least one matching row. (very efficient for correlated checks)

*QUERY*

-- Find departments that have at least one emergency admission
SELECT * FROM Departments d
WHERE EXISTS (
SELECT 1 FROM Visits v
WHERE v.department_id = d.department_id AND v.admission_type = 'Emergency'
);

3. `ANY / ALL`
Compares a value to a set of values returned by a subquery. `> ANY` means greater than the minimum while `> ALL` means greater than the maximum.

#### Placement of Subqueries (`SELECT`, `FROM`, `WHERE`)

1. `WHERE` **Clause (Filtering)**
This is the most common use case scenario. Subqueries in the `WHERE` clause act as dynamic filters based on aggregate calculations or cross-table criteria e.g., in filtering visits with above-average length of stay

2. `FROM` **Clause (Derived Tables / Inline Views)**
A subquery in the `FROM` clause results in the treatment of the result set of that subquery as a temporary table that your main query can select from or join against. 

*EXAMPLE*

Calculate the average department bills first, then categorize them in the outer query.

*QUERY*

SELECT dept_summary.department_id, dept_summary.avg_bill
FROM (
SELECT department_id, AVG(total_bill_kes) AS avg_bill
FROM Visits
GROUP BY department_id
) AS dept_summary
WHERE dept_summary.avg_bill > 10000;

3. `SELECT` **Clause (Computed Columns)**
These allow for the attachment of scalar contextual calculations or aggregate metrics directly onto every individual row output e.g., in adding a column showing the global average bill nest to every individual visit row.