YelpDataCourseraPR2.txt

Author: Ivo Gollini Navarrete
Date: 07-Feb-2023

Data Scientist Role Play: Profiling and Analyzing the Yelp Dataset Coursera Worksheet

This is a 2-part assignment. In the first part, you are asked a series of questions that will help you profile and understand the data just like a data scientist would. For this first part of the assignment, you will be assessed both on the correctness of your findings, as well as the code you used to arrive at your answer. You will be graded on how easy your code is to read, so remember to use proper formatting and comments where necessary.

In the second part of the assignment, you are asked to come up with your own inferences and analysis of the data for a particular research question you want to answer. You will be required to prepare the dataset for the analysis you choose to do. As with the first part, you will be graded, in part, on how easy your code is to read, so use proper formatting and comments to illustrate and communicate your intent as required.

For both parts of this assignment, use this "worksheet." It provides all the questions you are being asked, and your job will be to transfer your answers and SQL coding where indicated into this worksheet so that your peers can review your work. You should be able to use any Text Editor (Windows Notepad, Apple TextEdit, Notepad ++, Sublime Text, etc.) to copy and paste your answers. If you are going to use Word or some other page layout application, just be careful to make sure your answers and code are lined appropriately.
In this case, you may want to save as a PDF to ensure your formatting remains intact for you reviewer.



Part 1: Yelp Dataset Profiling and Understanding

1. Profile the data by finding the total number of records for each of the tables below:
	
i. Attribute table = 10000
ii. Business table = 10000
iii. Category table = 10000
iv. Checkin table = 10000
v. elite_years table = 10000
vi. friend table = 10000
vii. hours table = 10000 
viii. photo table = 10000 
ix. review table = 10000 
x. tip table = 10000 
xi. user table = 10000 

Queries:

SELECT COUNT(*) as 
total_records
--from attribute;
--from business;
--from category;
--from checkin;
--from elite_years;
--from friend;
--from hours;
--from photo;
--from review;
--from tip;
from user;

Output example:

+---------------+
| total_records |
+---------------+
|         10000 |
+---------------+

2. Find the total distinct records by either the foreign key or primary key for each table. If two foreign keys are listed in the table, please specify which foreign key.

i. Business = 10000 

SELECT COUNT(DISTINCT id)
AS distinct_records
FROM business;

ii. Hours = 1562

SELECT COUNT(DISTINCT business_id)                    
AS distinct_records
FROM attribute;

iii. Category = 2643

SELECT COUNT (DISTINCT business_id)                    
AS distinct_records
FROM category;

iv. Attribute = 1115

SELECT COUNT (DISTINCT business_id)                    
AS distinct_records
FROM attribute;

v. Review = 10000

SELECT COUNT (DISTINCT id)                    
AS distinct_records
FROM review;

vi. Checkin = 493 

SELECT COUNT (DISTINCT business_id)                    
AS distinct_records
FROM checkin;

vii. Photo = 10000 

SELECT COUNT (DISTINCT id)                    
AS distinct_records
FROM photo;

viii. Tip = 537 

SELECT COUNT (DISTINCT user_id)                    
AS distinct_records
FROM tip;

ix. User = 10000 

SELECT COUNT (DISTINCT id)                    
AS distinct_records
FROM user;

x. Friend = 11 

SELECT COUNT (DISTINCT user_id)                    
AS distinct_records
FROM friend;

xi. Elite_years = 2780 

SELECT COUNT (DISTINCT user_id)                    
AS distinct_records
FROM elite_years;


Note: Primary Keys are denoted in the ER-Diagram with a yellow key icon.	



3. Are there any columns with null values in the Users table? Indicate "yes," or "no."

	Answer: No, there there are no NULL values in the "user" table.
	
	
	SQL code used to arrive at answer:

SELECT 
    SUM(id IS NULL),
    SUM(name IS NULL),
    SUM(review_count IS NULL),
    SUM(yelping_since IS NULL),
    SUM(useful IS NULL),
    SUM(funny IS NULL),
    SUM(cool IS NULL),
    SUM(fans IS NULL),
    SUM(average_stars IS NULL),
    SUM(compliment_hot IS NULL),
    SUM(compliment_more IS NULL),
    SUM(compliment_profile IS NULL),
    SUM(compliment_cute IS NULL),
    SUM(compliment_list IS NULL),
    SUM(compliment_note IS NULL),
    SUM(compliment_plain IS NULL),
    SUM(compliment_cool IS NULL),
    SUM(compliment_funny IS NULL),
    SUM(compliment_writer IS NULL),
    SUM(compliment_photos IS NULL),
FROM user;

	
4. For each table and column listed below, display the smallest (minimum), largest (maximum), and average (mean) value for the following fields:

	i. Table: Review, Column: Stars
	
