Joins and Window Functions

In the world of data analytics, SQL is the essential language for extracting insights from relational databases. While basic queries (SELECT, WHERE) are straightforward, the true power of SQL lies in manipulating and analyzing complex data relationships. This is where Joins and Window Functions become indispensable tools.

Understanding these two concepts separates beginners from advanced SQL users. Joins help you connect different datasets, while Window Functions allow you to analyze data within those datasets without losing detail.

---

1. SQL Joins: Combining Data Across Tables

Relational databases are structured into multiple, smaller tables to prevent data repetition (normalization). For example, a customers table and an orders table are kept separate. Joins enable you to combine these tables based on a related column, usually a Primary Key–Foreign Key relationship such as customers.customer_id = orders.customer_id.

Core Join Types

INNER JOIN
Returns only the rows where there is a match in both tables.
This is used for finding valid, connected data (for example, all orders that belong to a customer).

SELECT customers.name, orders.order_id, orders.amount
FROM orders
INNER JOIN customers
    ON orders.customer_id = customers.customer_id;

---

LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table, and matched rows from the right table.
If no match exists, the right side will contain NULL values.
Ideal for keeping your main list intact (for example, all customers, even those who have not placed an order).

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;

---

RIGHT JOIN
The opposite of a LEFT JOIN; it keeps all rows from the right table.

SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;

---

FULL OUTER JOIN
Returns all rows when there is a match in either table.
It combines the results of both LEFT and RIGHT joins.

SELECT customers.name, orders.order_id
FROM customers
FULL OUTER JOIN orders
    ON customers.customer_id = orders.customer_id;

---

2. Window Functions: Context-Aware Analytics

Window functions perform calculations across a set of table rows that are related to the current row. Unlike GROUP BY, which collapses rows into a single summary row, window functions keep individual rows intact while adding a calculated column.

The Syntax: OVER()

The OVER() clause defines the "window" or subset of data that the function operates on.

• PARTITION BY → Divides the rows into groups
  Example: PARTITION BY customer_id

• ORDER BY → Orders the rows within each partition
  Example: ORDER BY amount DESC

---

Common Window Functions

Ranking Functions

• ROW_NUMBER() → Assigns a unique number
• RANK() → Assigns rank, skips numbers on ties
• DENSE_RANK() → Assigns rank without skipping numbers

Example: Rank orders from highest to lowest amount.

SELECT
    order_id,
    customer_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM orders;

---

Aggregation Functions

• SUM(), AVG(), COUNT(), MAX(), MIN()

Example: Running total of order amounts over time.

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

---

Value Functions

• LAG() → Accesses data from a previous row
• LEAD() → Accesses data from a following row

Example: Compare each order with the previous one.

SELECT
    order_id,
    amount,
    LAG(amount) OVER (ORDER BY order_id) AS previous_amount
FROM orders;

---

3. Combining Joins and Window Functions

In analytics, you often join tables first to get a complete picture, and then apply window functions to analyze trends.

Scenario: List all orders with customer names, and rank orders by amount within each customer.

SELECT
    customers.name,
    orders.order_id,
    orders.amount,
    RANK() OVER (
        PARTITION BY orders.customer_id
        ORDER BY orders.amount DESC
    ) AS order_rank
FROM orders
JOIN customers
    ON orders.customer_id = customers.customer_id;

---

Joins are used to connect two or more tables in a relational database, allowing you to bring related data together from different sources. Because joins combine datasets, they can change the number of rows in the result, either expanding or reducing them depending on the type of join used. They rely on keywords such as JOIN and ON to define how tables relate. Window functions, on the other hand, perform calculations over a set of rows within a single result set without collapsing those rows. Unlike joins, they preserve the original row count while adding computed values like running totals, rankings, or moving averages. They are defined using the OVER() clause, often with PARTITION BY to group related rows for analysis.

---

Conclusion

By mastering both Joins and Window Functions, one can transform scattered, raw data into meaningful, actionable insights, often without needing complex, multi-step subqueries. These tools are foundational for anyone working in data analytics, backend development, or database administration.

If the aim is to move from intermediate to advanced SQL, this is one of the most valuable skill combinations you can learn.