Window functions, Joins, CTES and Subqueries in SQL

Introduction

Structured Query Language (SQL) is a programming language used to store, retrieve, manage, and analyze data in relational databases. This article explains four important SQL concepts which are Window Functions, SQL Joins, CTEs, and Subqueries. Each concept will be explained using practical examples to demonstrate how SQL can solve real world data management and analysis problems.

SQL Window functions

Window functions perform calculations across a set of table rows that are related to the current row, without collapsing the result into a single summary row unlike group by which replaces detailed rows with an aggregated summary ,window functions keep your original row details intact while adding the computed summary as a new column beside them.
Types and functions of window functions are listed below;

• ROW_NUMBER() - assigns a unique sequential number to each row in a specified order. It is useful for identifying the latest admission, selecting one record per patient, or creating ranked lists.

select 
    patient_id,
    visit_date,
    row_number() over(
        partition by patient_id
        order by visit_date desc, ward_id desc
    ) as RowNum
from visits;

• RANK(), - assigns rankings according to a specified value. Rows with equal values receive the same rank, and the next rank reflects the number of tied rows

select
    patient_id,
    total_bill_Kes,
   rank() over(
        order by total_bill_Kes desc
    ) as BillRank
from visits;

• DENSE_RANK()- ranks rows with equal values equally but does not skip the next rank.

select
patient_id,
total_bill_kes,
    dense_rank() over (
        order by total_bill_kes desc
    ) as DenseBillRank
from visits;

• NTILE()- divides ordered rows into approximately equal groups, where n is the number of groups required.

select
    patient_id,
    total_bill_kes,
    ntile(4) over(
        order by total_bill_kes desc
    ) AS BillingGroup
from visits;

• LAG()- retrieves a value from a previous row without requiring a self-join. It is useful for comparing a patient's current bill with an earlier bill.

select 
    patient_id,
    visit_date,
    total_bill_kes,
    lag(total_bill_kes) over(
        partition by patient_id
        order by visit_date, admission_type
    ) as PreviousBill
from visits;

• FIRST_VALUE()- returns the value from the first row in a specified window order.

select
    diagnosis_id,
    patient_id,
    total_bill_kes,
    first_value(total_bill_kes) over(
        partition by visits.department_id
        order by total_bill_kes desc, admission_type
        rows between unbounded preceding
        and unbounded following
    ) as HighestDepartmentBill
from visits;

• LAST_VALUE(), - returns the value from the last row in the specified window frame. The frame must be selected carefully because the default frame may end at the current row or its peers rather than at the end of the entire partition.

 select
    diagnosis_id,
    patient_id,
    total_bill_kes,
    last_value(total_bill_kes) over(
        partition by visits.department_id
        order by total_bill_kes desc, admission_type
        rows between unbounded preceding
        and unbounded following
    ) as HighestDepartmentBill
from visits;

SUM(),- adds value across a specified row

select 
department_id,
total_bill_kes,
sum (total_bill_kes) over (partition by department_id)as departmental_bill
from visits;

AVG(),- calculates the average of values in a column

select 
department_id,
total_bill_kes,
avg (total_bill_kes) over (partition by department_id)as departmental_bill
from visits;

COUNT(),- counts rows or non nulls values in a column

select 
department_id,
total_bill_kes,
count (total_bill_kes) over (partition by department_id)as departmental_bill
from visits;

MIN(),- shows the lowest value in a column

select 
department_id,
total_bill_kes,
min(total_bill_kes) over (partition by department_id)as departmental_bill
from visits;

MAX()- shows the highest value in a column

select 
department_id,
total_bill_kes,
max(total_bill_kes) over (partition by department_id)as departmental_bill
from visits;

How to use OVER,PARTIOTION BY and ORDER BY

OVER clause defines the set of rows used by a window function ,It can specify how rows are divided into groups ,how they are ordered , and which which rows are included in a calculation. PARTITION BY divides rows into groups before a window function performs its calculation .Each group is calculated independently ,but the individual rows remain visible. ORDER BY determines the order in which rows are processed by functions such as row_number, rank, lag and running totals.

SQL joins

Used to combine two or more table tables of related columns.
Inner join- Returns only records that are matching in both tables.

select procedure_id,procedure_name
from procedures p 
inner join visits
on procedure_id= visits.procedure_id;
select * from procedures;

left join

it is also known as left outer join,it returns every row from the left table and matching rows from the right table.When no match exists ,columns from the right table column contains NULL

select procedure_id,procedure_name
from procedures p 
left join visits
on procedure_id= visits.procedure_id;
select * from procedures;

