SQL Programming Resources on Telegram: “SELECT customer_id, order_date, amount, ROW

SELECT
    customer_id,
    order_date,
    amount,

    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS order_number,

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

    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_order_amount

FROM orders;

Now we have:

Order number

+

Running spending

+

Previous order amount

all while preserving the order-level rows.

🏢 26. Real-World Business Example

Suppose a company wants to analyze customer transactions.

We want:

• Transaction date

• Transaction amount

• Previous transaction

• Difference from previous transaction

• Running customer spending

SELECT
    customer_id,
    transaction_date,
    amount,

    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
    ) AS previous_amount,

    amount - LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
    ) AS amount_change,

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

FROM transactions;

This is a realistic analytical SQL pattern.

🎤 SQL Interview Questions

Q1. What is a Window Function?

A function that performs calculations across a set of related rows while retaining the individual rows in the result.

Q2. What does "OVER()" do?

It defines the window of rows over which the function operates.

Q3. What does "PARTITION BY" do?

It divides rows into logical groups for the Window Function.

Q4. Difference between GROUP BY and Window Functions?

"GROUP BY" collapses rows into groups.

Window Functions calculate across rows without collapsing them.

Q5. Difference between RANK() and DENSE_RANK()?

"RANK()" leaves gaps after ties.

"DENSE_RANK()" does not.

Example:

RANK → 1, 2, 2, 4

DENSE_RANK → 1, 2, 2, 3

Q6. Difference between ROW_NUMBER() and RANK()?

"ROW_NUMBER()" gives every row a unique number.

"RANK()" gives tied rows the same rank.

Q7. What does LAG() do?

Returns a value from a previous row.

Q8. What does LEAD() do?

Returns a value from a subsequent row.

Q9. How do you calculate a running total?

SUM(amount) OVER (
    ORDER BY date_column
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW
)

Q10. How do you find the top customer in each region?

Use a ranking Window Function with:

PARTITION BY region
ORDER BY total_spending DESC

Then filter the ranking in an outer query or CTE.

📝 Practice Questions

Practice 1

Rank employees by salary.

SELECT
    employee_name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Practice 2

Calculate total spending for each customer while keeping every order.

SELECT
    order_id,
    customer_id,
    amount,

    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_total

FROM orders;

Practice 3

Find the previous order amount for every customer.

SELECT
    customer_id,
    order_date,
    amount,

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

FROM orders;

Practice 4

Calculate a running total for each customer.