Get all the names were total purchases are >70% of all the purchases per client

I have information about places and purchases in a table, and I need to find the name of all the places where, for all the clients who purchased in that place, the total of their purchases is at least 70%.

I’ve already found the answer on python, I’ve sum the number of purchases per client, then the purchases per client and place, and I’ve created a new column with the percentage.

So I got something like this:

client_id	place_name	total purchase	detail purchase	percent
1	place1	10	7	0.7
1	place2	10	3	0.3
2	place1	5	4	0.8
2	place3	5	1	0.2

So, my answer should be place1, since all the purchases in that place all the percentage is

= 70%.

I’ve developed this python code to solve it:

places = []
for  i in place name:
     if (c[c["place_name"]==i]["percent"]>=0.7).all():
       places.append(i) 

but now I need to do it in SQl, but I’m not sure if there’s a way to get a similar behavior with the function all in SQL I’ve been trying this:

SELECT place_name 
FROM c
GROUP BY place_name
HAVING total_purchase/detail_purchase >=0.7

But, It doesn’t work :c Any help?

Answer

Schema and insert statements:

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

Query:

 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)

Output:

place_name
place1

db<>fiddle here