right join

it is also known as right outer join. It returns every row from the the right table and matching rows from the left table

Full outer join

Returns all matching and unmatching rows from both tables ,where a match is absent the columns from the other table contains NULL

select procedure_id,procedure_name
from procedures p 
full outer join visits
on procedure_id= visits.procedure_id;
select * from procedures;

Cross join

Returns every possible combination of rows from two tables ,it does not require a matching condition.

select procedure_id,procedure_name
from procedures p 
full outer join visits
on procedure_id= visits.procedure_id;
select * from procedures;

Self join

It joins a table to itself .It is used when records in one table have relationships with other records in the same table

Common Table Expressions (CTEs)

CTEs are named temporary results set defined using the *with * keyword . it exists for the duration of the SQL statement and can make complex queries easier to read , organize and maintain

Basic CTEs

these define set that can be referenced by the main query

with High_bill as (
select total_bill_kes, patient_id
from visits v 
where total_bill_kes > 30000)
select *
from high_bill;

Multiple CTEs

Multiple CTEs allow several named result sets to be defined within the same WITH clause. Later CTEs can reference earlier CTEs.

with visit_counts as (
    select department_id, count(*) as visits_count from visits group by department_id
),
department_bills as (
    select department_id, sum(total_bill_kes) as total_bills from visits group by department_id
)
select d.department_name, vc.visits_count, db.total_bills
from departments d
left join visit_counts vc on d.department_id = vc.department_id
left join department_bills db on d.department_id = db.department_id;

Recursive CTEs

A recursive CTE repeatedly refers to its own result set to process hierarchical or sequential data. It commonly contains an anchor query, which establishes the starting records, and a recursive query, which identifies the next level.

CTEs with joins, Aggregations and subqueries

CTEs can prepare data before it is combined with another table which separates the calculation from the final reporting query
CTEs with Aggregations
They can also summarize and filter resulting groups
CTEs and subqueries can produce similar results .Neither is automatically faster in every situation ;performance depends on database engine , query structure and available indexes

SQL subqueries

A subquery is an SQL query placed inside another SQL statement. It provides a result that the outer query uses to filter records ,calculate values, or create an intermediate table

• Scalar subqueries It returns a single value : one row and one column. It can be used in a select ,where, or another expression where a single value is expected

select 
patient_id,
diagnosis_id,
total_bill_kes,
(select avg (total_bill_kes)
from visits) as average_bills
from visits;

• Non-correlated subqueries Operates independently of the outer query .It does not refer to the columns of the outer query and can be evaluated independently by the database engine

select 
patient_id,
diagnosis_id,
total_bill_kes
from visits
where total_bill_kes > (select avg (total_bill_kes)
from visits) ;

• Correlated subqueries
  Refers to a value from the the outer query. Its results depends on the outer row being evaluated by the outer query.

• Nested Subqueries
  It contains a subquery inside another subquery, this allows multiple conditions to be evaluated in stages

• Subqueries using IN
  IN checks whether a value matches any value in a list returned by a subquery

• Subqueries using EXISTS
  EXISTS checks whether a subquery returns at least one row .It is particularly useful when the question is whether a related record exists

• Subqueries using ANY
  ANY compares a value with the values returned by a subquery. The condition is true if it is satisfied for at least one value
  Subqueries using ALL
  

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT AVG(total_bill_kes)
        FROM visits
    ) AS average_bill
FROM visits;

SELECT
    visit_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM visits
    WHERE department_id = 3
);

ALL compares a value with every value returned by a subquery. The condition is true only when the comparison holds for all the returned values

SELECT
    x.department_id,
    x.avg_bill
FROM (
    SELECT
        department_id,
        AVG(total_bill_kes) AS avg_bill
    FROM visits
    GROUP BY department_id
) x
WHERE x.avg_bill > 5000;
SELECT
    visit_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
    SELECT total_bill_kes
    FROM visits
    WHERE department_id = 3
);

• Subqueries in the select clause SELECT clause can calculate an additional value for each row in the outer query

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT AVG(total_bill_kes)
        FROM visits
    ) AS average_bill
FROM visits;

Subqueries in the WHERE clause
WHERE clause filter records according to a value or set of values returned by another query

Conclusion

In conclusion, Window functions in SQL ,joins, CTEs and subqueries are important tools for analyzing relational data. Window functions calculate rankings, totals, averages, and comparisons while preserving individual records. Joins combine related information from different tables ,While CTEs organize complex queries into readable stages .Subqueries allow one query to use the results of another query to filter, compare, or calculate information.