Most popular item name that buyers order on their first purchase

Question

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); 

Answer 1

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