I have two tables, from which I need to select the latest purchase of each customer from the US. The query must present the user_id and the purchased item columns:
(I left out some of the records from both tables)
customers
| user_ID | created_at | country |
|---|---|---|
| A34212213 | 2021-06-03 14:31:35 | USA |
| BK76584I | 2022-01-02 14:31:35 | USA |
purchases
| user_ID | date_of_purchase | purchased_item |
|---|---|---|
| A34212213 | 2021-12-30 01:54:07 | Hamburger |
| A34212213 | 2021-12-02 17:02:59 | Chips |
| A34212213 | 2021-12-01 06:37:59 | Potatos |
| A34212213 | 2021-12-22 12:02:39 | Hamburger |
| BK76584I | 2021-12-02 08:55:30 | Ice |
| BK76584I | 2021-12-19 12:22:12 | Gummies |
| BK76584I | 2021-12-15 22:07:43 | Hotdogs |
the result would be:
| user_id | purchased_item |
|---|---|
| A34212213 | Hamburger |
| BK76584I | Gummies |
I tried writing this query:
SELECT c.user_id, purchased_item, MAX(date_of_purchase) FROM customers c JOIN purchases p ON c.user_id = p.user_id WHERE country = 'USA' GROUP BY c.user_id, purchased_item;
But it still gives me multiple records of the same user_ID, because the purchased_item is different in most records. I can’t seem to figure out how to get only one record of each user_ID and their latest purchase, using two or more fields in the gorup by clause.
Thanks in advance for all the help! 🙂
>Solution :
WITH CTE AS (
SELECT c.user_id, p.purchased_item, p.date_of_purchase,
ROW_NUMBER() OVER (PARTITION BY c.user_id ORDER BY p.date_of_purchase DESC) AS rn
FROM customers c
JOIN purchases p
ON c.user_id = p.user_id
WHERE country = 'USA'
)
SELECT user_id, purchased_item
FROM CTE
WHERE rn = 1;