-1

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! :)

Ben Kitai
  • 9
  • 2

2 Answers2

0

The subquery filters the last item each user bought.

SELECT c.user_id, p.purchased_item
FROM customers c
JOIN purchases p ON c.user_id = p.user_id
WHERE country = 'USA' AND date_of_purchase = 
  (SELECT MAX(date_of_purchase) FROM purchases p2 WHERE p2.user_id = c.user_id);

TEST

http://sqlfiddle.com/#!9/1ffae8/2

Horaciux
  • 6,322
  • 2
  • 22
  • 41
  • please add an explanation – nbk Feb 01 '23 at 18:21
  • I would love to get an explanation as well. It does work, but i don't understand how do we get the latest purchase date of each user when going into the subquery. Thank! :) – Ben Kitai Feb 01 '23 at 18:36
  • the subquery runs for each row, it does not bring the whole set of user and last purchase. We can use a cte for that propose performance wise – Horaciux Feb 01 '23 at 18:39
0
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;
Horaciux
  • 6,322
  • 2
  • 22
  • 41
Bryan Wright
  • 16
  • Please add an explanation for all, so that others can understand what yu have done – nbk Feb 01 '23 at 18:22
  • I don't know why I haven't thought about using a window function with ROW_NUMBER(). This is perfect, thank you! :) – Ben Kitai Feb 01 '23 at 18:37