Mastering Joins and Window Functions in SQL

Joins in SQL

Joins allow one to combine rows from two or more tables based a related column. This is important when data is stored across multiple tables. There are several types of joins:

a.INNER JOIN

-Returns only the matching rows from both tables.
Syntax

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

Example

SELECT c.customer_id, c.first_name, o.order_id,o.quantity
FROM customers AS C
INNER JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays only customers who have placed an order.

b.LEFT JOIN (LEFT OUTER JOIN)

-Returns all rows from the left table and only matching rows from the right table.
-If there's no match, NULL is returned on the right table columns.
Syntax

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

Example

SELECT c.customer_id, c.first_name, o.order_id, o.quantity
FROM customers AS C
LEFT JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all customers even those who didn't place an order.

c.RIGHT JOIN (RIGHT OUTER JOIN)

-Returns all rows from the right table and only matching rows from the left table.
-If there'so match, NULL appears on the left table
Syntax

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

Example

SELECT c.customer_id, c.first_name,o.order_id,o.quantity
FROM customers AS c
RIGHT JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all orders, even if there's no matching customer

d.FULL OUTER JOIN

-Returns all rows from both tables, matching where possible and filling NULL when there's no match.
Syntax

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

Example

SELECT c.customer_id, c.first_name, o.order_id, o.quantity
FROM customers AS C
FULL OUTER JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all customers and orders, even unmatched ones.

e.CROSS JOIN

-Returns all possible combinations of both tables, where every row from one table is paired with every row from the other table.
Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

Example

SELECT c.first_name, o.order_id
FROM customers AS C
CROSS JOIN orders AS O;

f.SELF JOIN

-Joins the same table to itself, to compare rows within the same table.
Syntax

SELECT a.column, b.column2
FROM table_name a
JOIN table_name b
ON a.common_column = b.related_column

Example

SELECT 
    c1.customer_name AS customer, 
    c2.customer_name AS referred_by
FROM customers c1
JOIN customers c2
ON c1.referred_by = c2.customer_id;

c1 - represents the customer who was referred
c2 - represents the customer who did the referring
This finds the rows where the referred_by column of one customer (c1) matches the customer_id of another (c2)

Window Functions in SQL

Window Functions perform calculations across a set of rows that are related to the current row.
They are three types of window functions:

• Aggregate window functions
• Ranking window functions
• Value/Analytical window functions

Syntax

SELECT columns
     windowfunction_name(expression)
     OVER (
          [PARTITION BY column_name]
          [ORDER BY column_name]
FROM table_name;
)

Partition By:-Divides the data into groups
Order By:-Specifies the order of rows within each group

1.Aggregate Window Functions

-They compute totals, average, counts across rows.

• MIN()

-Shows the smallest value within a window

SELECT customer_id, order_id, total_amount,
     MIN(total_amount) 
     OVER(
        PARTITION BY customer_id) AS smallest_order,
FROM orders;

• MAX()

-Shows the largest value within a window

SELECT customer_id, order_id, total_amount,
     MAX(total_amount) 
     OVER(
        PARTITION BY customer_id) AS biggest_order,
FROM orders;

• COUNT()

-Counts rows but keeps all rows visible
-Used for showing how many records are in a group without GROUP BY

SELECT customer_id,
    COUNT(order_id) 
    OVER(
       PARTITION BY customer_id) AS total_orders
FROM orders;

This shows how many orders each customer has made

• AVG()

-Gives the average value for a window of rows
-Used to find average spend, average score, etc.

SELECT customer_id, order_id, total_amount,
    AVG(total_amount) 
    OVER(
       PARTITION BY customer_id) AS avg_spending
FROM orders;

This shows the customer's average order value

• SUM()

-Adds up values without grouping the rows
-Used for running totals or cumulative sums

SELECT customer_id, order_id,
    SUM(total_amount) 
    OVER (
       PARTITION BY customer_id 
       ORDER BY order_date) AS running_total
FROM orders;

This calculates running totals per customer

2.Ranking Window Functions

-They assign ranks, row numbers or categories to each row within its partition.
-Examples include

• ROW_NUMBER()

-Assigns a unique number to each row
-Used to find the first, second, third item in a list

SELECT customer_id, order_date,
     ROW_NUMBER() 
     OVER (
        PARTITION BY customer_id 
        ORDER BY order_date) AS order_number
FROM orders;

In this example, each customer's first order gets 1, second gets 2...

• RANK()

-Gives a rank number based on order, but skips numbers when there are ties
-Used for ranking by price, score, sales

SELECT customer_id, total_amount,
    RANK() 
    OVER(
       PARTITION BY customer_id 
       ORDER BY total_amount DESC) AS order_rank
FROM orders;

In this example, if two orders have the same amount, they both get the same rank and the next rank is skipped eg.1,2,2,4

• DENSE_RANK()

-Similar to RANK() but does not skip numbers
-Used for ranking when you want continuous ranks eg.(1,2,2,3)

SELECT customer_id, total_amount,
    DENSE_RANK() 
    OVER (
       PARTITION BY customer_id 
       ORDER BY total_amount DESC) AS dense_rank
FROM orders;

• NTILE()

-Divides rows into n equal groups
-Used for dividing data into quartiles or percentiles

SELECT order_id, total_amount,
    NTILE(4) 
    OVER (
       ORDER BY total_amount DESC) AS quartile
FROM orders;

*In this example, orders are divided into 4 groups(top 25%, next 25%...)

3.Value/Analytical Window Functions

• LAG()

-Looks at the previous row's value
-Used for comparing current and previous rows

SELECT customer_id, order_date, total_amount,
    LAG(total_amount) 
    OVER(
       PARTITION BY customer_id 
       ORDER BY order_date) AS prev_order
FROM orders;

This example shows what the previous order's amount was for each customer

• LEAD()

-Looks at the next row's value
-Used for comparing to the next record

SELECT customer_id, order_date, total_amount,
      LEAD(total_amount) 
      OVER(
         PARTITION BY customer_id 
         ORDER BY order_date) AS next_order
FROM orders;

This example shows what the next order's amount will be

Conclusion

Joins help us bring related data together from different tables while window functions allow us to perform advanced calculations like rankings and totals.