-1

Let's say that I have a table with a DateTime column, purchase_time and other order details(store_id, buyer_id, item_id, value) and I am trying to find the most popular item name that buyers order on their first purchase?

So far I am here, how do I find the most popular item?

select store_id,  from transactions 
where purchase_time in (select min(purchase_time) 
from transactions c1 group by c1.store_id);
O. Jones
  • 103,626
  • 17
  • 118
  • 172
Sharma Singh
  • 7
  • 1
  • This class of problem is called [tag:groupwise-maximum]. I guess in your case it should be called groupwise-minimum because you want `MIN(date)`, but it is still the same class of problem. – O. Jones Mar 04 '22 at 13:53

1 Answers1

0
SELECT TOP 1 i.item_name
FROM Transactions t
INNER JOIN Items i ON i.item_id = t.item_id
WHERE purchase_time = (select *, MIN(purchase_time) FROM Transactions)
GROUP BY 1
ORDER BY COUNT(*)  DESC
Ritika Jaiswal
  • 9
  • 2