By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table c(client_id int, place_name varchar(50), total_purchase int, detail_purchase int);
insert into c values(1 ,'place1', 10, 7);
insert into c values(1 ,'place2', 10, 3);
insert into c values(2 ,'place1', 5, 4);
insert into c values(2 ,'place3', 5, 1);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
with cte as
(
select client_id,place_name,total_purchase,detail_purchase,detail_purchase*1.0/total_purchase percent,
count( client_id)over (partition by place_name) total_client
from c a
)
select place_name
from cte where percent>=0.7
group by place_name
having count(client_id)=max(total_client)
| place_name |
|---|
| place1 |