SELECT 
    MIN(stars),
    MAX(stars),
    AVG(stars)
FROM review

		min:	1	max:	5	avg:	3.7082
	

	ii. Table: Business, Column: Stars

SELECT 
    MIN(stars),
    MAX(stars),
    AVG(stars)
FROM business
	
		min:	1.0	max:	5.0	avg:	3.6549
		
	
	iii. Table: Tip, Column: Likes

SELECT 
    MIN(likes),
    MAX(likes),
    AVG(likes)
FROM tip
	
		min:	0.0	max:	2	avg:	0.0144
		
	
	iv. Table: Checkin, Column: Count

SELECT 
    MIN(count),
    MAX(count),
    AVG(count)
FROM checkin
	
		min:	1	max:	53	avg:	1.9414
		
	
	v. Table: User, Column: Review_count

SELECT 
    MIN(review_count),
    MAX(review_count),
    AVG(review_count)
FROM user
	
		min:	0	max:	2000	avg:	24.2995
		


5. List the cities with the most reviews in descending order:

	SQL code used to arrive at answer:
	
SELECT city, 
    count(review_count) AS total_review
FROM business
GROUP BY city
ORDER BY total_review DESC
	
	Copy and Paste the Result Below:
	
+-----------------+--------------+
| city            | total_review |
+-----------------+--------------+
| Las Vegas       |         1561 |
| Phoenix         |         1001 |
| Toronto         |          985 |
| Scottsdale      |          497 |
| Charlotte       |          468 |
| Pittsburgh      |          353 |
| Montréal        |          337 |
| Mesa            |          304 |
| Henderson       |          274 |
| Tempe           |          261 |
| Edinburgh       |          239 |
| Chandler        |          232 |
| Cleveland       |          189 |
| Gilbert         |          188 |
| Glendale        |          188 |
| Madison         |          176 |
| Mississauga     |          150 |
| Stuttgart       |          141 |
| Peoria          |          105 |
| Markham         |           80 |
| Champaign       |           71 |
| North Las Vegas |           70 |
| North York      |           64 |
| Surprise        |           60 |
| Richmond Hill   |           54 |
+-----------------+--------------+
(Output limit exceeded, 25 of 362 total rows shown)
	
6. Find the distribution of star ratings to the business in the following cities:

i. Avon

SQL code used to arrive at answer:

SELECT 
    DISTINCT stars AS star_rating,
    COUNT(stars)
FROM business
where city = 'Avon'
GROUP BY star_rating

Copy and Paste the Resulting Table Below (2 columns – star rating and count):

+-------------+--------------+
| star_rating | COUNT(stars) |
+-------------+--------------+
|         1.5 |            1 |
|         2.5 |            2 |
|         3.5 |            3 |
|         4.0 |            2 |
|         4.5 |            1 |
|         5.0 |            1 |
+-------------+--------------+

ii. Beachwood

SQL code used to arrive at answer:

SELECT 
    DISTINCT stars AS star_rating,
    COUNT(stars)
FROM business
where city = 'Beachwood'
GROUP BY star_rating

Copy and Paste the Resulting Table Below (2 columns – star rating and count):
		
+-------------+--------------+
| star_rating | COUNT(stars) |
+-------------+--------------+
|         2.0 |            1 |
|         2.5 |            1 |
|         3.0 |            2 |
|         3.5 |            2 |
|         4.0 |            1 |
|         4.5 |            2 |
|         5.0 |            5 |
+-------------+--------------+

7. Find the top 3 users based on their total number of reviews:
		
	SQL code used to arrive at answer:
	
SELECT name,
    review_count
FROM user
ORDER BY review_count DESC
		
	Copy and Paste the Result Below:
		
+-----------+--------------+
| name      | review_count |
+-----------+--------------+
| Gerald    |         2000 |
| Sara      |         1629 |
| Yuri      |         1339 |
| .Hon      |         1246 |
| William   |         1215 |
| Harald    |         1153 |
| eric      |         1116 |
| Roanna    |         1039 |
| Mimi      |          968 |
| Christine |          930 |
| Ed        |          904 |
| Nicole    |          864 |
| Fran      |          862 |
| Mark      |          861 |
| Christina |          842 |
| Dominic   |          836 |
| Lissa     |          834 |
| Lisa      |          813 |
| Alison    |          775 |
| Sui       |          754 |
| Tim       |          702 |
| L         |          696 |
| Angela    |          694 |
| Crissy    |          676 |
| Lyn       |          675 |
+-----------+--------------+
(Output limit exceeded, 25 of 10000 total rows shown)


8. Does posing more reviews correlate with more fans?

	Please explain your findings and interpretation of the results:
	
