SQL Group By Min Date When There Are Multiple Rows With Earliest Date

Francis Kim 0
Viewed 40

I am trying to come up with a query that will return the aggregate data for the earliest orders the customers have placed. Is it possible to do this when there are multiple rows/orders that were placed on the earliest purchase date for a given customer? For instance, I have these tables:

                                 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

Would greatly appreciate any help. Thanks!

mysql sql
2 Answers

Explanation:

  • cte will give you the first_purchase_date for each customer id and name
  • using that table to find aggregations that matches the customer id and the first_purchase_date

See db<>fiddle

WITH cte AS (
SELECT 
    orders.customer_id, 
    customers.name, 
    MIN(orders.created_at) AS first_purchase_date
FROM orders
INNER JOIN customers
    ON orders.customer_id = customers.id
INNER JOIN items
    ON orders.item_id = items.id
GROUP BY
    orders.customer_id,
    customers.name
)

SELECT
    cte.customer_id,
    cte.name,
    cte.first_purchase_date,
    COUNT(orders.item_id) AS n_items,
    SUM(items.price) AS total_price
FROM cte
INNER JOIN orders 
    ON cte.customer_id = orders.customer_id
    AND cte.first_purchase_date = orders.created_at
INNER JOIN items
    ON orders.item_id = items.id
GROUP BY 
    cte.customer_id,
    cte.name,
    cte.first_purchase_date
;
EmbraceNothingButFuture0 
select   customer_id
        ,name
        ,created_at as first_purchase_date
        ,count(*)   as n_items
        ,sum(price) as total_price
from    (
         select    o.customer_id
                  ,c.name
                  ,o.created_at
                  ,i.price
                 ,rank() over(partition by o.customer_id order by o.created_at) as rn
         from     orders o join items i on i.id = o.item_id join customers c on c.id = o.customer_id
        ) t
where    rn = 1
group by customer_id, name, created_at
customer_id name first_purchase_date n_items total_price
1 Sam 2018-08-01 00:00:00 1 200
2 Jimmy 2019-01-22 00:00:00 2 350

Fiddle

DannySlor0
Related