Making Sense of SQL: From Joins to Window Functions

SQL is more than just selecting rows from a table. Real-world databases store related information across multiple tables, and real-world questions often require analysis beyond simple totals. This is where JOINs and Window Functions shine.

Window function

A window function performs a calculation across a set of table rows that are related to the current row. Window functions can be compared to aggregate functions but unlike aggregate functions window functions does not cause rows to be grouped into a single output row that is the rows maintain their original identities.

Common window functions

1. ROW_NUMBER()
   Assigns a unique number to each row within a partition.
   
SELECT name, department_id,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY name
) AS row_num
FROM employees;

   Explanation:
   Each department starts numbering employees from 1.

2. RANK() and DENSE_RANK()
   Used to rank values, often with ties.
   
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

   RANK() skips numbers after ties

DENSE_RANK() does not skip numbers

1. LEAD() and LAG() LEAD - This function allows you to access the quantity of the next row for each customer. SELECT o.order_id, o.customer_id, o.quantity, LEAD(o.quantity) OVER (ORDER BY o.order_id) AS next_quantity FROM orders o; LAG - This function allows you to access the quantity of the previous row for each customer. SELECT o.order_id, o.customer_id, o.quantity, LAG(o.quantity) OVER (ORDER BY o.order_id) AS prev_quantity FROM orders o;

2. NTILE() function
   The NTILE function is a SQL window function that divides an ordered result set into a specified number of roughly equal-sized groups and assigns a bucket number to each row.
   for example: We want to divide customers into 2 groups (quartiles) based on their total order quantity.
   NTILE(2) divides customers into 2 equal groups (quartiles) based on their total quantity ordered.
   
SELECT c.first_name, c.second_name, SUM(o.quantity) AS total_quantity,
NTILE(3) OVER (ORDER BY SUM(o.quantity) DESC) AS quantity_tile
FROM orderss o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.first_name, c.second_name
ORDER BY quantity_tile;


3. Partition by
   It is used within a window function's OVER() clause to divide the query's result set into partitions. The window function then operates on each partition independently and calculations (like running totals or rankings) restart for each new partition

When to Use Window Functions:

1. Rankings
2. Running totals
3. Percentages and comparisons
4. Analytics without losing row-level data

JOINS

Joins allow you to combine related data from different tables into one result set.
a. INNER JOIN
An INNER JOIN returns only the rows that have matching values in both tables.
Example: Departments with employees (list department and employee names)

SELECT d.department_name, e.name
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id;


b. LEFT (OUTER) JOIN: Returns all rows from the left table, and only the matched rows from the right table
Example: Departments and their employees (even departments without employees)
SELECT d.department_name, e.name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

c. RIGHT (OUTER) JOIN: Returns all rows from the right table, and only the matched rows from the left table
Example: Projects with or without assigned employees
SELECT p.project_name, e.name
FROM projects p
RIGHT JOIN employees e ON p.employee_id = e.employee_id;

d. FULL (OUTER) JOIN: Returns all rows when there is a match in either the left or right table
Example: Departments and employees (whether matched or not)
SELECT d.department_name, e.name
FROM departments d
FULL OUTER JOIN employees e ON d.department_id = e.department_id;

e. SELF JOIN
A self join is a regular join but the table is joined with itself.

Example: Employees who manage someone
SELECT DISTINCT m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
f. CROSS JOIN
It combines every roww from one table with every row from another table.
Example 1: Every employee with every project
SELECT e.name, p.project_name
FROM employees e
CROSS JOIN projects p;