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(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), SUM(), AVG(), COUNT(), MIN(), and MAX().
