How to get the 2nd record for a customer purchase?-CodePudding

I'm working on a customers database and I want to get all data for their second purchase (for all of our customer weather they have 2 or more purchases). For example:

Customer_ID      Order_ID      Order_Date 
1                259           09/05/2020
1                644           03/11/2020
1                617           18/04/2022
4                834           22/09/2021
4                995           07/02/2022

I want to display the second order which is:

Customer_ID      Order_ID      Order_Date 
1                644           03/11/2020
4                995           07/02/2022

I'm facing some difficulties in finding the right logic, any idea how I can achieve my end goal? :)

*Note: I'm using snowflake

CodePudding user response：

You can use a ROW_NUMBER and filter using QUALIFY clause:

select * from table qualify row_number() over(partition by customer_id order by order_date) = 2;

CodePudding user response：

You can use common table expression

with CTE_RS
AS (
SELECT Customer_ID,ORDER_ID,Order_Date,ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY Order_Date ) ORDRNUM FROM *TABLE NAME*
  )
 SELECT Customer_ID,ORDER_ID,Order_Date
 FROM CTE_RS
 WHERE ORDRNUM = 2 ;