E-commerce: Second Earliest Order
Amazon 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.
This question is the last part of a 5-part e-commerce question series. The other lessons in the series are linked below:
- E-commerce: Units Ordered Yesterday (1 of 5)
- E-commerce: Units Ordered Last Week (2 of 5)
- E-commerce: Total Orders by Category (3 of 5)
- E-commerce: Earliest Order by Customer (4 of 5)
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.
WITH RankedOrders AS (
SELECT
customer_id,
order_date,
order_id,
ROW_NUMBER() OVER(PARTITION BY customer_id, order_date ORDER BY order_id) AS order_rank
FROM orders
)
SELECT
customer_id,
order_date,
order_id AS second_earliest_order_id
FROM RankedOrders
WHERE order_rank = 2
ORDER BY order_date, customer_id;
SELECT customer_id, order_date, order_id AS second_earliest_order_id FROM ( SELECT order_id, customer_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id, order_date ORDER BY order_id ASC) AS rank FROM orders ) WHERE rank = 2 ORDER BY order_date, customer_id