To solve this problem, we need to find the second earliest order_id for each customer on each date where they placed at least two orders.

 To solve this problem, we need to find the second earliest order_id for each customer on each date where they placed at least two orders. The solution involves ranking orders for each customer by date and then selecting the second order based on the order_id (assuming lower order_id values correspond to earlier orders).

Approach

1. Group Orders by Customer and Date: Since each order can contain multiple items, we first need to get distinct orders per customer per date. This is done by grouping on customer_id, order_date, and order_id.

2. Rank Orders Within Each Group: For each customer and date, rank the orders by order_id in ascending order. The ranking uses ROW_NUMBER(), which assigns a unique rank to each order within the partition of customer_id and order_date.

3. Select Second Order: Filter the ranked orders to only include those with a rank of 2, which represents the second earliest order for each customer on each date where at least two orders were placed.

4. Order the Result: The final result is ordered by order_date and customer_id in ascending order.

Solution Code

sql

WITH ranked_orders 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
    GROUP BY customer_id, order_date, order_id
)
SELECT 
    customer_id,
    order_date,
    order_id AS second_earliest_order_id
FROM ranked_orders
WHERE order_rank = 2
ORDER BY order_date, customer_id;

Explanation

1. Common Table Expression (CTE) - ranked_orders:

   • Groups the orders by customer_id, order_date, and order_id to handle cases where multiple items per order exist.

   • Uses ROW_NUMBER() to assign a rank to each order within each partition (customer and date), ordered by order_id (ascending). The earliest order gets rank 1, the next rank 2, and so on.

2. Main Query:

   • Selects customer_id, order_date, and the corresponding order_id (aliased as second_earliest_order_id) from the CTE.

   • Filters results to only include rows where order_rank is 2, indicating the second earliest order.

   • Orders the final result by order_date and customer_id in ascending order to meet the specified output requirements.