BigQuery SQL on Yelp business and review tables

A group assignment. We answered seven business questions with BigQuery SQL on Yelp business, user, review and tip tables.

Role
Group member (coursework)
Period
2022
Stack
SQL, BigQuery, CTEs, Joins
Evidence
Published
Question
How do open and closed businesses, reviewer groups and restaurant categories compare in the Yelp data?
Data
The Yelp tables of the course in BigQuery: business, user, review and tip.
Method
CTEs for each group, joins on the city or the business ID, filters with HAVING and REGEXP_CONTAINS, date filters by year, and a ratio of each row to the total.
ResultPublished
Seven answered questions, each with its query and a short business reading of the output.

Coursework from a group assignment. This page shows the query methods only. It gives no result values and makes no claim about cause.

The questions

  • Open and closed businesses in the top cities: mean review count and mean star rating.
  • Frequent reviewers with a high mean star rating.
  • High-volume reviewers by the share of their reviews that other users marked useful.
  • Restaurant reviews by city and star band, as a share of the total.
  • Mexican restaurants in New Orleans, ranked by the number of tips.
  • Review engagement and mean stars before and after a given year.
  • Restaurants that accept credit cards against those that do not.

The query patterns

  • One CTE for each group, then a join of the groups on the city.
  • GROUP BY with HAVING to keep only the reviewers above a count.
  • REGEXP_CONTAINS to select businesses by category text.
  • EXTRACT(YEAR FROM date) to make the before and after groups.
  • RATIO_TO_REPORT to give each row its share of the total.

The limit of this work

The before and after comparison shows a difference between two periods. It does not show what caused the difference.

Open businesses by city (first CTE of question 2, part i)
WITH
 o AS (
 SELECT
   city,
   AVG(stars) AS open_avg_stars,
   AVG(review_count) AS open_avg_reviews
 FROM
   yelp.business
 WHERE
   is_open = 1
 GROUP BY
   city )