Introduction

SQL (Structured Query Language) is one of the most important tools used by data analysts to retrieve, organize, transform, and analyze data stored in relational databases. PostgreSQL is a powerful open source relational database management system that supports advanced SQL features for solving real-world business problems.
This guide covers four skills that take you from basic queries to real analysis in PostgreSQL: window functions, joins, common table expressions (CTEs) and subqueries.

1. SQL Window Functions

A window function performs a calculation across a group of related rows while retaining the individual rows in the output.

Imagine that a hospital manager wants to rank hospital visits according to their total bills. A normal aggregation could calculate the total or average bill, but it might combine multiple visits into one result. A window function can calculate a ranking while keeping each visit visible.

OVER()

The OVER() clause tells PostgreSQL that a function should operate as a window function. It defines the rows that the function should consider and, where relevant, their ordering and window frame.

Example:

SELECT
    visit_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER () AS hospital_average_bill
FROM visits_fact;

What does this query do?

AVG(total_bill_kes) calculates the average hospital bill.
OVER() makes the average a window calculation.
Because no partition is specified, all rows belong to the same window.
Every visit remains in the output.
The expected result is one row per visit, with the hospital-wide average displayed alongside each bill.

PARTITION BY

PARTITION BY divides the rows into separate groups, called partitions. A window function then operates within each partition rather than across the entire dataset.

Suppose the hospital manager wants to calculate the average bill separately for each department.

SELECT
    department_id,
    visit_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_average_bill
FROM visits_fact;

What does this query do?
It groups visits into department-specific windows and calculates the average bill for each department.
For example, a visit in one department is compared with the average bill of visits in that same department, not with visits from every other department.

ORDER BY inside a window

Within OVER(), ORDER BY determines the sequence in which rows are considered. Its meaning depends on the function being used.

For ranking functions, it determines the ranking order. For functions such as LAG() and LEAD()
, it determines which row is considered previous or next.

Example:

SELECT
    visit_id,
    total_bill_kes,
    ROW_NUMBER() OVER (
        ORDER BY total_bill_kes DESC
    ) AS bill_position
FROM visits_fact;

The largest bill receives position 1, the next largest receives position 2, and so on.

Adding DESC means descending order, from highest to lowest. Using ASC means ascending order, from lowest to highest.

Window frames

A window frame defines the subset of rows within a partition that a window function considers for the current row. Frames are particularly useful with aggregate window functions and value functions.

For example:

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

This calculates a running total of hospital bills in date order.
The frame:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means to include every row from the beginning of the ordered partition through the current row.

A. ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within a partition.
Hospital use case: Identify the highest-billed visits within the hospital or within each department.

SELECT
    visit_id,
    total_bill_kes,
    ROW_NUMBER() OVER (
        ORDER BY total_bill_kes DESC, visit_id
    ) AS row_number
FROM visits_fact;

B. RANK()

RANK() assigns the same rank to rows with equal ordering values. It leaves gaps in the rankings after ties.

SELECT
    visit_id,
    total_bill_kes,
    RANK() OVER (
        ORDER BY total_bill_kes DESC
    ) AS bill_rank
FROM visits_fact;

C. DENSE_RANK()

DENSE_RANK() also assigns equal ranks to tied values, but it does not leave gaps.

SELECT
    visit_id,
    total_bill_kes,
    DENSE_RANK() OVER (
        ORDER BY total_bill_kes DESC
    ) AS dense_bill_rank
FROM visits_fact;

Expected ranks for the same illustrative data are 1, 2, 2, and 3.
This is useful when the hospital wants to group visits into distinct bill levels without skipping ranking numbers.

D. NTILE()

NTILE(n) divides ordered rows into approximately equal-sized groups and assigns each group a number from 1 to n.

For example, NTILE(4) divides the ordered visits into four groups, often called quartiles.

SELECT
    visit_id,
    total_bill_kes,
    NTILE(4) OVER (
        ORDER BY total_bill_kes
    ) AS bill_quartile
FROM visits_fact;

