You can then calculate month-over-month change:

SELECT
    Sales_Month,
    Sales,
    Sales - LAG(Sales) OVER (
        ORDER BY Sales_Month
    ) AS Sales_Change
FROM Monthly_Sales;

🔹 9. LEAD()

LEAD() does the opposite.

It allows you to access the next row.

SELECT
    Sales_Month,
    Sales,
    LEAD(Sales) OVER (
        ORDER BY Sales_Month
    ) AS Next_Month_Sales
FROM Monthly_Sales;

Useful for:

• Comparing future periods

• Customer activity

• Event sequences

• Next purchase analysis

• Time-based analysis

🔹 10. Running Total

A running total continuously accumulates values.

SELECT
    Order_Date,
    Sales,
    SUM(Sales) OVER (
        ORDER BY Order_Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Running_Sales
FROM Orders;

Example: 10,000 → 15,000 → 22,000 becomes 10,000 → 25,000 → 47,000

🔹 11. Running Total by Region

You can combine PARTITION BY with a running total.

SELECT
    Region,
    Order_Date,
    Sales,
    SUM(Sales) OVER (
        PARTITION BY Region
        ORDER BY Order_Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Regional_Running_Sales
FROM Orders;

Each region gets its own running total.

🔹 12. Moving Average

A moving average helps identify trends while reducing short-term fluctuations.

SELECT
    Sales_Month,
    Sales,
    AVG(Sales) OVER (
        ORDER BY Sales_Month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS Three_Month_Avg
FROM Monthly_Sales;

This calculates a 3-month moving average.

Useful for:

📈 Sales trends,

📊 Revenue analysis,

📦 Demand forecasting,

👥 Customer activity

🔹 13. NTILE()

NTILE() divides rows into approximately equal groups.

For example, divide customers into four sales groups:

SELECT
    Customer_ID,
    Total_Sales,
    NTILE(4) OVER (
        ORDER BY Total_Sales DESC
    ) AS Sales_Quartile
FROM Customers;

This can help identify:

• Top 25% customers

• Bottom 25% customers

• Customer segments

• Performance groups

🔹 14. Removing Duplicates

Window functions are also extremely useful for deduplication.

WITH Ranked_Data AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Customer_ID, Order_Date, Sales
            ORDER BY Order_ID
        ) AS rn
    FROM Orders
)

SELECT *
FROM Ranked_Data
WHERE rn = 1;

This keeps the first record from each duplicate group.

🔹 15. Why Window Functions Cannot Usually Be Used Directly in WHERE

This won't generally work:

SELECT
    Employee,
    RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees
WHERE Salary_Rank <= 3;

Why?

Because the window calculation happens after the filtering stage.

Instead, use a CTE:

WITH Ranked AS (
    SELECT
        Employee,
        Salary,
        RANK() OVER (
            ORDER BY Salary DESC
        ) AS Salary_Rank
    FROM Employees
)
SELECT *
FROM Ranked
WHERE Salary_Rank <= 3;

This is another reason CTEs + Window Functions are such a powerful combination.

💼 Real-World Data Analyst Applications

Window functions are commonly used for:

✅ Top N products by category

✅ Ranking employees by department

✅ Customer rankings by region

✅ Month-over-month growth

✅ Running revenue totals

✅ Moving averages

✅ Finding first/previous/next transactions

✅ Identifying duplicate records

✅ Customer purchase sequences

✅ Performance comparisons

🎯 SQL Interview Challenge

Question: Find the top 3 highest-paid employees in every department.

WITH Ranked_Employees AS (
    SELECT
        Employee,
        Department,
        Salary,
        DENSE_RANK() OVER (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS Salary_Rank
    FROM Employees
)
SELECT
    Employee,
    Department,
    Salary
FROM Ranked_Employees
WHERE Salary_Rank <= 3;

🏆 Double Tap ❤️ For More