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

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

Answer

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;