The lowest-billed visits are placed in the first group, and the highest-billed visits are placed in the fourth group.
Hospital use case: Classify visits into relative bill groups for exploratory financial analysis. The groups are based on the distribution of visits, not on fixed KSh thresholds.
If the row count is not divisible evenly by four, group sizes may differ by one row.

E. LAG()

LAG() retrieves a value from a preceding row.

SELECT
    visit_date,
    visit_id,
    total_bill_kes,
    LAG(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
    ) AS previous_visit_bill
FROM visits_fact;

The result includes the previous visit's bill according to the specified ordering. The first row has no preceding row, so the result is NULL unless a default value is supplied.

F. LEAD()

LEAD() retrieves a value from a following row.

SELECT
    visit_date,
    visit_id,
    total_bill_kes,
    LEAD(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
    ) AS next_visit_bill
FROM visits_fact;

The final row has no following row, so its next_visit_bill is normally NULL.
Hospital use case: Compare bills across chronologically ordered visits or analyze changes between daily financial totals.

G. FIRST_VALUE()

FIRST_VALUE() returns the value from the first row in the ordered window frame.

SELECT
    visit_id,
    total_bill_kes,
    FIRST_VALUE(total_bill_kes) OVER (
        ORDER BY total_bill_kes DESC, visit_id
    ) AS highest_bill
FROM visits_fact;

This displays the highest bill alongside every visit, using the specified ordering.
To find the highest bill separately for each department, add PARTITION BY department_id.

H. LAST_VALUE()

LAST_VALUE()returns the value from the last row in the current window frame, not necessarily the last row in the entire partition.

This distinction is important because the default ordered frame in PostgreSQL commonly ends at the current row's last peer. As a result, LAST_VALUE() may return the current row's value or a value from its peer group instead of the final value in the partition.

To retrieve the final bill in the entire ordered partition, specify the frame explicitly:

