Gichanga Wangui

Posted on Oct 18

SQL CONCEPTS - Window Functions, Joins, Common Table Expressions & Subqueries

#sql #postgressql #database

1. SQL Window Functions

These are functions that are used to perform calculations. They return one output row per one input row.
Examples of Window functions include:

ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), SUM(), AVG(), COUNT(), MIN(), and MAX().

ROW NUMBER() It assigns a unique sequential number to each row within an ordered partition, starting at 1. Example:-

Row number() over(
[Partition by column1, ..]
Order by column2,...)

RANK() It assigns a rank to each row within a result set based on a specified order. When used together with Desc, it ranks rows in descending order. Example:-

SELECT
Employee_name, salary,
Rank() over (Order by salary) as Rank
FROM Employees;

DENSE_RANK() It assigns a rank to each row within a partition of a result set. It works with descending and ascending formats. Example:-

Dense_Rank() Over (
[Partition by column_name]
Order by column_name
)

NTILE() It divides an ordered result set into a specified number of equal groups where each row is assigned a group number starting from 1. Example:-

SELECT ID,
      NTILE(3) OVER (ORDER BY ID) AS GroupNumber
FROM Patients;

LAG() It allows access of data from a previous row in the same result set without using a self-join. It is used for comparison purposes. Example:- Comparing current and previous month sales.

WITH MonthlySales AS (
   SELECT
       MONTH(OrderDate) AS MonthNum,
       SUM(TotalDue) AS TotalSales
   FROM Sales.SalesOrderHeader
   WHERE YEAR(OrderDate) = 2023
   GROUP BY MONTH(OrderDate)
)
SELECT
   MonthNum,
   TotalSales,
   LAG(TotalSales, 1, 0) OVER (ORDER BY MonthNum) AS PrevMonthSales,
   TotalSales - LAG(TotalSales, 1, 0) OVER (ORDER BY MonthNum) AS SalesDiff
FROM MonthlySales;

LEAD() It allows users to access data from a subsequent row in the same result set without using a self-join. It is used when comparing values between consecutive rows, calculating differences or changes over time and looking ahead in ordered data. Example:- SELECT Company, Year, Amount, LEAD(Amount, 1) OVER ( PARTITION BY Company ORDER BY Year DESC ) AS Next_Year_Amount FROM CompanySales;
FIRST_VALUE It returns the first value in an ordered set of rows within a partition. Example:- FIRST_VALUE(column_name) OVER ( [PARTITION BY partition_column] ORDER BY order_column [ASC|DESC] )
LAST_VALUE() It returns the last value in an ordered set of rows within a partition. Example:-

LAST_VALUE(column_name) OVER (
    [PARTITION BY partition_column]
    ORDER BY order_column [ASC|DESC]
)

SUM() It is used to calculate the total sum of values in a numeric column. Example:-

SELECT SUM(column_name) 
FROM table_name;

AVG() It calculates the average of values in a numeric column Example:-

SELECT AVG(Column_name)
FROM table_name;

COUNT() It returns the number of rows that match a specified condition Example:-

SELECT COUNT(*) AS TotalProducts
FROM Products;

MIN() & MAX()
Min() is used to find the smallest value in a specified column.
Max() is used to find the largest value in a specified column. Examples of Min():-

SELECT MIN(Price) AS SmallestPrice
FROM Products;

Examples of Max():-

SELECT MAX(Price) AS LargestPrice
FROM Products;

SQL JOINS

They are used to combine data from multiple tables in a relational database hence providing more meaningful information based on relationships between tables.

Inner Join It returns only the rows that have matching values in both tables. Example:-

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Left Join It returns all rows from the left table and the matched rows from the right table. Example:-

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Right Join It returns all rows from the right table and the matched rows from the left table. Example:-

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Full Outer Join It returns all rows when there is a match in either of left or right table records. Example:-

SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

Common Table Expressions

Basic CTEs
It is a temporary, named result set that can be referenced within a single SQL statement. It is created using the with clause and can be used in select, insert, update, or delete queries.
Multiple CTEs
They’re defined in a single SQL Query by listing them after one with clause and separating each CTE with a comma.
Recursive CTEs
It is an SQL query that contains an anchor query and a recursive query joined by union all, allowing SQL to iterate through hierarchical of level-based data until no new rows are produced.

How can CTEs be used with Joins and aggregations?

They can be used to join multiple tables in the CTE or in the main query.
Aggregate the joined data.
Reference the CTE multiple times in the same query for reuse.

Differences between CTEs and subqueries

The main difference is that CTEs are named, reusable temporary result sets that are used to improve readability and maintainability, while subqueries are inline queries nested within another query and are used for simple or one-off calculations.

SQL SUBQUERIES

A subquery is a query within another query.

Scalar subqueries – It is a query that returns exactly one row and one column.
Example:- SELECT, WHERE, HAVING, and ORDER BY.
Correlated subqueries - It is a query that depends on values from the outer query and is executed once for each row processed by the outer query.
Non-correlated subqueries – It is a query that runs independently and executes once.

Subqueries used in SELECT, FROM, and WHERE clauses are used to perform dynamic filtering, aggregation, or derived table operations.
Examples:-

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

DEV Community