No, having a high number of reviews doesn't corelate with a high number of fans.
If it was true, we would observe a constant increase of fans ans the number of reviews increases. Nevetheless, we observe users with high number of reviews and the user with the most fans only has 609 reviews.

SELECT name,
    review_count,
    fans
FROM user
ORDER BY review_count DESC
LIMIT 3

+-----------+--------------+------+
| name      | review_count | fans |
+-----------+--------------+------+
| Gerald    |         2000 |  253 |
| Sara      |         1629 |   50 |
| Yuri      |         1339 |   76 |
+-----------+--------------+------+

SELECT name,
    review_count,
    fans
FROM user
ORDER BY fans DESC
LIMIT 3

+-----------+--------------+------+
| name      | review_count | fans |
+-----------+--------------+------+
| Amy       |          609 |  503 |
| Mimi      |          968 |  497 |
| Harald    |         1153 |  311 |
+-----------+--------------+------+
	
9. Are there more reviews with the word "love" or with the word "hate" in them?

	Answer: 

There are more reviews with the word "love".
	
	SQL code used to arrive at answer:

select
    SUM(text LIKE '%love%') AS love_count,
    SUM(text LIKE '%hate%') AS hate_count
from review

+------------+------------+
| love_count | hate_count |
+------------+------------+
|       1780 |        232 |
+------------+------------+
	
10. Find the top 10 users with the most fans:

	SQL code used to arrive at answer:

SELECT name,
    fans
FROM user
ORDER BY fans DESC
LIMIT 10	

	Copy and Paste the Result Below:

+-----------+------+
| name      | fans |
+-----------+------+
| Amy       |  503 |
| Mimi      |  497 |
| Harald    |  311 |
| Gerald    |  253 |
| Christine |  173 |
| Lisa      |  159 |
| Cat       |  133 |
| William   |  126 |
| Fran      |  124 |
| Lissa     |  120 |
+-----------+------+
		

Part 2: Inferences and Analysis

1. Pick one city and category of your choice and group the businesses in that city or category by their overall star rating.
Compare the businesses with 2-3 stars to the businesses with 4-5 stars and answer the following questions. Include your code.

To pick the city with the most elements for one category, the following code finds the city with most categories:

SELECT b.city,
    c.category,
    count(c.category) AS count_category
FROM business b INNER JOIN category c ON b.id = c.business_id
GROUP BY c.category, b.city
ORDER BY count_category DESC
LIMIT 5

+---------+-------------+----------------+
| city    | category    | count_category |
+---------+-------------+----------------+
| Toronto | Restaurants |             10 |
| Phoenix | Restaurants |              6 |
| Toronto | Bars        |              4 |
| Phoenix | Food        |              4 |
| Toronto | Food        |              4 |
+---------+-------------+----------------+

City: Toronto
Category: Restaurants

i. Do the two groups you chose to analyze have a different distribution of hours?

Yes, looks like low rate business are available for longer periods while high rate business are open less days and less time.

ii. Do the two groups you chose to analyze have a different number of reviews?
         
Yes, together high rate business have a larger number of reviews.
         
iii. Are you able to infer anything from the location data provided between these two groups? Explain.

It's hard to tell, there is not enough information provided. We could find a relation if the dataset was larger.

SQL code used for analysis:
SELECT b.name,
    postal_code,
    CASE
        WHEN b.stars BETWEEN 2 AND 3 THEN '2-3 stars'
        WHEN b.stars BETWEEN 4 AND 5 THEN '4-5 stars'
    END AS stars_rating,
    CASE
        WHEN hours LIKE "%monday%" THEN 1
        WHEN hours LIKE "%tuesday%" THEN 2
        WHEN hours LIKE "%wednesday%" THEN 3
        WHEN hours LIKE "%thursday%" THEN 4
        WHEN hours LIKE "%friday%" THEN 5
        WHEN hours LIKE "%saturday%" THEN 6
        WHEN hours LIKE "%sunday%" THEN 7
    END AS week_day,
    h.hours,
    b.review_count
FROM (business b 
    INNER JOIN category c ON b.id = c.business_id)
    INNER JOIN hours h ON b.id = h.business_id
WHERE (b.city = 'Toronto' AND c.category = 'Restaurants')
    AND (stars_rating = '2-3 stars' OR stars_rating = '4-5 stars')
GROUP BY stars, week_day
ORDER BY week_day, stars_rating
		
2. Group business based on the ones that are open and the ones that are closed. What differences can you find between the ones that are still open and the ones that are closed? List at least two differences and the SQL code you used to arrive at your answer.

+---------+----------------+-------------+----------+
| is_open | business_count | sum_reviews | avg_rate |
+---------+----------------+-------------+----------+
|       0 |           1520 |       35261 |     3.52 |
|       1 |           8480 |      269300 |     3.68 |
+---------+----------------+-------------+----------+
	
