SQL Programming Resources on Telegram: “SELECT employee_name, salary, ROW

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_num
FROM employees;

Even if two employees have the same salary, they receive different row numbers.

Example:

90000 → 1

80000 → 2

80000 → 3

70000 → 4

🆚 10. RANK vs DENSE_RANK vs ROW_NUMBER

ROW_NUMBER

→ Every row gets a unique number

RANK

→ Ties share rank + gaps appear

DENSE_RANK

→ Ties share rank + no gaps

🏆 11. Top Customer per Region

Suppose we want the highest-spending customer in each region.

First calculate spending:

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

SELECT
    customer_id,
    region,
    total_spending,

    RANK() OVER (
        PARTITION BY region
        ORDER BY total_spending DESC
    ) AS regional_rank

FROM customer_sales;

The important part is:

PARTITION BY region

This restarts the ranking for each region.

🎯 12. Top 1 Per Group

If we want only the top customer from each region:

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

ranked_customers AS (
    SELECT
        customer_id,
        region,
        total_spending,

        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY total_spending DESC
        ) AS rn

    FROM customer_sales
)

SELECT
    customer_id,
    region,
    total_spending
FROM ranked_customers
WHERE rn = 1;

This is one of the most frequently used Window Function patterns in SQL interviews.

➕ 13. Running Total

Suppose we have:

order_date| amount

Jan 1| 100

Jan 2| 200

Jan 3| 150

Jan 4| 300

We want:

order_date| amount| running_total

Jan 1| 100| 100

Jan 2| 200| 300

Jan 3| 150| 450

Jan 4| 300| 750

Use:

SELECT
    order_date,
    amount,

    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total

FROM orders;

The calculation builds cumulatively:

100

100 + 200

100 + 200 + 150

100 + 200 + 150 + 300

📅 14. Running Total by Customer

We can combine "PARTITION BY" and "ORDER BY".

SELECT
    customer_id,
    order_date,
    amount,

    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total

FROM orders;

Now each customer's running total is calculated independently.

⏮️ 15. LAG()

"LAG()" lets you access a value from a previous row.

Example:

SELECT
    order_date,
    amount,

    LAG(amount) OVER (
        ORDER BY order_date
    ) AS previous_amount

FROM orders;

Result:

order_date| amount| previous_amount

Jan 1| 100| NULL

Jan 2| 200| 100

Jan 3| 150| 200

Jan 4| 300| 150

This is extremely useful for:

• Month-over-month analysis

• Previous transaction comparison

• Trend analysis

• Customer behavior

⏭️ 16. LEAD()

"LEAD()" does the opposite.

It looks at the next row.

SELECT
    order_date,
    amount,

    LEAD(amount) OVER (
        ORDER BY order_date
    ) AS next_amount

FROM orders;

Result:

order_date| amount| next_amount

Jan 1| 100| 200

Jan 2| 200| 150

Jan 3| 150| 300

Jan 4| 300| NULL

Easy memory:

LAG

→ Look backward

LEAD

→ Look forward

📈 17. Calculate Change from Previous Row

"LAG()" becomes more useful when combined with arithmetic.