SELECT
    visit_id,
    total_bill_kes,
    LAST_VALUE(total_bill_kes) OVER (
        ORDER BY total_bill_kes, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS highest_bill_in_order
FROM visits_fact;

Because the order is ascending, the final value is the highest bill. The result is displayed alongside every visit.
These functions are useful when analysts need to compare a row with the first or last value in a defined sequence.

2. SQL JOINS

PostgreSQL join is used to combine columns from one (self-join) or more tables based on the values of the common columns between related tables. The common columns are typically the primary key columns of the first table and the foreign key columns of the second table.

PostgreSQL supports inner join, left join, right join, full outer join, cross join, natural join, and a special kind of join called self-join.

A.INNER JOIN

An INNER JOIN returns only rows that match the join condition in both tables.

SELECT
    v.visit_id,
    v.total_bill_kes,
    d.department_name
FROM visits_fact AS v
INNER JOIN dim_departments AS d
    ON v.department_id = d.department_id;

Explanation:

• PostgreSQL reads the visits.
• It matches each visit's department identifier with the department dimension.
• It returns only records that have a matching department. Hospital use case: Produce a report of visits associated with recognized departments. Visits without a matching department record will not appear in the result.

B. LEFT JOIN

A LEFT JOIN returns every row from the left table and any matching rows from the right table. If no right-side match exists, the right-side columns contain NULL.

SELECT
    v.visit_id,
    v.total_bill_kes,
    d.department_name
FROM visits_fact AS v
LEFT JOIN dim_departments AS d
    ON v.department_id = d.department_id;

Hospital use case: Keep every visit in the report, even if the department identifier is missing or has no matching dimension record.
This is particularly useful for data-quality audits because unmatched records remain visible.

C. RIGHT JOIN

A RIGHT JOIN returns every row from the right table and any matching rows from the left table.

SELECT
    v.visit_id,
    v.total_bill_kes,
    d.department_name
FROM visits_fact AS v
RIGHT JOIN dim_departments AS d
    ON v.department_id = d.department_id;

Hospital use case: Display every department, including departments that currently have no associated visits.
Departments without visits will have NULL in the visit columns.
A RIGHT JOIN can usually be rewritten as a LEFT JOIN by reversing the table order. Many analysts prefer LEFT JOIN because it makes the preserved table easier to identify.

D. FULL OUTER JOIN

A FULL OUTER JOIN returns all matching rows and all unmatched rows from both tables.

SELECT
    v.visit_id,
    v.department_id AS visit_department_id,
    d.department_id AS dimension_department_id,
    d.department_name,
    v.total_bill_kes
FROM visits_fact AS v
FULL OUTER JOIN dim_departments AS d
    ON v.department_id = d.department_id;

Hospital use case: Reconcile the visits table against the department dimension.

The output can reveal:

• Visits whose department has no matching dimension record.
• Departments that have no visits.
• Records where the department exists on both sides.

The unmatched side's columns will contain NULL.

E. CROSS JOIN

A CROSS JOINreturns every possible combination of rows from two tables. It does not require a matching key.

Suppose the hospital wants to prepare a reporting framework for every combination of department and patient gender.

SELECT
    d.department_name,
    p.patient_gender
FROM dim_departments AS d
CROSS JOIN (
    SELECT DISTINCT patient_gender
    FROM dim_patients
    WHERE patient_gender IS NOT NULL
) AS p;

The inner query returns the distinct non-null gender categories, and the CROSS JOIN pairs each category with every department.
If there are six departments and two distinct gender categories, the result contains twelve combinations.
Hospital use case: Create a reporting grid that can later be compared with actual visit counts.
A cross join can generate a very large result when both inputs contain many rows, so use it deliberately.

F. SELF JOIN

A self join joins a table to itself. It is useful when records in one table need to be compared with other records in that same table.

For example, suppose the analyst wants to identify repeat visits by the same patient.

SELECT
    earlier.patient_id,
    earlier.visit_id AS earlier_visit_id,
    earlier.visit_date AS earlier_visit_date,
    later.visit_id AS later_visit_id,
    later.visit_date AS later_visit_date
FROM visits_fact AS earlier
JOIN visits_fact AS later
    ON earlier.patient_id = later.patient_id
   AND earlier.visit_date < later.visit_date;

Explanation:

• Earlier and later are two aliases for the same table.
• The patient identifiers must match.
• The earlier visit date must precede the later visit date.
• The result identifies pairs of visits by the same patient on different dates.

Hospital use case: Analyze repeat visits, revisit patterns, and patient service utilization.
This query assumes patient_id exists in visits_fact. It may produce multiple pairs for a patient with several visits, because each earlier visit can be paired with multiple later visits.

3. Common Table Expressions (CTEs)

A common table expression (CTE) allows you to create a temporary result set within a query.

A CTE helps you enhance the readability of a complex query by breaking it down into smaller and more reusable parts
Parts of a CTE include

• WITH clause: Introduce the common table expression (CTE). It is followed by the name of the CTE and a list of column names in parentheses. The column list is optional and is only necessary if you want to explicitly define the columns for the CTE.
• CTE name: Specify the name of the CTE. The CTE name exists within the scope of the query. Ensure that the CTE name is unique within the query.
• AS keyword: The AS keyword indicates the beginning of the CTE definition.
• CTE query: This is a query that defines the CTE, which may include JOINs, WHERE, GROUP BY clauses, and other valid SQL constructs.
• Main query: After defining the CTE, you can reference it in the main query by its name. In the main query, you can use the CTE as if it were a regular table, simplifying the structure of complex queries.

###Basic CTE

Suppose the hospital manager wants to calculate total revenue for each department.

WITH department_revenue AS (
    SELECT
        department_id,
        SUM(total_bill_kes) AS total_revenue
    FROM visits_fact
    GROUP BY department_id
)
SELECT
    department_id,
    total_revenue
FROM department_revenue;

How it works:

• The CTE named department_revenue groups the visits by department and calculates total billing.
• The outer query retrieves the results from the CTE.
• The output contains one row per department identifier represented in the fact table.

###Multiple CTEs

A query can define more than one CTE. Each CTE can build on an earlier CTE.

Suppose the hospital wants to compare department revenue and average length of stay.

WITH department_revenue AS (
    SELECT
        department_id,
        SUM(total_bill_kes) AS total_revenue
    FROM visits_fact
    GROUP BY department_id
),
department_stay AS (
    SELECT
        department_id,
        AVG(length_of_stay_days) AS average_stay
    FROM visits_fact
    GROUP BY department_id
)
SELECT
    r.department_id,
    r.total_revenue,
    s.average_stay
FROM department_revenue AS r
JOIN department_stay AS s
    ON r.department_id = s.department_id
ORDER BY r.total_revenue DESC;

Explanation:

• department_revenue calculates total billing for each department.
• department_stay calculates average length of stay for each department.
• The final query joins the two summaries using the department identifier.
• The result provides a department-level report with both financial and operational measures.
• The example assumes that length_of_stay_days is numeric and that each department identifier is represented consistently in both CTEs.

CTEs with joins

CTEs can also make a multi-table query easier to understand.

For example, first combine visit data with department names, then summarize billing.

WITH visit_details AS (
    SELECT
        v.visit_id,
        v.total_bill_kes,
        d.department_name
    FROM visits_fact AS v
    JOIN dim_departments AS d
        ON v.department_id = d.department_id
)
SELECT
    department_name,
    SUM(total_bill_kes) AS total_revenue
FROM visit_details
GROUP BY department_name
ORDER BY total_revenue DESC;

The CTE prepares the visit details, and the outer query calculates revenue by department.

This structure is helpful when the same joined result is needed for several subsequent calculations.

SUBQUERRIES

A subquery is a query nested within another query. A subquery is also known as an inner query or nested query.

A subquery can be useful for retrieving data that will be used by the main query as a condition for further data selection.

Types of subquerries include

1. Scalar subqueries

A scalar subquery returns a single value: one row and one column. It can be used wherever a single value is expected.

*2. Non-correlated subqueries
*
A non-correlated subquery does not refer to columns from the outer query. It can be evaluated independently of the outer row.

3. Correlated subqueries

A correlated subquery references a column from the outer query. Its logic depends on the current outer row.

4. Nested subqueries

A nested subquery is a subquery placed inside another subquery. Multiple levels can be used when one calculation depends on the result of another.

Subqueries can appear in SELECT, FROM, and WHERE clauses, and they can return a single value, a list of values, or a table-like result.

Subqueries in the WHERE clause

A subquery in WHERE is used to filter rows based on another query's result.

Example:

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

The inner query calculates the average bill. The outer query keeps only visits with bills above that average.

Subqueries in the SELECT clause

A scalar subquery in SELECT can add a calculated value to the output.

SELECT
visit_id,
total_bill_kes,
(
SELECT AVG(total_bill_kes)
FROM visits_fact
) AS hospital_average_bill
FROM visits_fact;

The query returns each visit with an additional column containing the hospital-wide average bill.

Subqueries in the FROM clause

A subquery in FROM creates a table-like result that the outer query can use.

For example, calculate total billing per department and then retain departments above KSh 100,000.

SELECT
    department_id,
    total_revenue
FROM (
    SELECT
        department_id,
        SUM(total_bill_kes) AS total_revenue
    FROM visits_fact
    GROUP BY department_id
) AS department_summary
WHERE total_revenue > 100000;

The inner query calculates total revenue for each department. The outer query filters those summaries.
In PostgreSQL, a subquery in FROM requires an alias, in this case department_summary.

Conclusion

Window functions, joins, CTEs, and subqueries are essential tools for intermediate and advanced SQL analysis.

Window functions calculate rankings, running totals, comparisons, and other values while preserving row-level detail. Joins combine related records from different tables and determine which unmatched records remain in the output. CTEs organize complex queries into named steps, while subqueries allow one query to use the results of another.

For hospital data analysis, these techniques can support financial reporting, department performance comparisons, repeat visit analysis, data-quality checks, and operational reporting.