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 )

SELECT 
    customer_email, 
    COUNT(entity_id) AS NumberOfOrders, 
    MIN(CONVERT_TZ(created_at,'UTC','US/Mountain')) AS 'FirstOrder', 
    MAX(CONVERT_TZ(created_at,'UTC','US/Mountain')) AS 'MostRecentOrder',
    SUM(grand_total) AS TotalRevenue, 
    SUM(discount_amount) AS TotalDiscount
FROM sales_flat_order 
WHERE 
    customer_email IS NOT NULL
    AND store_id = 1
GROUP BY customer_email
LIMIT 500000

Answer

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

SELECT *
FROM (
    SELECT 
        t.*, 
        ROW_NUMBER() OVER(PARTITION BY customer_email ORDER BY created_at) rn_asc,
        ROW_NUMBER() OVER(PARTITION BY customer_email ORDER BY created_at DESC) rn_desc
    FROM sales_flat_order
    WHERE customer_email IS NOT NULL AND store_id = 1
) x
WHERE rn_asc = 1 OR rn_desc <= 2

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:

SELECT 
    customer_email,
    NumberOfOrders,
    MAX(CASE WHEN rn_asc = 1 THEN created_at END) FirstOrder,
    MAX(CASE WHEN rn_desc = 1 THEN created_at END) MostRecentOrder,
    MAX(CASE WHEN rn_desc = 2 THEN created_at END) MostRecentButOneOrder,
    TotalRevenue,
    TotalDiscount
FROM (
    SELECT 
        customer_email,
        created_at,
        COUNT(*) OVER(PARTITION BY customer_email) NumberOfOrders,
        SUM(grand_total) OVER(PARTITION BY customer_email) TotalRevenue, 
        SUM(discount_amount) OVER(PARTITION BY customer_email) TotalDiscount,
        ROW_NUMBER() OVER(PARTITION BY customer_email ORDER BY created_at) rn_asc,
        ROW_NUMBER() OVER(PARTITION BY customer_email ORDER BY created_at DESC) rn_desc
    FROM sales_flat_order
    WHERE customer_email IS NOT NULL AND store_id = 1
) x
GROUP BY customer_email