SELECT
    order_date,
    amount,

    amount - LAG(amount) OVER (
        ORDER BY order_date
    ) AS change_from_previous

FROM orders;

If:

Previous = 100

Current = 200

then:

200 - 100 = 100

📊 18. Percentage Change

We can calculate percentage change:

SELECT
    order_date,
    amount,

    (
        amount - LAG(amount) OVER (
            ORDER BY order_date
        )
    ) * 100.0
    / NULLIF(
        LAG(amount) OVER (
            ORDER BY order_date
        ),
        0
    ) AS percentage_change

FROM orders;

Here we use:

LAG()

→ Previous value

NULLIF()

→ Prevent division by zero

This combines concepts from earlier parts.

📐 19. Moving Average

Suppose we want a 3-row moving average.

SELECT
    order_date,
    amount,

    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING
                 AND CURRENT ROW
    ) AS moving_average

FROM orders;

For each row, SQL considers:

Current row

+

Previous row

+

Previous 2nd row

This is useful for smoothing short-term fluctuations in time-series data.

🧮 20. Window Functions for Percentage of Total

Suppose we want each product's percentage of total revenue.

SELECT
    product_id,
    revenue,

    revenue * 100.0
    / SUM(revenue) OVER () AS percentage_of_total

FROM product_sales;

Example:

Product A → 20%

Product B → 30%

Product C → 50%

The key advantage:

We don't need a separate query to calculate total revenue.

📊 21. Percentage Within a Group

Suppose we want each customer's percentage of their region's revenue.

SELECT
    customer_id,
    region,
    revenue,

    revenue * 100.0
    / SUM(revenue) OVER (
        PARTITION BY region
    ) AS region_percentage

FROM customer_sales;

The denominator is calculated separately for each region.

🧠 22. Window Functions with CASE

You can combine Window Functions with "CASE".

Example:

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)

SELECT
    customer_id,
    total_spending,

    CASE
        WHEN RANK() OVER (
            ORDER BY total_spending DESC
        ) <= 10
        THEN 'Top 10'
        ELSE 'Other'
    END AS customer_group

FROM customer_sales;

This allows analytical results to be converted into business categories.

⚠️ 23. Window Functions Don't Replace GROUP BY

Window Functions and "GROUP BY" solve different problems.

GROUP BY

Use when you want:

1 row per group

Example:

SELECT
    region,
    SUM(revenue)
FROM sales
GROUP BY region;

Window Function

Use when you want:

Original rows + group-level calculation

Example:

SELECT
    customer_id,
    region,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY region
    ) AS regional_revenue
FROM sales;

⚠️ 24. Window Function Order Matters

For rankings:

RANK() OVER (
    ORDER BY revenue DESC
)

and:

RANK() OVER (
    ORDER BY revenue ASC
)

produce completely different rankings.

Always ask:

«What should rank 1 represent?»

Highest value?

Use:

DESC

Lowest value?

Use:

ASC

🔍 25. Multiple Window Functions

You can use several Window Functions in one query.