The purpose of this document is to show how MySQL can be used to join and filter tables, then use R to create a map of the data set. Essentially, this is a nerdy way of taking Yelp reviews and finding specific restaurants.
The data came from https://relational.fit.cvut.cz/dataset/Carcinogenesis This is a data dump with numerous schemas. For this instance, I used the Yelp schema.
MySQL was used to join 3 tables giving information about restaurants in Arizona. I then filtered the table to find restaurants in Tempe, AZ. The filter consisted of non-fast food restaurants, that are active, and have high ratings (4/5 at least). In addition, we only want reviews from people who have given over 10 reviews and have an average of at least 4 stars as reviewers, this will help ensure we are getting quality reviews. Lastly, we want outdoor restaurants for COVID. Below is the MySQL code and the map with restaurant information.
Use Yelp;
SELECT -- Columns of interest, with lat and long for mapping purposes
b.business_name AS 'Restaurant name',
ROUND (AVG(r.stars), 1) AS 'Average stars',
COUNT(r.stars) AS 'Number of reviews',
b.categories AS Cuisine,
b.full_address AS Address,
b.latitude,
b.longitude
FROM Business b -- Join the 3 tables
JOIN Reviews r USING (business_id)
JOIN Users u USING (user_id)
WHERE b.categories REGEXP 'Restaurants'
AND b.categories NOT REGEXP 'Fast'
AND b.city REGEXP 'Tempe'
AND b.active = 'true'
AND b.stars >= 4
AND u.average_stars >= 4
AND u.review_count >= 10
AND r.review_text REGEXP 'outside|outdoor|patio'
GROUP BY b.business_name, b.stars, b.categories, b.full_address, b.latitude, b. longitude
;