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 columm with the percentage.

So I got something like this:

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?

CodePudding user response：

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:

db<>fiddle here

CodePudding user response：

If I understand your question correctly, you could just use a where statement

SELECT place_name
FROM purchases
where (detail_purchase/total_purchase) >=0.7
GROUP BY place_name

db fiddle

CodePudding user response：

Here is a clean solution based on group by and avg functions

select place_name
from c
group by place_name
having avg(percent)>=0.7

Fiddle