By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE transactions(
Id INTEGER NOT NULL PRIMARY KEY
,payee VARCHAR(7) NOT NULL
,exp NUMERIC(5,2) NOT NULL
,category VARCHAR(9) NOT NULL
);
INSERT INTO transactions(Id,payee,exp,category) VALUES (1,'Amazon',25.00,'Gifts');
INSERT INTO transactions(Id,payee,exp,category) VALUES (2,'Amazon',30.21,'Books');
INSERT INTO transactions(Id,payee,exp,category) VALUES (3,'Amazon',12.98,'Gifts');
INSERT INTO transactions(Id,payee,exp,category) VALUES (4,'Amazon',15.00,'Groceries');
INSERT INTO transactions(Id,payee,exp,category) VALUES (5,'Amazon',14.54,'Gifts');
INSERT INTO transactions(Id,payee,exp,category) VALUES (6,'Alibaba',55.55,'Stock');
INSERT INTO transactions(Id,payee,exp,category) VALUES (7,'Alibaba',99.00,'Stock');
INSERT INTO transactions(Id,payee,exp,category) VALUES (8,'Alibaba',12.00,'Fun');
select payee, cnt, category
from (
select
payee,
count(*) cnt,
category,
rank() over(partition by payee order by count(*) desc) rn
from transactions
group by category, payee
) t
where rn = 1
| payee | cnt | category |
|---|---|---|
| Alibaba | 2 | Stock |
| Amazon | 3 | Gifts |