In real-world data analysis, businesses often need to know when customers make their second purchase. This helps them understand buying habits, track repeat customers and study customer activity.
You have an "orders" table with order IDs, customer IDs and order dates.
Your task is to write an SQL query to find the second purchase made by each customer using the ROW_NUMBER() window function.
Your Challenge
Write an SQL query to:
- Display customer ID, order ID and order date.
- Use ROW_NUMBER() to number each customer's orders.
- Use PARTITION BY to start the numbering again for every customer.
- Arrange orders by date, from oldest to newest.
- Return only the second order of each customer.
- Get the result without changing the original orders table.
orders
order_id|customer_id |order_date
1001 | C101 | 05-Jan-2026
1002 | C101 | 12-Jan-2026
1003 | C101 | 20-Jan-2026
2001 | C102 | 03-Feb-2026
2002 | C102 | 10-Feb-2026
3001 | C103
| 15-Feb-2026
Can you write the query before checking the solution?
The SQL Solution (Oracle)
SELECT
customer_id,
order_id,
order_date
FROM (
SELECT
customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS rn
FROM orders
)
WHERE rn = 2
ORDER BY customer_id;
Expected Output
customer_id |order_id |order_date
C101 | 1002 | 12-Jan-2026
C102 | 2002 | 10-Feb-2026
Customer C103 has only one order, so there is no second purchase to display.
Why Does This SQL Query Work?
- ROW_NUMBER() gives each order a number based on its position.
- PARTITION BY customer_id starts the numbering again for each customer.
- ORDER BY order_date arranges each customer's orders from oldest to newest.
- order_id helps decide the order when two purchases have the same date.
- WHERE rn = 2 returns only the second purchase of each customer.
- The original orders table remains unchanged because this is a SELECT query.
One Thing to Remember:
The PARTITION BY clause makes a big difference when working with customer purchase history.
- Without PARTITION BY: All orders are numbered together.
- With PARTITION BY customer_id: Numbering starts again for every customer.
- ROW_NUMBER(): Gives every order a separate number, even when two orders have the same date.
For example, if a customer has five orders, their orders will be numbered 1, 2, 3, 4 and 5.
Your Turn: How would you change this query to find the third purchase of every customer?
Share your SQL query in the comments!
Follow Sankalan Data Tech for more practical SQL challenges, Oracle SQL examples, window functions and database interview questions.



