SQL Programming Resources on Telegram: “SELECT customer_id, order

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;

Practice 5

Assign a unique order number to each customer's orders.

SELECT
    customer_id,
    order_id,
    order_date,

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

FROM orders;

🧪 Mini SQL Challenge

You have:

sales

sale_id

customer_id

sale_date

amount

Write a query that returns:

• Customer ID

• Sale date

• Amount

• Previous sale amount

• Difference from previous sale

• Running customer spending

• Customer's transaction number

Solution

SELECT
    customer_id,
    sale_date,
    amount,

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

    amount - LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS difference_from_previous,

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

    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS transaction_number

FROM sales;

What did we use?

LAG()

→ Previous transaction

SUM() OVER()

→ Running spending

ROW_NUMBER()

→ Transaction sequence

PARTITION BY

→ Separate calculations for each customer

ORDER BY

→ Establish chronological order

💡 Double Tap ❤️ For More