On March 11, the WHO declared Covid-19 a pandemic. From then on, countries started to close their borders and cities started to shut down. This article analyzes how the number of new restaurants opening changed since Covid started. The analysis was conducted based on the Yelp Dataset via Snowflake. March 11 was marked as the watershed to compare the new openings within one year prior to and one year after it. The results were shown on Tableau dashboard. The remaining of this article presents the steps of querying and analyzing restaurant opening data obtained from Yelp.
The original Yelp dataset has four tables: business, category, review, and user. Each table is composed of corresponding information. The business table has fields of business_id , name, address, city, state, etc.
The category table has two fields: business_id, category_name. Each business_id may belong to multiple categories.
The review table has fields of review_id, business_id, user_id, stars, text, date, etc. Each user_id may be associated to multiple business_ids and review_ids.
The user table has fields of user_id, name, review_count, yelping_since, etc.
First, the date of a restaurant’s first review was assumed as the opening date of that restaurant. Thus, the date of a restaurant’s first review was queried as the opening date, stored in a new table opening_date.
CREATE OR REPLACE VIEW opening_date AS
SELECT b.business_id, min(r.review_date) as first_review_date
FROM business b
INNER JOIN review r using (business_id)
INNER JOIN category c using (business_id)
WHERE c.category_name = 'Restaurants'
GROUP BY b.business_id;
SELECT * FROM opening_date;The results of new restaurants’ opening dates were shown in the following table.
Second, to obtain the number of new restaurants opening in each category before and after March 11, 2020, two tables, opening_pre_covid, opening_post_covid, were created. opening_pre_covid stores the number of new restaurants opening between March 11, 2019 and March 11, 2020 and opening_post_covid stores the number of new restaurants opening between March 12, 2020 and March 11, 2021.
CREATE OR REPLACE VIEW opening_pre_covid AS
SELECT c.category_name, COUNT(*) as num_openings_pre_covid
FROM opening_date o
INNER JOIN business b USING (business_id)
INNER JOIN category c USING (business_id)
WHERE (o.first_review_date >='2019-03-11') AND (o.first_review_date <= '2020-03-11')
GROUP BY category_name;
SELECT * FROM opening_pre_covid;
CREATE OR REPLACE VIEW opening_post_covid AS
SELECT c.category_name, COUNT(*) as num_openings_post_covid
FROM opening_date o
INNER JOIN business b USING (business_id)
INNER JOIN category c USING (business_id)
WHERE (o.first_review_date >='2020-03-12') AND (o.first_review_date <= '2021-03-11')
GROUP BY category_name;
SELECT * FROM opening_post_covid;The results of the number of new restaurants opening within one year before March 11, 2020 were shown in the following table.
The results of the number of new restaurants opening within one year after March 11, 2020 were shown in the following table.
Third, two tables (opening_pre_covid, opening_post_covid) were joined together based on their category name. Note that any null values were replaced with value 0. Then the percentage change in new restaurant opening within one year before and after March 11, 2020 were calculated by dividing the change of new restaurant opening by the number of new restaurant opening before March 11, 2020. Businesses less than 20 new openings in 2019 were not considered in this analysis.
SELECT category_name,
COALESCE(num_openings_pre_covid, 0),
COALESCE(num_openings_post_covid, 0),
ROUND(((COALESCE(num_openings_post_covid, 0) -
COALESCE(num_openings_pre_covid, 0))*100)/COALESCE(num_openings_pre_covid, 0), 0)
AS percentage_change
FROM opening_pre_covid
LEFT JOIN opening_post_covid USING (category_name)
WHERE num_openings_pre_covid >= 20
ORDER BY percentage_change DESCThe results of new restaurant openings change in percentage in a descending order were shown in the following table.
Lastly, Tableau was used to illustrate the results on the dashboard.
The number of new restaurants that opened in one year right after the pandemic started decreased sharply in almost all the categories in comparison with the year before. Only three categories, food delivery services, chicken shops, and food trucks, observed some increase, where food delivery witnessed the largest increase (135%).
