How to group rows in SQL by earliest date when are there are multiple rows with earliest date?

Francis Kim 0
Viewed 42

I am trying to come up with a query that will return the aggregate data for the earliest orders the customers have placed. What I cannot quite wrap my head around is how to construct this query when there are multiple orders placed on the same day for the earliest purchase date for customer 2.

                                 customers
id name created_at
1 Sam 2019-07-12
2 Jimmy 2019-01-22 
                                   items
id name price
1 Watch 200
2 Belt 75
3 Wallet 150 
                                  orders
id customer_id item_id created_at
1 1 1 2018-08-01
2 1 2 2018-08-11
3 2 1 2019-01-22
4 2 3 2019-01-22
5 2 2 2019-03-03 
                             expected query
customer_id name first_purchase_date n_items total_price
1 Sam 2018-08-01 1 200
2 Jimmy 2019-01-22 2 350

I currently have the following query set up, but this query is grouping by the customer_id such that the total number of items and total price do not reflect the earliest orders.

SELECT 
    orders.customer_id, 
    customers.name AS name, 
    MIN(orders.created_at) AS first_purchase_date,
    COUNT(*) as n_items,
    SUM(items.price) as total_price
FROM orders
INNER JOIN customers
    ON orders.customer_id = customers.id
INNER JOIN items
    ON orders.item_id = items.id
GROUP BY
    customers.id


         my incorrect query
customer_id name first_purchase_date n_items total_price
1 Sam 2018-08-01 2 275
2 Jimmy 2019-01-22 3 425
mysql sql
1 Answers

I recreated the tables in a SQL Server environment but this should help...I hope as it gives you the query result you're looking for. The data is exactly the same but I'm using temporary tables so hence the # prefixes.

SELECT 
    #orders.customer_id, 
    #customer.name AS name, 
    #orders.created_at as first_purchase_date,
   --MIN(#orders.created_at) AS first_purchase_date,
   COUNT(*) as n_items,
   SUM(#items.price) as total_price
FROM #orders
    INNER JOIN #customer
ON #orders.customer_id = #customer.id
INNER JOIN #items
ON #orders.item_id = #items.id
inner join 
(
    select customer_id, name, MIN(first_purchase_date) as 
    first_purchase_date
    from 
    (
        SELECT 
            #orders.customer_id, 
            #customer.name AS name, 
            #orders.created_at as first_purchase_date,
            --MIN(#orders.created_at) AS first_purchase_date,
            COUNT(*) as n_items,
            SUM(#items.price) as total_price
        FROM #orders
        INNER JOIN #customer
            ON #orders.customer_id = #customer.id
        INNER JOIN #items
            ON #orders.item_id = #items.id
            group by #orders.customer_id,#customer.name, #orders.created_at
    )base
    group by customer_id, name
) firstorders
on 
#customer.id = firstorders.customer_id
and
#customer.name = firstorders.name
and
#orders.created_at = firstorders.first_purchase_date
group by 
#orders.customer_id,#customer.name, #orders.created_at
i_coder0
Related