E-commerce (4 of 5)
EasyAmazon 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.
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.