E-commerce (5 of 5)
HardAmazon is a large e-commerce platform where customers can order various items ranging from electronics to clothing.
You're provided with two tables, orders and items, with the following columns:
Write a SQL query to get the second earliest order_id for each customer for each date they placed at least two orders. Your output should have the following columns: customer_id, order_date, second_earliest_order_id. Order it by order date and customer ID.
First, we create a CTE named RankedOrders.
Inside the CTE, we employ the ROW_NUMBER() window function to assign a rank to each order for a given customer on a specific date, based on the order_id in ascending order.
The PARTITION BY customer_id, order_date ensures that the numbering resets for each unique combination of customer and date.
In the main query, we filter out rows where order_rank is 2, which gives us the second earliest order for each customer-date combination.
The final result is sorted by order_date and customer_id for readability.