i. Difference 1:
       
The number of open  business is higher. And, as expected, the number of reviews for open business is more than 7 times than for closed ones.
         
ii. Difference 2:

The star rate average of open business is slightly better. Meaning the star rate might not be a determinant factor for a business to close.
         
SQL code used for analysis:

SELECT is_open,
    count(*) AS business_count,
    sum(review_count) sum_reviews,
    ROUND(avg(stars), 2) AS avg_rate
FROM business
GROUP BY is_open


	
3. For this last part of your analysis, you are going to choose the type of analysis you want to conduct on the Yelp dataset and are going to prepare the data for analysis.

Ideas for analysis include: Parsing out keywords and business attributes for sentiment analysis, clustering businesses to find commonalities or anomalies between them, predicting the overall star rating for a business, predicting the number of fans a user will have, and so on. These are just a few examples to get you started, so feel free to be creative and come up with your own problem you want to solve. Provide answers, in-line, to all of the following:
	
i. Indicate the type of analysis you chose to do:
         
	A closed business is considered "failure" since it was not able to succed. This analysis will try to find a relationship of "succesful" business, their attributes and star rating.
         
ii. Write 1-2 brief paragraphs on the type of data you will need for your analysis and why you chose that data:
                           
	To understand the impact of different attributes we need the current state of the business, we will focus on "Restaurants" since its the category with the biggest data sample. 
	We will need to extract the star rating and a series of discriminative attributes to determine if a restaurant will stay open.
	
iii. Output of your finished dataset:
         
+---------+---------------+-----------+------------+-------------+--------+-------+---------+------------+---------+------+---------------+--------+-----------+-----------+
| is_open | is_open_count | stars_avg | PriceRange | BikeParking | WC_acc | HasTV | Outdoor | CreditCard | TakeOut | Kids | Table_Service | Caters | DriveThru | HappyHour |
+---------+---------------+-----------+------------+-------------+--------+-------+---------+------------+---------+------+---------------+--------+-----------+-----------+
|       0 |           122 |      3.43 |        2.0 |         1.0 |    1.0 |  0.67 |    0.43 |        1.0 |     1.0 | 0.83 |          0.67 |   0.67 |      None |       1.0 |
|       1 |           465 |      3.22 |       1.78 |        0.89 |    0.8 |  0.48 |    0.59 |        0.9 |    0.96 | 0.91 |          0.45 |   0.44 |      0.14 |       1.0 |
+---------+---------------+-----------+------------+-------------+--------+-------+---------+------------+---------+------+---------------+--------+-----------+-----------+

         
iv. Provide the SQL code you used to create your final dataset:

SELECT 
    b.is_open,
    COUNT(b.is_open) AS is_open_count,
    ROUND(AVG(b.stars), 2) AS stars_avg,
    ROUND(AVG(CASE
        WHEN a.name = 'RestaurantsPriceRange2' THEN a.value
    END), 2) AS PriceRange,
    ROUND(AVG(CASE
        WHEN a.name = 'BikeParking' THEN a.value
    END), 2) AS BikeParking,
    ROUND(AVG(CASE
        WHEN a.name = 'WheelchairAccessible' THEN a.value
    END), 2) AS WC_acc,
    ROUND(AVG(CASE
        WHEN a.name = 'HasTV' THEN a.value
    END), 2) AS HasTV,
    ROUND(AVG(CASE
        WHEN a.name = 'OutdoorSeating' THEN a.value
    END), 2) AS Outdoor,
    ROUND(AVG(CASE
        WHEN a.name = 'BusinessAcceptsCreditCards' THEN a.value
    END), 2) AS CreditCard,
    ROUND(AVG(CASE
        WHEN a.name = 'RestaurantsTakeOut' THEN a.value
    END), 2) AS TakeOut,
    ROUND(AVG(CASE
        WHEN a.name = 'GoodForKids' THEN a.value
    END), 2) AS Kids,
    ROUND(AVG(CASE
        WHEN a.name = 'RestaurantsTableService' THEN a.value
    END), 2) AS Table_Service,
    ROUND(AVG(CASE
        WHEN a.name = 'Caters' THEN a.value
    END), 2) AS Caters,
    ROUND(AVG(CASE
        WHEN a.name = 'DriveThru' THEN a.value
    END), 2) AS DriveThru,
    ROUND(AVG(CASE
        WHEN a.name = 'HappyHour' THEN a.value
    END), 2) AS HappyHour
FROM (business b LEFT JOIN category c ON b.id = c.business_id)
    LEFT JOIN attribute a ON b.id = a.business_id
WHERE c.category = 'Restaurants'
GROUP BY is_open