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.
- 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.
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 )More work
- Stay for the exhale
I turned a yoga teacher's classes into a membership product. I did the strategy, the design, the build and the operations.
- A bank reconciliation that explains each difference
A working sample of the reconciliation method from my years as Operations & Finance Lead. Excel formulas match 119 bank lines to 121 ledger entries, explain each difference and reconcile to 0.00.
- SQL case studies: revenue share, donor segments and a 3NF schema
Three SQL studies on small course datasets. Each one starts with a business question. Every figure is query output that you can run again.