Skip to content
Advertisement

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:

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:

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

Advertisement

Answer

Schema and insert statements:

Query:

Output:

place_name
place1

db<>fiddle here

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement