Summary: We are interested in finding restaurants that "punch above their weight" by having ratings (stars) that are above their category averages. However, a restaurant can have multiple categories on Yelp. Each category has its own category average rating. So in Part A, we will write some SQL to find restaurants with ratings that are greater than allof their category average ratings.

Example 1: McDonalds #5 in Madison, WI: Rating = 4.5. Categorized as Restaurant (category average = 3.0), Fast Food (category average = 2.5) and Burgers (category average = 3.0). Since its rating = 4.5 is greater than 3.0 and2.5and3.0, we will claim that it "punches above its weight".

Example 2: Taco Bell #3 in Madison, WI: Rating = 4.5. Categorized as Restaurant (category average = 3.0), Fast Food (category average = 2.5) and Tacos (category average = 4.8). Since its rating = 4.5 is less than 4.8, it does not satisfy our requirement for "punching above its weight".

Question

dataset - https://www.yelp.com/dataset

an SQL query that reports the following in a single results table:

1. For every category that co-occurs with the "Restaurants" category on Yelp, report the number of distinct restaurants (i.e. distinct business IDs)in that category and the average rating among distinct restaurantsin that category. For example, since there are businesses that are "Nail Salons" and "Restaurants", "Nail Salons" is a category that co-occurs with "Restaurants". In the "Nail Salons" category, there are 4621 distinct businesses but only 9 distinct restaurants. So the average rating among restaurants in the "Nail Salons" category will be over just 9 ratings.
2. Only report categories having at least 100 distinct restaurants. Drop categories with less than 100 distinct restaurants from the results.
3. Order your results first in decreasing order of the average rating, then in decreasing order of the number of distinct restaurants, and then in increasing order of thecategory name.

Transcribed Image Text:

Expected Result (showing the first few rows): CATEGORY NAME Farmers Market Coffee Roasteries Food Stands AVERAGE RATING 4.39968 4.34480 4.34059 NUM DISTINCT RESTAURANTS 122 140 423 Hint: Here is an SQL query that calculates the average rating among all businesses in each category (not just restaurants). Expected Result (showing the first few rows): CATEGORY NAME Farmers Market Coffee Roasteries Food Stands AVERAGE RATING 4.39968 4.34480 4.34059 NUM DISTINCT RESTAURANTS 122 140 423 Hint: Here is an SQL query that calculates the average rating among all businesses in each category (not just restaurants).