When working with customer purchase data, looking at individual orders doesn't always tell us the full story.


Sometimes, we need to compare a customer's current purchase with their previous purchase to understand whether their spending is increasing or decreasing.

For example:

  • Did the customer spend more on their latest order?
  • Has their purchase amount increased compared to the previous order?
  • Which customers are spending more over time?

We can solve this using the SQL window function "LAG()".

In this tutorial, let's see how to use "LAG()" with "PARTITION BY" and "ORDER BY" to compare customer purchases in Oracle SQL.

The Problem

Suppose we have a table called "customer_orders" containing customer IDs, order dates, and purchase amounts.

Sample Table: customer_orders

customer_id| order_date| amount
C101| 01-Nov-2026| 1000
C101| 05-Nov-2026| 1500
C101| 10-Nov-2026| 1200
C102| 02-Nov-2026| 500
C102| 08-Nov-2026| 800
C102| 12-Nov-2026| 1000

Your Challenge

Write an SQL query to:

  1. Display customer ID, order date, and purchase amount.
  2. Find the previous purchase amount for each customer using "LAG()".
  3. Compare the current purchase amount with the previous purchase.
  4. Display only records where the purchase amount increased.
  5. Keep each customer's purchase history separate.
  6. Arrange the results by customer ID and order date.

Can you write the query before looking at the solution?

SQL Solution (Oracle)

We can use a subquery with the "LAG()" function to solve this problem.

SELECT
customer_id,
order_date,
amount,
previous_amount
FROM (
SELECT
customer_id,
order_date,
amount,
LAG(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS previous_amount
FROM customer_orders
)
WHERE amount > previous_amount
ORDER BY customer_id, order_date;

Expected Output

customer_id| order_date| amount| previous_amount
C101| 05-Nov-2026| 1500| 1000
C102| 08-Nov-2026| 800| 500
C102| 12-Nov-2026| 1000| 800

Notice that:

  • C101 spent 1500 compared to 1000 on the previous order.
  • C102 spent 800 compared to 500 on the previous order.
  • C102 then spent 1000 compared to 800 on the previous order.

The record for C101 on 10-Nov-2026 is not displayed because the purchase amount decreased from 1500 to 1200.

Why Does This SQL Query Work?

Let's understand the important parts.

  1. LAG(amount)

"LAG()" retrieves a value from a previous row without requiring a self-join.

Here, it retrieves the previous purchase amount.

  1. PARTITION BY customer_id

This separates the purchase history of each customer.

For example, C101's orders are compared only with other C101 orders. The same applies to C102.

  1. ORDER BY order_date

This determines the order in which purchases are compared.

The earliest purchase comes first, followed by the next purchase.

  1. WHERE amount > previous_amount

This condition returns only those records where the current purchase amount is greater than the previous purchase amount.

  1. Why use a subquery?

In Oracle SQL, we cannot directly use an analytic function in the WHERE clause of the same query block.

The subquery first calculates the previous purchase amount. The outer query then filters the results.

One Thing to Remember

  • "LAG()" retrieves a value from a previous row.
  • "LEAD()" retrieves a value from a following row.
  • "PARTITION BY" separates calculations into groups.
  • "ORDER BY" determines the sequence of rows.
  • Window functions are useful for analyzing changes over time.

Your Turn: Try This Next!

How would you modify this query to find customers whose spending decreased compared with their previous purchase?

Hint: Check the comparison operator in the WHERE condition.

Share your SQL query in the comments!

Where Can You Use This in Real Projects?

This type of SQL query is useful in:

  • Customer purchase analysis
  • Sales trend reporting
  • E-commerce analytics
  • Customer spending comparisons
  • Business intelligence dashboards
  • Data analytics projects

Learning window functions like "LAG()", "LEAD()", "ROW_NUMBER()" and "DENSE_RANK()" can help you solve many practical SQL reporting and interview problems.

More SQL tutorials and practical challenges:
https://www.sankalandtech.com/

sql #beginners #database #programming