Joins and Window Functions In SQL

#sql #datascience #luxdevhq

Introduction

In the world of SQL, there is a fundamental difference between having data and understanding it. If your database is a library, raw tables are just books scattered across different book-shelves. To give an insightful story, you need to have a connection between those books and analyze the narrative within them. This is where the two most powerful tool in data analysis come in. Joins and Windows.
Often, data analysis feels like a jigsaw puzzle where the pieces are not consolidated and are scattered across different boxes. Joins and Window functions are the basic tools to arrange those pieces together to create order. While they might appear similar - both combine data - Meaning they serve in a different capacity in your analytical workflow. In this article, we shall break down how they work, when to use them and how to combine them in solving complex real-world problems.

JOINS: The "Horizontal" Connection

Joins are used to link two or more tables together on a thread column between them. They act like a way to enrich your data. If you have a listof orders but require the customers' names, you join the orders table to the customers' table.

Types of Joins:

Inner Join - Returns records that have matching values in both tables.
Left Join - Returns all records from them left table, and matched records from the right.
Right Join - Returns all records from the right table, and matched records from the left.
Full Join - Returns all records when there is a match in either table.
Scenario: You have an Employees table and want to see every employees' name right next to their manager's name. Since the manager is also an employee, their data is in the same table.
SELECT e.Name AS Employee, m.Name AS Manager FROM Employees e LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

The Rule of Join; Joins change the breadth of your data by adding columns from other tables, but they can also change the row count if there are multiple matches.

WINDOW FUNCTIONS: The "Vertical" calculation.

Window functions perform the role of calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, like Sum, Avg, with GROUPED BY, Window functions do not group rows into a single output row. The rows retain their seperate identities. This is made possible through the OVER() Clause.
Key Components
Partition by: Divides the results set into partitions (Groups)
Order By: Sorts the rows within each partition.
Frame Clause: Defines the subset of rows to look at e.g The last two days.

Example: The Running Total.
If you want to see your daily sales alongside a running total of all sales to date, you use a window function.

SELECT 
    Date, 
    DailySales,
    SUM(DailySales) OVER (ORDER BY Date) AS RunningTotal
FROM Sales;

When to Use Which:

Choosing between a Join and Window Function depends on your 'Data Goal'.
Use a join when;

You need data that lives in another table.
You are building a foundation dataset for a report.
You need to filter one table based on the existence pf records in another.

Use a Window Function when;

You need to compare a row to its neighbors e.g How much more did we sell today than yesterday.
You need to do ranking, e.g Who are the top 4 customers by region?

SELECT 
    Region, 
    Salesperson, 
    TotalSales,
    DENSE_RANK() OVER (PARTITION BY Region ORDER BY TotalSales DESC) AS SalesRank
FROM RegionalSales;

You need to calculate a moving average or a cumulative sum.

Conclusion

At first glance, Joins and Windows might seem like competing methods for combining data. However, as we have explored in the article, the two are actually distinct. By mastering both, you move beyond simply "Retrieving" data and begin generating meaningful insights. You stop asking what data is and start explaining it.

DEV Community