PostgreSQL, CASE WHEN and IF

Question

I have a table with some cities, restaurants and orders (per restaurant) for a specific year.
From this dataset, I have a problem stating that:

In cities with average orders higher than 120, restaurants with less than 100 order should be aborted.

In cities with average orders higher than 60, restaurants with less than 40 order should be aborted.

In cities with average orders higher than 30, restaurants with less than 20 order should be aborted.

Until now, I have created a query that can give me a table with the average orders for each city and label them as 1, 2, 3 or 4 as follows:

SELECT cities, AVG(orders), COUNT(restaurants),

      CASE 

         WHEN (AVG(orders) >= 120) THEN '1'

         WHEN (120 > AVG(orders) >= 60) THEN '2'

         WHEN (60 > AVG(orders) >= 30) THEN '3'

         ELSE '4'

         END AS ranking

FROM c_cities

GROUP BY 1

So, now I want to create a statement that will check for example:
for rating = 1, if orders > 100 -> keep them or label them as something.
for rating = 2, if orders > 60 -> similar and etc.

I am trying to find the logic and the right statement in it.
I think that a CASE ... WHEN cannot solve my problem.
I am reading the documentation for IF statement, but I cannot make it work.

I would be grateful for any help you could provide :)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

I follow the logic in your bullet points, but not the rest of the question. To implement that logic, use window functions and then where. For instance, the following gets the restaurants that should be "aborted" according to your definition:

select cr.*
from (select c.city, c.restaurant, avg(orders) as restaurant_avg,
             (sum(orders) over (partition by city) /
              count(distinct restaurant) over (order by city)
             ) as city_avg
      from c_cities
      group by c.city, c.restaurant
     ) cr
where (city_avg > 120 and restuarant_avg < 100) or
      (city_avg > 100 and restaurant_avg < 80) or
      (city_avg > 30 and restaurant_avg < 20);

The idea is to get the city and restaurant average calculated for each row. Then you can just compare them.

PostgreSQL, CASE WHEN and IF

Sign up