DEV Community: Frank Hahanyu

# A Practical Guide to Advanced SQL: Window Functions, Joins, CTEs, and Subqueries Using a Hospital Database

Frank Hahanyu — Fri, 16 Oct 2026 17:41:35 +0000

1. Introduction

Structured Query Language (SQL) is used to retrieve, manipulate, and analyze data stored in relational databases. In a hospital environment, SQL helps administrators understand patient visits, monitor medical departments, evaluate hospital performance, track revenue, and support operational decisions.

The hospital database used in this article is built with PostgreSQL and contains the following main tables:

Table	Purpose
`patients`	Stores patient details, ages, genders, counties, and sub-counties.
`doctors`	Stores doctors' names, specialties, genders, and years of experience.
`departments`	Stores hospital departments.
`wards`	Stores hospital ward information.
`diagnosis`	Stores diagnoses and their categories.
`procedures`	Stores medical procedures and their categories.
`visits`	Stores hospital visit records, including dates, bills, admission types, and lengths of stay.

The visits table connects the other tables using identifiers such as patient_id, doctor_id, department_id, and diagnosis_id. This structure allows information from several tables to be combined during analysis.

The examples use the actual table and column names in the supplied hospital SQL script. They assume the database tables have been created and populated successfully and that the current schema is evening.

SET search_path TO evening;

The article covers four important SQL concepts: window functions, joins, common table expressions, and subqueries. Each concept is explained through practical hospital scenarios.

2. SQL Window Functions

2.1 What are window functions?

Window functions perform calculations across a group of related rows while preserving the individual rows in the query result. Unlike ordinary aggregate functions used with GROUP BY, window functions do not normally collapse multiple records into a single row.

For example, a hospital manager may want to display every patient visit alongside the total bill for the entire hospital. A window function can calculate that total while keeping every visit visible.

2.2 Understanding `OVER()`

The OVER() clause specifies the window of rows over which a function operates. It can be used with ranking functions, navigation functions, and aggregate functions.

Example:

SELECT
    visit_id,
    patient_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER () AS hospital_total_bill
FROM visits
ORDER BY visit_id;

Explanation: The query displays every visit and its bill alongside the sum of all visit bills. Because OVER() contains no partition or ordering, the total covers the entire result set.

Expected result: The query returns 30 rows, one for each visit in the supplied dataset. Each row contains its individual bill and the same hospital-wide total.

2.3 Understanding `PARTITION BY`

PARTITION BY divides the rows into separate groups for calculation. The function then operates independently within each group.

Example:

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_total_bill
FROM visits
ORDER BY department_id, visit_id;

Explanation: Every visit is displayed with the total bill for its department. The total is repeated for each visit belonging to that department.

Hospital application: Management can compare the revenue generated by individual departments without losing the details of the visits contributing to each total.

2.4 Understanding `ORDER BY` inside a window

Within OVER(), ORDER BY determines the order in which rows are ranked or processed. It is different from the final ORDER BY, which determines the presentation order of the query results.

SELECT
    visit_id,
    visit_date,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
    ) AS running_total
FROM visits
ORDER BY visit_date, visit_id;

Explanation: The query calculates a cumulative total in visit-date order. Adding visit_id makes the ordering deterministic when multiple visits share a date.

Expected result: The running total increases as the visits are processed. It includes the current visit and all preceding visits in the defined window.

2.5 Understanding window frames

A window frame specifies which rows within a partition are included in a calculation for the current row. Common frame boundaries include:

UNBOUNDED PRECEDING: starts at the first row in the partition.
CURRENT ROW: includes the current row as a boundary.
n PRECEDING: refers to a specified number of preceding rows.
UNBOUNDED FOLLOWING: extends to the final row in the partition.

Example: running total

SELECT
    visit_id,
    visit_date,
    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
ORDER BY visit_date, visit_id;

Explanation: The frame starts with the first visit and extends through the current visit. ROWS specifies a physical row-based frame.

Example: moving average over three visits

SELECT
    visit_id,
    visit_date,
    total_bill_kes,
    ROUND(
        AVG(total_bill_kes) OVER (
            ORDER BY visit_date, visit_id
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS three_visit_average
FROM visits
ORDER BY visit_date, visit_id;

This query calculates the average bill for the current visit and up to the two preceding visits. At the beginning of the dataset, fewer than three rows may be available.

Important distinction: In PostgreSQL, an ordered window using the default frame commonly includes all rows from the beginning of the partition through the current row's peer group. For predictable running calculations, explicitly specifying ROWS is often clearer. This distinction is particularly important when several visits share the same date or ordering value.

2.6 `ROW_NUMBER()`

ROW_NUMBER() assigns a unique sequential number to each row in a window partition.

SELECT
    visit_id,
    patient_id,
    total_bill_kes,
    ROW_NUMBER() OVER (
        ORDER BY total_bill_kes DESC, visit_id
    ) AS bill_row_number
FROM visits;

Explanation: Visits are numbered from the highest bill to the lowest. The visit_id breaks ties so that the ordering is deterministic.

Expected result: The visit with the highest bill receives row number 1, the next visit receives 2, and so forth. Even if two visits have identical bills, they receive different row numbers.

Hospital application: This is useful when creating ranked lists or selecting the first visit per patient using a common table expression.

2.7 `RANK()`

RANK() assigns the same rank to rows with equal ordering values. It leaves gaps after tied ranks.

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

Explanation: Visits are ranked by bill amount. Equal bills receive equal ranks, and the next rank skips the number of tied positions.

For example, if three visits occupy positions 1, 2, and 3 but share the same bill, all three receive rank 1, and the next distinct bill receives rank 4.

Hospital application: A hospital can identify the highest-billed visits while preserving ties.

2.8 `DENSE_RANK()`

DENSE_RANK() also assigns the same rank 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;

Explanation: Visits with the same bill share a rank. The next distinct bill receives the next consecutive rank.

Comparison:

Function	Treatment of ties	Example ranks for tied top values
`ROW_NUMBER()`	Assigns a unique number to every row	1, 2, 3
`RANK()`	Same rank for ties; gaps follow	1, 1, 3
`DENSE_RANK()`	Same rank for ties; no gaps	1, 1, 2

These functions are useful when analyzing hospital bills, patient satisfaction scores, or department performance.

2.9 `NTILE()`

NTILE(n) divides an ordered result set into approximately equal groups called buckets.

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

Explanation: The visits are sorted by bill and divided into four buckets. Because the dataset contains 30 visits, the bucket sizes will be as balanced as possible: two buckets will contain eight visits and two will contain seven.

Bucket 1 contains the lowest bills, while bucket 4 contains the highest bills.

Hospital application: The hospital can segment visits into four bill-value groups for financial reporting.

2.10 `LAG()`

LAG() retrieves a value from a preceding row without requiring a self-join.

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

Explanation: Each visit is displayed alongside the bill of the preceding visit in the ordered dataset. The first row has NULL because no preceding row exists.

Hospital application: Analysts can compare consecutive visits and identify changes in bill amounts.

2.11 `LEAD()`

LEAD() retrieves a value from a subsequent row.

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

Explanation: The query displays the bill of the next visit alongside the current visit. The last row has NULL because no following row exists.

Comparison: LAG() looks backward, while LEAD() looks forward. Both are useful for comparisons between neighboring records.

2.12 `FIRST_VALUE()`

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

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    FIRST_VALUE(total_bill_kes) OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes DESC, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS highest_department_bill
FROM visits;

Explanation: The query displays each visit alongside the highest bill in its department.

Hospital application: Department managers can see the highest-billed visit while retaining the rest of the department's visit records.

2.13 `LAST_VALUE()`

LAST_VALUE() returns the value from the last row in the current window frame. It is important to define the frame carefully.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    LAST_VALUE(total_bill_kes) OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS highest_department_bill
FROM visits;

Explanation: Because the rows are ordered from the lowest bill to the highest, and the frame extends to the final row, the function returns the highest bill in each department.

Without the explicit frame ending in UNBOUNDED FOLLOWING, LAST_VALUE() may return the current row's value or the last value in a smaller frame instead of the final value in the whole department.

2.14 Aggregate window functions: `SUM()`, `AVG()`, `COUNT()`, `MIN()`, and `MAX()`

Aggregate functions summarize values. When combined with OVER(), they calculate summaries across a window while keeping individual visits visible.

A. SUM() — total bill by department

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_total
FROM visits;

The query calculates each department's total bill and repeats that value for every visit in the department.

B. AVG() — average bill by admission type

SELECT
    visit_id,
    admission_type,
    total_bill_kes,
    ROUND(
        AVG(total_bill_kes) OVER (
            PARTITION BY admission_type
        ), 2
    ) AS average_bill_for_admission_type
FROM visits;

The query calculates the average bill separately for outpatient, inpatient, and emergency visits. Each visit remains visible.

C. COUNT() — number of visits per patient

SELECT
    visit_id,
    patient_id,
    COUNT(*) OVER (
        PARTITION BY patient_id
    ) AS patient_visit_count
FROM visits
ORDER BY patient_id, visit_id;

The query counts the visits associated with each patient. A patient with multiple visits receives the same visit count on each of their rows.

D. MIN() — lowest bill by department

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    MIN(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS lowest_department_bill
FROM visits;

The query identifies the minimum bill within each department.

E. MAX() — highest bill by department

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    MAX(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS highest_department_bill
FROM visits;

The query identifies the maximum bill within each department.

Why use aggregate window functions? They are especially useful for dashboards and detailed reports where analysts need both individual visit information and group-level performance measures in the same result set.

3. SQL Joins

3.1 What are joins?

A join combines rows from two or more tables based on a related column or condition. In the hospital database, visits contains identifiers that link to patients, doctors, departments, diagnoses, and procedures.

Joins allow analysts to replace numerical identifiers with meaningful information such as patient names, department names, and diagnosis descriptions.

3.2 `INNER JOIN`

An INNER JOIN returns only rows with matching records in both tables.

SELECT
    v.visit_id,
    p.patient_name,
    v.visit_date,
    v.total_bill_kes
FROM visits v
INNER JOIN patients p
    ON v.patient_id = p.patient_id
ORDER BY v.visit_id;

Explanation: The query combines each visit with the corresponding patient record. Only visits whose patient IDs match a patient in patients appear.

Expected result: The supplied dataset has 30 visits and all referenced patient IDs exist, so this query returns 30 rows.

Hospital scenario: Retrieving a patient's name and bill for each hospital visit.

3.3 `LEFT JOIN`

A LEFT JOIN returns every row from the left table and matching rows from the right table. If there is no match, columns from the right table contain NULL.

SELECT
    p.patient_id,
    p.patient_name,
    v.visit_id,
    v.visit_date
FROM patients p
LEFT JOIN visits v
    ON p.patient_id = v.patient_id
ORDER BY p.patient_id, v.visit_date;

Explanation: Every patient is displayed, including patients without visits. If a patient has multiple visits, that patient appears once for each matching visit.

Hospital scenario: Identifying patients who have never visited the hospital.

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

This second query returns patients without a matching visit. In the supplied dataset, every patient has at least one recorded visit, so it returns no rows.

3.4 `RIGHT JOIN`

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

SELECT
    v.visit_id,
    d.department_name
FROM visits v
RIGHT JOIN departments d
    ON v.department_id = d.department_id
ORDER BY d.department_id, v.visit_id;

Explanation: Every department appears, even if it has no visits. A department without visits would have NULL in the visit columns.

Hospital scenario: Reporting all hospital departments, including departments that have not recorded any visits.

Comparison with LEFT JOIN: The result can generally be expressed using a LEFT JOIN by reversing the table order. For example, departments LEFT JOIN visits serves the same purpose as the preceding right join.

3.5 `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 registered_department_id,
    d.department_name
FROM visits v
FULL OUTER JOIN departments d
    ON v.department_id = d.department_id
ORDER BY d.department_id, v.visit_id;

Explanation: The result includes visits with matching departments, departments without visits, and visits whose department ID does not match a department record.

In the supplied dataset, every visit references an existing department and all four departments have visits. Therefore, this query produces matched visit-department rows only.

Hospital scenario: Auditing records across operational tables to detect unmatched or incomplete relationships.

3.6 `CROSS JOIN`

A CROSS JOIN returns every possible combination of rows from two tables.

SELECT
    d.department_name,
    w.ward_name
FROM departments d
CROSS JOIN wards w
ORDER BY d.department_name, w.ward_name;

Explanation: There are four departments and four wards, producing (4 \times 4 = 16) combinations.

Hospital scenario: Preparing a planning matrix showing all department-ward combinations for resource allocation. These combinations are hypothetical unless a separate assignment table confirms that a department operates in a particular ward.

3.7 `SELF JOIN`

A self join joins a table to itself. It is useful when records within the same table must be compared.

The doctors table does not contain a manager or supervisor ID, so a practical self-join example can compare doctors within the same specialty.

SELECT
    d1.doctor_name AS doctor_one,
    d2.doctor_name AS doctor_two,
    d1.doctor_specialty
FROM doctors d1
JOIN doctors d2
    ON d1.doctor_specialty = d2.doctor_specialty
   AND d1.doctor_id < d2.doctor_id;

Explanation: The query finds pairs of doctors who share a specialty. The condition d1.doctor_id < d2.doctor_id prevents a doctor from being matched with themselves and avoids listing the same pair twice.

Expected result: The supplied data contains four doctors with four different specialties, so the query returns no rows.

Hospital scenario: In a larger hospital database, this approach can identify doctors sharing specialties or compare staff records. A true supervisor hierarchy would require an appropriate relationship column, such as manager_id.

3.8 Comparing SQL joins

Join type	What it returns	Typical hospital use
`INNER JOIN`	Matching records only	Visits with valid patient records
`LEFT JOIN`	All left-side records and matches	All patients, including those without visits
`RIGHT JOIN`	All right-side records and matches	All departments, including unused departments
`FULL OUTER JOIN`	All records from both sides	Auditing unmatched records
`CROSS JOIN`	Every possible combination	Planning matrices
`SELF JOIN`	Comparisons within the same table	Finding doctors with the same specialty

Choosing the correct join is important because an inappropriate join can exclude relevant records or create unintended combinations.

4. Common Table Expressions (CTEs)

4.1 What is a CTE?

A Common Table Expression is a named temporary result defined using the WITH clause. It exists for the duration of the statement and can make complex SQL easier to read and maintain.

A CTE does not automatically create a permanent table.

4.2 Basic CTE

Scenario: Finding visits with bills greater than KES 20,000.

WITH expensive_visits AS (
    SELECT
        visit_id,
        patient_id,
        visit_date,
        total_bill_kes
    FROM visits
    WHERE total_bill_kes > 20000
)
SELECT *
FROM expensive_visits
ORDER BY total_bill_kes DESC;

Explanation: The CTE first selects visits whose bills exceed KES 20,000. The outer query retrieves and sorts those records.

Hospital application: Reviewing high-value visits for financial monitoring or administrative follow-up.

4.3 Multiple CTEs

Multiple CTEs allow a query to be divided into logical stages. A later CTE can reference an earlier one.

WITH department_revenue AS (
    SELECT
        department_id,
        SUM(total_bill_kes) AS total_revenue
    FROM visits
    GROUP BY department_id
),
department_averages AS (
    SELECT
        AVG(total_revenue) AS average_department_revenue
    FROM department_revenue
)
SELECT
    d.department_name,
    dr.total_revenue,
    da.average_department_revenue
FROM department_revenue dr
JOIN departments d
    ON d.department_id = dr.department_id
CROSS JOIN department_averages da
ORDER BY dr.total_revenue DESC;

Explanation:

department_revenue calculates the total bill for each department.
department_averages calculates the average of those department totals.
The final query displays each department's revenue alongside the overall average department revenue.

Hospital application: Management can identify departments performing above or below the average revenue level. This is a financial comparison, not a measure of clinical quality.

4.4 Recursive CTEs

A recursive CTE refers to its own result and repeatedly generates rows until its stopping condition is met. It normally contains an initial query, a recursive query, and a termination condition.

Example: generating a sequence of numbers

WITH RECURSIVE visit_day_sequence AS (
    SELECT 1 AS day_number

    UNION ALL

    SELECT day_number + 1
    FROM visit_day_sequence
    WHERE day_number < 7
)
SELECT day_number
FROM visit_day_sequence
ORDER BY day_number;

Explanation: The initial query creates day 1. The recursive part adds one to the previous number until day 7 is reached.

Expected result: Seven rows containing the numbers 1 through 7.

Hospital application: The generated sequence can support reporting templates for a seven-day monitoring period or be joined to a calendar table to show dates with no visits.

A recursive CTE can also traverse a genuine hierarchy, such as a hospital organizational chart, provided the database contains a parent-child relationship. The current doctor table does not contain such a relationship, so the sequence example demonstrates recursion without inventing one.

4.5 CTEs with joins and aggregations

CTEs can organize joins and aggregate calculations into separate stages.

WITH visit_details AS (
    SELECT
        v.visit_id,
        v.total_bill_kes,
        d.department_name,
        dg.diagnosis_name
    FROM visits v
    JOIN departments d
        ON d.department_id = v.department_id
    JOIN diagnosis dg
        ON dg.diagnosis_id = v.diagnosis_id
),
diagnosis_summary AS (
    SELECT
        department_name,
        diagnosis_name,
        COUNT(*) AS visit_count,
        SUM(total_bill_kes) AS total_billed
    FROM visit_details
    GROUP BY department_name, diagnosis_name
)
SELECT
    department_name,
    diagnosis_name,
    visit_count,
    total_billed
FROM diagnosis_summary
ORDER BY total_billed DESC;

Explanation: The first CTE combines visit, department, and diagnosis information. The second groups the records by department and diagnosis, calculating the number of visits and total bills. The final query sorts the summaries by total billed.

Hospital application: Identifying which diagnoses contribute most to each department's recorded visit volume and revenue.

4.6 CTEs compared with subqueries

Both CTEs and subqueries can break complex SQL into smaller logical operations. Their differences mainly concern organization, reuse, and readability.

Feature	CTE	Subquery
Syntax	Defined using `WITH`	Written inside another query
Readability	Often clearer for multi-stage analysis	Convenient for short, focused logic
Reuse	Can be referenced multiple times in a statement	Often repeated if needed in multiple places
Recursion	Supports recursive queries with `WITH RECURSIVE`	Does not independently provide recursive CTE functionality
Performance	Depends on the query and PostgreSQL's optimization	Depends on the query and PostgreSQL's optimization

A CTE is not automatically faster than a subquery. PostgreSQL can inline many CTEs, while materialization behavior depends on the query and explicit options. The best choice should be guided by clarity and, when necessary, execution-plan analysis.

5. SQL Subqueries

5.1 What is a subquery?

A subquery is a query placed inside another SQL statement. It can return a single value, a list of values, a set of rows, or a table-like result.

Subqueries help solve problems where one query depends on the result of another.

5.2 Scalar subqueries

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

Scenario: Finding visits with bills above the hospital's average bill.

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

Explanation: The inner query calculates the average bill across all visits. The outer query returns visits with bills above that average.

The aggregate query returns one row, making it suitable as a scalar subquery.

Hospital application: Identifying unusually expensive visits relative to the overall average. Such visits may merit review, but a high bill alone does not establish an error.

5.3 Non-correlated subqueries

A non-correlated subquery does not reference columns from the outer query. Its result is independent of the current outer row.

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM visits
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('Cardiology', 'Orthopedics')
)
ORDER BY visit_id;

Explanation: The inner query retrieves the IDs of Cardiology and Orthopedics. The outer query returns visits whose department IDs are in that set.

Hospital application: Retrieving visits from selected departments without hard-coding their numerical IDs.

5.4 Correlated subqueries

A correlated subquery references a value from the outer query. Conceptually, its result depends on the current outer row.

Scenario: Finding visits whose bill exceeds the average bill for their own department.

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

Explanation: For each outer visit, the inner query calculates the average bill for the same department. The outer query retains the visit if its bill exceeds that department-specific average.

Hospital application: Comparing financial activity fairly within departments instead of comparing every visit against a single hospital-wide average.

5.5 Nested subqueries

A nested subquery contains one subquery inside another. This can be useful when a condition depends on several stages of analysis.

Scenario: Finding visits associated with the department that has the highest total bill.

SELECT
    visit_id,
    department_id,
    total_bill_kes
FROM visits
WHERE department_id = (
    SELECT department_id
    FROM (
        SELECT
            department_id,
            SUM(total_bill_kes) AS department_total
        FROM visits
        GROUP BY department_id
        ORDER BY department_total DESC, department_id
        LIMIT 1
    ) AS highest_revenue_department
)
ORDER BY visit_id;

Explanation: The innermost query calculates total revenue for each department and selects the highest-revenue department. The enclosing query extracts that department's ID. The outer query then returns its individual visits.

The LIMIT 1 ensures the scalar comparison receives no more than one row. The department ID acts as a deterministic tie-breaker if totals are equal.

Hospital application: Retrieving detailed visit records for the department with the greatest total billed amount.

5.6 Subqueries using `IN`

IN checks whether a value matches any value in a specified list or result set.

SELECT
    visit_id,
    patient_id,
    diagnosis_id
FROM visits
WHERE diagnosis_id IN (
    SELECT diagnosis_id
    FROM diagnosis
    WHERE diagnosis_category = 'Infectious Disease'
)
ORDER BY visit_id;

Explanation: The inner query finds diagnoses classified as infectious diseases. The outer query returns visits linked to those diagnoses.

Hospital application: Retrieving visits associated with infectious disease diagnoses, such as malaria, typhoid, and other diagnoses categorized as infectious in the supplied dataset.

5.7 Subqueries using `EXISTS`

EXISTS tests whether a subquery returns at least one row. It is particularly useful for checking whether a related record exists.

Scenario: Finding patients who have visited the hospital.

SELECT
    p.patient_id,
    p.patient_name
FROM patients p
WHERE EXISTS (
    SELECT 1
    FROM visits v
    WHERE v.patient_id = p.patient_id
)
ORDER BY p.patient_id;

Explanation: For each patient, the subquery checks for at least one visit linked to that patient. The query returns patients for whom a match exists.

Hospital application: Producing a list of patients with recorded hospital visits.

The opposite condition, NOT EXISTS, can identify patients without any recorded visits:

SELECT
    p.patient_id,
    p.patient_name
FROM patients p
WHERE NOT EXISTS (
    SELECT 1
    FROM visits v
    WHERE v.patient_id = p.patient_id
);

In the supplied data, this second query returns no rows because every listed patient has a visit.

5.8 Subqueries using `ANY`

ANY compares a value with the results of a subquery and returns true when the comparison succeeds for at least one returned value.

Scenario: Finding visits whose bill exceeds at least one bill from an outpatient visit.

SELECT
    visit_id,
    admission_type,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM visits
    WHERE admission_type = 'Outpatient'
)
ORDER BY total_bill_kes DESC;

Explanation: A visit is returned if its bill is greater than at least one outpatient bill. This does not mean its bill must exceed every outpatient bill.

For a nonempty set of outpatient bills, > ANY effectively means that the bill exceeds the minimum value in that set, subject to SQL's NULL comparison rules.

Hospital application: Finding visits that are more expensive than at least one outpatient visit.

5.9 Subqueries using `ALL`

ALL requires a comparison to be true for every value returned by the subquery.

Scenario: Finding visits whose bills exceed every outpatient bill.

SELECT
    visit_id,
    admission_type,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
    SELECT total_bill_kes
    FROM visits
    WHERE admission_type = 'Outpatient'
)
ORDER BY total_bill_kes DESC;

Explanation: A visit is returned only when its bill exceeds every outpatient bill in the subquery result.

Hospital application: Identifying visits that are more expensive than all outpatient visits, which can help administrators investigate high-cost admissions or emergencies.

Comparison: > ANY requires the bill to exceed at least one value; > ALL requires it to exceed every value. The two conditions can therefore produce very different results.

5.10 Using subqueries in the `SELECT` clause

A scalar subquery can calculate a value to display alongside each row.

SELECT
    v.visit_id,
    v.total_bill_kes,
    (
        SELECT AVG(total_bill_kes)
        FROM visits
    ) AS hospital_average_bill
FROM visits v
ORDER BY v.visit_id;

Explanation: Every visit is displayed alongside the same hospital-wide average bill.

Hospital application: Providing context for individual bills in a detailed financial report.

5.11 Using subqueries in the `FROM` clause

A subquery in the FROM clause acts as a derived table. PostgreSQL requires it to have an alias.

SELECT
    department_summary.department_id,
    department_summary.total_revenue
FROM (
    SELECT
        department_id,
        SUM(total_bill_kes) AS total_revenue
    FROM visits
    GROUP BY department_id
) AS department_summary
WHERE department_summary.total_revenue > 0
ORDER BY department_summary.total_revenue DESC;

Explanation: The inner query calculates total revenue per department. The outer query filters the resulting department summaries.

Hospital application: Creating a department-level financial report using an intermediate aggregated result.

5.12 Using subqueries in the `WHERE` clause

Subqueries in WHERE filter records based on values calculated or selected by another query.

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM visits
)
AND length_of_stay_days > 0
ORDER BY total_bill_kes DESC;

Explanation: The query selects visits whose bills exceed the hospital-wide average and whose recorded length of stay is greater than zero.

Hospital application: Reviewing higher-billed visits that involved an overnight or other recorded inpatient stay. The dataset's length_of_stay_days field should be interpreted according to the hospital's recording practices.

5.13 Comparing subquery types

Type	Main characteristic	Example use
Scalar	Returns one value	Compare a bill with the overall average
Non-correlated	Does not reference the outer query	Select visits from chosen departments
Correlated	References the outer query	Compare a visit with its department average
Nested	Contains another subquery	Find visits from the highest-revenue department
`IN`	Tests membership in a result set	Select infectious disease visits
`EXISTS`	Checks whether a matching row exists	Find patients with visits
`ANY`	Requires at least one comparison to succeed	Exceed at least one outpatient bill
`ALL`	Requires every comparison to succeed	Exceed every outpatient bill

Subqueries can make SQL expressive and powerful. However, equivalent joins, CTEs, or window functions may sometimes be easier to understand or more suitable for a particular performance requirement.

6. Applying the Concepts to Real Hospital Analysis

The four topics can be combined to solve more complex operational questions.

6.1 Ranking the highest-billed visit in each department

WITH ranked_visits AS (
    SELECT
        v.visit_id,
        v.visit_date,
        p.patient_name,
        d.department_name,
        v.total_bill_kes,
        ROW_NUMBER() OVER (
            PARTITION BY v.department_id
            ORDER BY v.total_bill_kes DESC, v.visit_id
        ) AS department_rank
    FROM visits v
    JOIN patients p
        ON p.patient_id = v.patient_id
    JOIN departments d
        ON d.department_id = v.department_id
)
SELECT *
FROM ranked_visits
WHERE department_rank = 1
ORDER BY department_name;

This query combines joins, a CTE, and ROW_NUMBER(). It first enriches each visit with the patient and department names, then ranks visits by bill within each department. Finally, it returns one highest-billed visit per department.

If multiple visits share the highest bill, ROW_NUMBER() selects one of them according to the visit_id tie-breaker. To return every visit tied for the highest bill, a ranking function such as RANK() can be used instead.

6.2 Calculating revenue and running revenue over time

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_revenue
FROM visits
ORDER BY visit_date, visit_id;

This query combines the ordering and window-frame concepts to track cumulative billed amounts over the recorded period. It can support financial trend analysis and help administrators see how recorded revenue accumulates over time.

6.3 Finding patients with repeated visits

SELECT
    p.patient_id,
    p.patient_name,
    COUNT(v.visit_id) AS number_of_visits,
    SUM(v.total_bill_kes) AS total_billed
FROM patients p
JOIN visits v
    ON v.patient_id = p.patient_id
GROUP BY p.patient_id, p.patient_name
HAVING COUNT(v.visit_id) > 1
ORDER BY number_of_visits DESC, total_billed DESC;

This query uses an inner join and aggregation to identify patients with more than one recorded visit. It also calculates the total amount billed across their visits.

The result can help the hospital understand repeat service utilization. It should not be interpreted as proof of a medical condition or an avoidable readmission.

6.4 Comparing each visit with its department's average

SELECT
    v.visit_id,
    d.department_name,
    v.total_bill_kes,
    ROUND(
        AVG(v.total_bill_kes) OVER (
            PARTITION BY v.department_id
        ), 2
    ) AS department_average_bill,
    v.total_bill_kes
      - AVG(v.total_bill_kes) OVER (
            PARTITION BY v.department_id
        ) AS difference_from_average
FROM visits v
JOIN departments d
    ON d.department_id = v.department_id
ORDER BY d.department_name, v.visit_id;

This query uses a join and aggregate window functions to display each visit's bill, its department's average bill, and the difference between the two. A positive difference indicates a bill above the department average, while a negative difference indicates a bill below it.

7. Best Practices and Common Mistakes

Use meaningful aliases. Short aliases such as v, p, and d make joins easier to read when used consistently.
Check join conditions carefully. Missing or incorrect join conditions can create duplicate combinations and inflate revenue calculations.
Distinguish grouped aggregates from window aggregates. GROUP BY normally reduces the result to groups, whereas a window aggregate preserves individual rows.
Specify deterministic ordering when ranking matters. Add a unique tie-breaker such as visit_id when using ROW_NUMBER().
Define window frames explicitly when necessary. This is especially important for running totals, moving averages, and LAST_VALUE().
Understand NULL values. Missing values can affect comparisons, aggregates, and subquery conditions. COUNT(column) ignores NULL, whereas COUNT(*) counts rows.
Use EXISTS when checking for related records. It expresses existence checks clearly and avoids multiplying outer rows as a join can do.
Do not assume CTEs are always faster. Performance depends on the SQL statement, data distribution, indexes, and PostgreSQL's query plan.
Validate results before reporting. Check row counts, aggregate totals, and unexpected duplicates before using SQL outputs in dashboards.
Protect patient information. Use only the patient details needed for a report and follow applicable access-control and privacy requirements.

8. Conclusion

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

Window functions provide rankings, comparisons, running totals, moving averages, and group-level calculations while retaining individual visit records. Joins combine related hospital data, enabling analysts to connect visits with patients, doctors, departments, and diagnoses. CTEs organize complex SQL into readable stages and support recursive operations. Subqueries allow one query to depend on values or records returned by another.

When applied to the hospital dataset, these techniques support practical tasks such as identifying high-billed visits, comparing department revenue, examining repeat visits, analyzing trends over time, and detecting incomplete relationships.

The main objective is not merely to write SQL that executes. It is to write SQL that answers a meaningful operational question, produces accurate results, and communicates those results in a form that supports responsible hospital decision-making.

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

Frank Hahanyu — Sat, 29 Aug 2026 20:45:50 +0000

Introduction

Excel is a data entry tool, but its far more than that. It's a versatile workspace for cleaning, analyzing and visualizing data. In Data analytics, excel is all about transforming raw data into clear, actionable insights for decision-making. While specialized coding tools exist, Excel remains the primary operational engine for businesses worldwide. This guide walks you through the foundational tools, cleaning workflows, core formulas, and reporting features needed to turn messy datasets into professional analysis.

Gettting Familiar with the interface

Before writing formulas, you need to understand where everything lives. Excel’s interface centers around the top ribbon and grid system.

The Excel Ribbon Breakdown

Home: Your formatting hub for numbers, cell alignments, styles, and basic edits.
Insert: Adds visual structure, including tables, PivotTables, charts, and shapes.
Page Layout: Controls print boundaries, margins, gridlines, and page orientation.
Formulas: Houses the full function library, auditing tools, and defined names.
Data: The analytics engine. Contains sorting, filtering, deduplication, text tools, and Power Query.
Review: Manages spellcheck, comments, sheet protection, and collaboration rules.
View: Customizes workbook views, freezes panes, and toggles gridlines or formula bars.

Structural Grid Basics
Rows: Horizontal lines identified by numbers(1, 2, 3...).
Columns: Vertical sections identified by letters (A, B, C...).
Cells: The intersection of a row and column (e.g., B4).
Name Box: Displays the active cell reference.
Formula Bar: Shows the underlying value or calculation inside the selected cell.

Managing Data Types & Basic Formatting

Excel treats different data types in specific ways. Alignments and formatting dictate how calculations run under the hood.

Common Data Types

Text: Left-aligned by default. Used for descriptions, IDs, or labels.
Numbers: Right-aligned by default. Used for standard numeric calculations.
Currency/Dates: Formatted numbers displaying currency symbols or standard calendar dates (YYYY-MM-DD).
Percentages: Decimal values displayed as parts of 100 (e.g., 0.05formatted as 5%).
Pro Tip: If a number is left-aligned, Excel is treating it as text, which will cause formulas like SUM to ignore it!

Organizing Data: Tables, Sorting, & Filtering

Structured data allows for quick exploration without modifying original values permanently.
Converting Ranges to Tables (CTRL + T)
Converting a raw range into an Excel Table enables structured references, alternating row colors, and auto-expanding ranges.

Select your data range.
Go to Insert > Table (or press CTRL + T).
Ensure "My table has headers" is checked.

Sorting & Filtering

Sorting: Rearrange rows alphabetically (A-Z), numerically (Smallest to Largest), or by cell color (Data > Sort).
Filtering: Click Data > Filter to enable dropdown arrows on headers. Use these to isolate specific criteria or filter by blank values.

The Data Cleaning Workflow

Clean data is the foundation of accurate analysis. Always keep an untouched raw copy of your data before applying these steps.
Essential Data Cleaning Techniques

Handling Missing Values: Use Data > Filter to isolate blank cells, or highlight them via Find & Select > Go To Special > Blanks.
Removing Duplicates: Select your table range, go to Data > Remove Duplicates, and choose key columns.
Trimming Extra Spaces: Standardize text containing accidental leading/trailing spaces using=TRIM(cell).
Standardizing Text Case: Clean text inconsistencies using=UPPER(),=LOWER(), or =PROPER().
Find & Replace(CTRL + H): Swap out erroneous values, currency symbols, or typos across thousands of rows instantly.

Core Excel Functions

Formulas perform manual calculations, while built-in functions execute predefined logic. All entries must begin with an equals sign (=).

Basic Aggregations

SUM(range): Adds all numeric values in a range.
AVERAGE(range): Calculates the arithmetic mean.
COUNT(range): Counts cells containing numbers.
COUNTA(range): Counts non-empty cells (numbers and text).
COUNTBLANK(range): Counts empty cells.
MIN(range) / MAX(range): Returns the lowest or highest value.

Date & Time Calculations

TODAY() / NOW(): Inserts dynamic current date or timestamp.
YEAR(), MONTH(),DAY(): Extracts individual components from a date cell.
DATEDIF(start_date, end_date, unit): Calculates time elapsed in days ("D"), months ("M"), or years ("Y").
NETWORKDAYS(start_date, end_date): Counts working days excluding weekends.

Summarizing Data with PivotTables & Charts

PivotTables allow you to slice, aggregate, and analyze thousands of rows without writing complex formulas.

Creating a PivotTable

Click inside your clean dataset and select Insert > PivotTable.
Choose to place it in a New Worksheet.
Drag fields into the four main zones:

Filters: Global data boundaries.
Columns: Cross-tabulation categories.
Rows: Primary groupings down the left side.
Values: Numeric metrics to sum, average, or count.

Visualizing with Pivot Charts
To transform summary tables into visual reports:

Click anywhere inside your PivotTable.
Go to PivotTable Analyze > PivotChart (orInsert > PivotChart).
Select a bar, column, or line visualization that best represents your metrics.

Conclusion

Mastering data analytics in Excel is a step-by-step process. Once you get comfortable cleaning raw datasets and building basic summaries, explore advanced analytics tools.

GitHub Project :Understanding git work flow

Frank Hahanyu — Sun, 23 Aug 2026 03:49:13 +0000

INTRODUCTION

My first week at LuxDev Academy as a beginner in the tech field was awesome since all tutors gave us hands on tutoring , we covered installation of apps and understanding coding languages. For today I'll cover git workflow and all its components .

Version control can feel overwhelming when you first star writing code or managing data projects and for tools like git , they're designed to track changes in your projects over time using commands like git_add, git_commit, and git_push often feel like magic spells rather than logical steps.

To master or understand git you only need to understand the stages a file goes through during construction wich is :

1.The Working Directory

This is where you do the work and it consists of the actual files on your computer's files system inside your project folder. In her when you create a new file or modify an existing code those changes only exist on your working directory, meaning git only notices that changes have occurred but it doesn't track or save them automatically and for that to happen we use git status

2.The Staging Area

The staging area acts as the intermediate step between your actual workspace and your saved history.
Git selectively lets you choose which modified files belong together in the next check point . you stage a file changes from the working directory by inputting git add command , then you stage a specific file git add filename.py then stage all modified files at once git add

3.The Commit and Push

After the staging area we go ahead and commit to record those specific changes along with a clear descriptive message explaining what we updated( commit acts as a restore point).This is the commit prompt with a descriptive message i.e analysis data: git commit -m "add data analysis data"
In the push section , all your commits exist on your local computer but pushing takes all the commits and uploads them to a remote repository on GitHub and this is how you push it git push origin main

Conclusion

Git gives you full control over how your project's history is recorded, organized and shared. You need to be keen with how you key in your prompts cause once you miss a step a lot of errors pile up and with that, once you understand these four stages then you won't have a problem working with Git in your work developments.

DEV Community: Frank Hahanyu

# A Practical Guide to Advanced SQL: Window Functions, Joins, CTEs, and Subqueries Using a Hospital Database

1. Introduction

2. SQL Window Functions

2.1 What are window functions?

2.2 Understanding OVER()

2.3 Understanding PARTITION BY

2.4 Understanding ORDER BY inside a window

2.5 Understanding window frames

2.6 ROW_NUMBER()

2.7 RANK()

2.8 DENSE_RANK()

2.9 NTILE()

2.10 LAG()

2.11 LEAD()

2.12 FIRST_VALUE()

2.13 LAST_VALUE()

2.14 Aggregate window functions: SUM(), AVG(), COUNT(), MIN(), and MAX()

3. SQL Joins

3.1 What are joins?

3.2 INNER JOIN

3.3 LEFT JOIN

3.4 RIGHT JOIN

3.5 FULL OUTER JOIN

3.6 CROSS JOIN

3.7 SELF JOIN

3.8 Comparing SQL joins

4. Common Table Expressions (CTEs)

4.1 What is a CTE?

4.2 Basic CTE

4.3 Multiple CTEs

4.4 Recursive CTEs

4.5 CTEs with joins and aggregations

4.6 CTEs compared with subqueries

5. SQL Subqueries

5.1 What is a subquery?

5.2 Scalar subqueries

5.3 Non-correlated subqueries

5.4 Correlated subqueries

5.5 Nested subqueries

5.6 Subqueries using IN

5.7 Subqueries using EXISTS

5.8 Subqueries using ANY

5.9 Subqueries using ALL

5.10 Using subqueries in the SELECT clause

5.11 Using subqueries in the FROM clause

5.12 Using subqueries in the WHERE clause

5.13 Comparing subquery types

6. Applying the Concepts to Real Hospital Analysis

6.1 Ranking the highest-billed visit in each department

6.2 Calculating revenue and running revenue over time

6.3 Finding patients with repeated visits

6.4 Comparing each visit with its department's average

7. Best Practices and Common Mistakes

8. Conclusion

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

Introduction

Gettting Familiar with the interface

Managing Data Types & Basic Formatting

Organizing Data: Tables, Sorting, & Filtering

The Data Cleaning Workflow

Core Excel Functions

Summarizing Data with PivotTables & Charts

Conclusion

GitHub Project :Understanding git work flow

INTRODUCTION

1.The Working Directory

2.The Staging Area

3.The Commit and Push

Conclusion

2.2 Understanding `OVER()`

2.3 Understanding `PARTITION BY`

2.4 Understanding `ORDER BY` inside a window

2.6 `ROW_NUMBER()`

2.7 `RANK()`

2.8 `DENSE_RANK()`

2.9 `NTILE()`

2.10 `LAG()`

2.11 `LEAD()`

2.12 `FIRST_VALUE()`

2.13 `LAST_VALUE()`

2.14 Aggregate window functions: `SUM()`, `AVG()`, `COUNT()`, `MIN()`, and `MAX()`

3.2 `INNER JOIN`

3.3 `LEFT JOIN`

3.4 `RIGHT JOIN`

3.5 `FULL OUTER JOIN`

3.6 `CROSS JOIN`

3.7 `SELF JOIN`

5.6 Subqueries using `IN`

5.7 Subqueries using `EXISTS`

5.8 Subqueries using `ANY`

5.9 Subqueries using `ALL`

5.10 Using subqueries in the `SELECT` clause

5.11 Using subqueries in the `FROM` clause

5.12 Using subqueries in the `WHERE` clause