Skip to content
Advertisement

How do I find the 2nd most recent order of a customer

I have the customers first order date and last order date by doing MIN and MAX on the created_at field (grouping by email), but I also need to get the customers 2nd most recent order date (the order date right before the last orderdate )

Advertisement

Answer

Use window function ROW_NUMBER() (available in MySQL 8.0):

This will get you the earlierst and the two latest orders placed by each customer.

Note: it is unclear what the timezone conversions are intended for. I left them apart, since they obviously do not affect the sorting order; feel free to add them as per your use case.


If you want a single record per customer, along with its total count of orders, and the date of his first, last, and last but one order, then you can aggregate in the outer query:

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement