Most popular product titles to attract first-time buyers

Question

Suppose I have a table with a DateTime column called purchase_time and other order details (store_id, buyer_id, item_id, value) and I want to find what buyers purchased most often during their first purchase product name.

So far I've done this, but how do I find the most popular items?

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