Advanced SQL Techniques in PostgreSQL using Window Functions & Common Table Expressions
Tags: postgres, opensource, database, apacheage
Published: 2023-09-17 22:27:53

# Window Functions

IMAGINE YOU'RE CHILL- oh, forgot that I had my capslock on from writing SQL queries... anyways, imagine you're chilling by your window, scrolling through Insta, and you see your neighbors, the delivery dude dropping off Amazon packages, and a cheeky squirrel raiding the bird feeder. Now, what if I told you that you can not only watch but also interact with all that action without moving a muscle? That's precisely what **Window Functions** in PostgreSQL allow you to do with your data.

![squidward](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u6yvnkyt82kr7sfa69w6.png)

Window Functions are akin to astute observers. They not only perceive events but also offer illuminating insights. These functions operate within a designated "window" of rows related to the one currently under scrutiny. There is no requirement for labyrinthine joins or convoluted subqueries. It is as if you have a data maestro attending to the intricacies of your SQL queries.

![crab guy](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/30ev4fo9jpajmukiudgf.png)

Example, you need to calculate the monthly sales growth rate for each product category and let's say you have the tables named "sales" with columns "category," "sale_date," and "sale_amount."

```sql
-- Calculating Monthly Sales Growth Rate with Window Functions
SELECT
    category,
    EXTRACT(MONTH FROM sale_date) AS month,
    SUM(sale_amount) AS monthly_sales,
    LAG(SUM(sale_amount)) OVER (PARTITION BY category ORDER BY EXTRACT(MONTH FROM sale_date)) AS prev_month_sales,
    CASE
        WHEN LAG(SUM(sale_amount)) OVER (PARTITION BY category ORDER BY EXTRACT(MONTH FROM sale_date)) = 0 THEN 100 -- Handling division by zero
        ELSE ((SUM(sale_amount) - LAG(SUM(sale_amount)) OVER (PARTITION BY category ORDER BY EXTRACT(MONTH FROM sale_date))) / LAG(SUM(sale_amount)) OVER (PARTITION BY category ORDER BY EXTRACT(MONTH FROM sale_date))) * 100
    END AS growth_rate
FROM
    sales
GROUP BY
    category, EXTRACT(MONTH FROM sale_date)
ORDER BY
    category, EXTRACT(MONTH FROM sale_date);
```

Although, it may be sounding cool, it's not much without some practical applications isn't it? Well ofcourse! there are plenty of applications to get a hold of this function, cases like - that one time where you pondered over who claims the top spot within your ranks or sought to calculate percentiles within your dataset? Window Functions make **<u>ranking and percentile</u>** calculations as straightforward as a stroll in a garden.

![confusion](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fbohs04636kxznu3zlb7.png)

Or, when you have the necessity of maintaining a **<u>running tally</u>**, whether it be tracking one's weekly consumption of crumpets or the number of rainy days in a month, is a common occurrence. Window Functions are more than capable of managing such requisites.

Should you be engaged in tracking time-series data such as stock prices or the weather, and wish to compute **<u>moving averages</u>**, Window Functions are at your service, rendering the task gracefully achievable.


# Common Table Expressions (CTEs)

CTEs are like craftsmen who ensure the meticulous arrangement of your queries, much like a masterful composition by Hans Zimmer. It lets you afford the luxury of dividing complex queries into manageable sections. These segments are established at the commencement of your query and subsequently woven together to produce an eloquent and organized SQL statement.

![bear](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2pq4hmnkeb0rqmgxfp1q.png)


Let's say, you faced with data structures possessing a hierarchical nature, such as organizational charts or genealogical trees, in that case, CTEs provide an elegant solution as **<u>recursive queries</u>**.

Not only that, but it **<u>simplified self-joins</u>** as it is often perplexing practice of joining a table with itself and is rendered obsolete by CTEs.

It can also, make **<u>reusability</u>** a blessing, if you encounter a subquery deserving of reuse within your primary query, CTEs allow you to define it once and employ it as necessary, akin to employing a versatile tool in your workshop.

![oppa](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k67foun6c4o8gi1yf18j.png)

And for the practical usecase scenario for this, let's consider a query for retrieving hierarchical data, such as an organizational chart and we got a table named "employees" with columns "employee_id" and "manager_id," where "manager_id" references the "employee_id" of the employee's manager.

```sql
-- Common Table Expression (CTE) for Organizational Hierarchy
WITH RecursiveEmployeeHierarchy AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS depth -- Initial depth for the CEO
    FROM
        employees
    WHERE
        manager_id IS NULL -- Assuming the CEO has no manager
    
    UNION ALL
    
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        r.depth + 1 -- Incrementing depth for subordinates
    FROM
        employees e
    INNER JOIN
        RecursiveEmployeeHierarchy r
    ON
        e.manager_id = r.employee_id
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    depth
FROM
    RecursiveEmployeeHierarchy
ORDER BY
    depth, employee_id;
```

This is a much simpler approach using CTE to get the organizational chart up.


# So? Why on earth are these considered advanced!?

Okay smarty pants, let's say you have an objective to calculate the monthly sales growth rate for each product category and you seek clarity and efficiency in your endeavor, rather than becoming entangled in a web of SQL intricacies. Enter Window Functions and CTEs, your trusty companions, so, you'll have a representation something like this - 

```sql
-- Calculating Monthly Sales Growth Rate with Window Functions and CTEs
WITH MonthlySales AS (
    SELECT
        category,
        date_trunc('month', sale_date) AS month,
        SUM(sale_amount) AS monthly_sales
    FROM
        sales
    GROUP BY
        category, month
),
MonthlyGrowth AS (
    SELECT
        category,
        month,
        monthly_sales,
        LAG(monthly_sales) OVER (PARTITION BY category ORDER BY month) AS prev_month_sales
    FROM
        MonthlySales
)
SELECT
    category,
    month,
    monthly_sales,
    CASE
        WHEN prev_month_sales = 0 THEN 100 -- Handling division by zero
        ELSE (monthly_sales - prev_month_sales) / prev_month_sales * 100
    END AS growth_rate
FROM
    MonthlyGrowth
ORDER BY
    category, month;
```

Fear not if this code initially appears somewhat daunting. Essentially, we are calculating the monthly sales growth rate for each product category, with Window Functions and CTEs orchestrating the process as gracefully as a symphony conducted by a maestro.

And yeah, that's all for today folks, keep smashing in capslocks! :)


![idk](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q344xnk2qixfnezl0qk9.png)