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!
2
Answers
Explanation:
cte
will give you the first_purchase_date for each customer id and nameSee db<>fiddle
Fiddle