By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE transactions (
transaction_id int , user_id int , merchant_name varchar(255), transaction_date date , amount int
);
INSERT INTO transactions (transaction_id, user_id, merchant_name, transaction_date, amount)
VALUES (1, 1 ,'abc', '2015-01-17', 100),(2, 2, 'ced', '2015-2-17', 100),(3, 1, 'def', '2015-2-16', 120),
(4, 1 ,'ced', '2015-3-17', 110),(5, 1, 'ced', '2015-3-17', 150),(6, 2 ,'abc', '2015-4-17', 130),
(7, 3 ,'ced', '2015-12-17', 10),(8, 3 ,'abc', '2015-8-17', 100),(9, 2 ,'abc', '2015-12-17', 140),(10, 1,'abc', '2015-7-17', 100),
(11, 1 ,'abc', '2015-01-17', 120),(12, 2 ,'ced', '2015-12-23', 130);
select t.merchant_name, count(*)
from transactions t
join (
select merchant_name, sum(amount) amount
from transactions
group by merchant_name
order by 2 desc
limit 3
) top3
on t.merchant_name = top3.merchant_name
group by t.merchant_name
order by top3.amount asc
limit 1
| merchant_name | count(*) |
|---|---|
| def | 1 |