E-commerce: Earliest Order by Customer
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 earliest order_id for each customer for each date they placed an order (they can place multiple orders per day). Your output should have the following columns: customer_id, order_date, earliest_order_id. Order in ascending order date. Within the same date, order by ascending customer ID.
This question is 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: Second Earliest Order (5 of 5)
First, we select the customer_id, order_date, and the minimum order_id for each combination of customer and order date.
Using the GROUP BY clause, we group the records by customer_id and order_date. This ensures that we're looking at each unique combination of a customer and the specific dates they made an order.
Within these groups, we employ the MIN(order_id) function to fetch the smallest order_id, which corresponds to the earliest order placed by the customer on that date.
Finally, we sort the resulting dataset by order_date and then customer_id, ensuring a chronological and organized output.
SELECT
customer_id,
order_date,
MIN(order_id) AS earliest_order_id
FROM orders
GROUP BY customer_id, order_date
ORDER BY order_date, customer_id;
select customer_id, order_date, order_id as earliest_order_id from ( select customer_id, order_date, order_id, row_number() over (partition by customer_id, order_date order by order_date) as order_rank_per_customer from orders ) sub_table where order_rank_per_customer=1 order by order_date, customer_id;
Standard solution assumed that the order_id indicates which order comes in first. However this is not always the case, and sometime order_id can be random number without this assumption(e.g a 16-digit random number ). So I doubt that finding min(order_id) is the optimal solution.