SELECT
  city,
  name,
  bus_review_count,
  bus_avg_stars,
  city_avg_stars,
  all_avg_stars
FROM (
  SELECT
    city,
    name,
    bus_review_count,
    bus_avg_stars,
    AVG(bus_avg_stars) OVER (PARTITION BY city) as city_avg_stars,
    AVG(bus_avg_stars) OVER () as all_avg_stars,
    SUM(bus_review_count) OVER () AS total_reviews
  FROM (
    SELECT
      city,
      name,
      AVG(stars) as bus_avg_stars,
      SUM(review_count) AS bus_review_count
  	FROM
    	elastic.yelp.business
  	GROUP BY
    	city, name
  	)
  )
WHERE
  bus_review_count > 100
ORDER BY
  bus_avg_stars DESC,
  bus_review_count DESC