Learn SQL with PostgreSQL - Window Functions (Advanced Data Analytics)

#Introduction

Welcome to episode 9 of the Learn SQL with PostgreSQL series! We already learned aggregation with GROUP BY in episode 5, which combines many rows into a single summary. But what if we want to calculate something per group while still preserving every original row? For example: rank each product within its category, or calculate the sales difference between months without losing the per-month detail. The answer is window functions.

In this episode, we'll cover the conceptual difference between GROUP BY aggregation and window functions, the anatomy of the OVER() clause with PARTITION BY and window framing, the ranking function category (ROW_NUMBER, RANK, DENSE_RANK, NTILE), and the value function category (LAG, LEAD, FIRST_VALUE, LAST_VALUE).

#The Window Functions Concept

#GROUP BY vs Window Functions

The fundamental difference between the two lies in the number of result rows:

GROUP BY: combines the rows in a group into ONE summary row. The number of rows decreases.
Window function: calculates a value for each row, considering the rows around it ("the window"), without combining — the number of rows stays the same.

Window function: all rows remain

SELECT
    product,
    category,
    sales,
    SUM(sales) OVER (PARTITION BY category) AS total_per_kategori
FROM sales;

Notice the query: every row still appears, and the extra total_per_kategori column contains the total for that row's category. A window function "looks sideways" at the rows within its group — that's why it's called a window.

#Anatomy of the OVER() Clause

Every window function must be followed by an OVER() clause that defines the window:

sql

OVER (
    PARTITION BY kolom_pengelompokan
    ORDER BY kolom_pengurutan
    window_frame
)

PARTITION BY: divides the result into groups. Similar to GROUP BY, but rows aren't combined. If omitted, the entire result is treated as one big group.
ORDER BY: determines the order of rows within each group — important for ranking functions and LAG/LEAD.
Window frame: determines the range of rows to look at, optional (defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present).

Complete OVER anatomy

SELECT
    product,
    category,
    sales,
    ROW_NUMBER() OVER (
        PARTITION BY category
        ORDER BY sales DESC
    ) AS peringkat
FROM sales;

#Window Framing: ROWS BETWEEN

A window frame controls the range of rows the function calculates over. The most common syntax:

sql

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Meaning: from the first row of the partition up to the current row — producing a running total:

Running total with a window frame

SELECT
    month,
    sales,
    SUM(sales) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS kumulatif
FROM monthly_sales
ORDER BY month;

Other useful frame variations:

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW: a 4-month moving average.
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: the total from this row to the end.

#Window Function Category: Ranking

The four ranking functions behave differently on equal values (ties):

Function	Behavior on equal values
`ROW_NUMBER()`	Consecutive unique numbers, no ties — arbitrary order for equal values
`RANK()`	Ties get the same rank, next rank skips (1, 1, 3)
`DENSE_RANK()`	Ties get the same rank, no skips (1, 1, 2)
`NTILE(n)`	Divides the partition into n balanced buckets (for percentiles)

Comparing ranking functions

SELECT
    product,
    category,
    sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS dense_rank,
    NTILE(4) OVER (PARTITION BY category ORDER BY sales DESC) AS kuartil
FROM sales;

#Practical Pattern: Top-N per Group

One of the most useful window function patterns in production is top-N per group — e.g. the 3 best-selling products per category:

Top 3 products per category

SELECT product, category, sales
FROM (
    SELECT
        product,
        category,
        sales,
        ROW_NUMBER() OVER (
            PARTITION BY category ORDER BY sales DESC
        ) AS peringkat
    FROM sales
) AS berperingkat
WHERE peringkat <= 3;

#Window Function Category: Value

Function	Takes its value from
`LAG(expr, n)`	n rows before (default 1)
`LEAD(expr, n)`	n rows after (default 1)
`FIRST_VALUE(expr)`	the first row in the window
`LAST_VALUE(expr)`	the last row in the window

#LAG and LEAD: Comparing with Neighboring Rows

Month-over-month growth with LAG

SELECT
    month,
    sales,
    LAG(sales) OVER (ORDER BY month) AS sales_bulan_lalu,
    sales - LAG(sales) OVER (ORDER BY month) AS selisih
FROM monthly_sales
ORDER BY month;

#FIRST_VALUE and LAST_VALUE

Difference from the most expensive product

SELECT
    product,
    sales,
    FIRST_VALUE(sales) OVER (
        PARTITION BY category ORDER BY sales DESC
    ) AS termahal_di_kategori,
    FIRST_VALUE(sales) OVER (
        PARTITION BY category ORDER BY sales DESC
    ) - sales AS gap_dari_tertinggi
FROM sales;

#Closing

Key takeaways:

Window functions don't combine rows — the number of rows stays the same, with analysis result columns added.
PARTITION BY divides groups; ORDER BY inside OVER() sets the order.
Window framing controls the range of rows calculated — the key to running totals and moving averages.
RANK vs DENSE_RANK vs ROW_NUMBER are distinguished by how they treat tied values.
LAG and LEAD are the main tools for analyzing trends between rows.

In episode 10, we'll learn the query structure that many developers are most proud of: Common Table Expressions (CTE) & Recursive Queries — from non-recursive CTEs with the WITH clause for clean, modular queries, to WITH RECURSIVE for processing hierarchical data like organizational structures and multi-level categories.