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:
SELECT cities, AVG(orders), COUNT(restaurants),
WHEN (AVG(orders) >= 120) THEN '1'
WHEN (120 > AVG(orders) >= 60) THEN '2'
WHEN (60 > AVG(orders) >= 30) THEN '3'
END AS ranking
GROUP BY 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.