SQLAssignment_Preetha.txt

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
	


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 Primary key=id
ii. Hours = 1562 Foreign key=business_id
iii. Category = 2643 Foreign key=business_id
iv. Attribute = 1115 Foreign key=business_id
v. Review = 10000 Primary key=id, 8090 Foreign key=business_id, 9581 Foreign key=user_id
vi. Checkin = 493 Foreign key=business_id
vii. Photo = 10000 Primary key=id, 6493 Foreign key=business_id
viii. Tip = 537 Foreign key=user_id, 3979 Foreign key=business_id
ix. User = 10000 Primary key=id
x. Friend = 11 Foreign key =user_id
xi. Elite_years = 2780 Foreign key =user_id

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”
	
	
	SQL code used to arrive at answer: 

SELECT *
FROM user
WHERE id IS NULL
OR name IS NULL
OR review_count IS NULL
OR yelping_since IS NULL
OR useful IS NULL
OR funny IS NULL
OR cool IS NULL
OR fans IS NULL
OR average_stars IS NULL
OR compliment_hot IS NULL
OR compliment_more IS NULL
OR compliment_profile IS NULL
OR compliment_cute IS NULL
OR compliment_list IS NULL
OR compliment_note IS NULL
OR compliment_plain IS NULL
OR compliment_cool IS NULL
OR compliment_funny IS NULL
OR compliment_writer IS NULL
OR compliment_photos IS NULL;

	
	

	
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
	
		min:	1	max: 5		avg: 3.7082
		
	
	ii. Table: Business, Column: Stars
	
		min:	1.0	max: 5.0		avg: 3.6549
		
	
	iii. Table: Tip, Column: Likes
	
		min:	0	max: 2		avg: 0.0144
		
	
	iv. Table: Checkin, Column: Count
	
		min:	1	max:53		avg: 1.9414
		
	
	v. Table: User, Column: Review_count
	
		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, sum(review_count) AS total_reviews
	FROM business
	GROUP By city
	ORDER BY total_reviews DESC;

	
	
	Copy and Paste the Result Below:
	
+-----------------+---------------+
| city            | total_reviews |
+-----------------+---------------+
| Las Vegas       |         82854 |
| Phoenix         |         34503 |
| Toronto         |         24113 |
| Scottsdale      |         20614 |
| Charlotte       |         12523 |
| Henderson       |         10871 |
| Tempe           |         10504 |
| Pittsburgh      |          9798 |
| Montréal        |          9448 |
| Chandler        |          8112 |
| Mesa            |          6875 |
| Gilbert         |          6380 |
| Cleveland       |          5593 |
| Madison         |          5265 |
| Glendale        |          4406 |
| Mississauga     |          3814 |
| Edinburgh       |          2792 |
| Peoria          |          2624 |
| North Las Vegas |          2438 |
| Markham         |          2352 |
| Champaign       |          2029 |
| Stuttgart       |          1849 |
| Surprise        |          1520 |
| Lakewood        |          1465 |
| Goodyear        |          1155 |
+-----------------+---------------+
(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

+-------+------+-------------+
| stars | city | total_count |
+-------+------+-------------+
|   1.5 | Avon |           1 |
|   2.5 | Avon |           2 |
|   3.5 | Avon |           3 |
|   4.0 | Avon |           2 |
|   4.5 | Avon |           1 |
|   5.0 | Avon |           1 |
+-------+------+-------------+

SQL code used to arrive at answer:

SELECT stars, city, COUNT(review_count) AS total_count 
FROM business 
WHERE city='Avon' 
GROUP BY stars




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


ii. Beachwood

+-------+-----------+-------------+
| stars | city      | total_count |
+-------+-----------+-------------+
|   2.0 | Beachwood |           1 |
|   2.5 | Beachwood |           1 |
|   3.0 | Beachwood |           2 |
|   3.5 | Beachwood |           2 |
|   4.0 | Beachwood |           1 |
|   4.5 | Beachwood |           2 |
|   5.0 | Beachwood |           5 |
+-------+-----------+-------------+

SQL code used to arrive at answer:

SELECT stars, city, COUNT(review_count) AS total_count 
FROM business 
WHERE city='Beachwood' 
GROUP BY stars


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


7. Find the top 3 users based on their total number of reviews:
		
	SQL code used to arrive at answer:

select name, sum(review_count) AS total_review
FROM user 
GROUP BY id
ORDER BY total_review DESC
LIMIT 3

	
		
	Copy and Paste the Result Below:

+--------+--------------+
| name   | total_review |
+--------+--------------+
| Gerald |         2000 |
| Sara   |         1629 |
| Yuri   |         1339 |
+--------+--------------+
		


8. Does posing more reviews correlate with more fans?

Answer: No

	Please explain your findings and interpretation of the results:

	SELECT id, name, review_count, fans
	FROM user
	ORDER BY review_count DESC;

| id                     | name      | review_count | fans |
+------------------------+-----------+--------------+------+
| -G7Zkl1wIWBBmD0KRy_sCw | Gerald    |         2000 |  253 |
| -3s52C4zL_DHRK0ULG6qtg | Sara      |         1629 |   50 |
| -8lbUNlXVSoXqaRRiHiSNg | Yuri      |         1339 |   76 |

The user name Sara has more reviews but lesser fans than user name Yuri.


	
9. Are there more reviews with the word "love" or with the word "hate" in them?

	Answer: Yes

	
	SQL code used to arrive at answer:
SELECT id, text
FROM review
WHERE text LIKE ‘%love%’;

This gives out 1780 records.

SELECT id, text
FROM review
WHERE text LIKE '%hate%';

This gives out 232 records.

	
	
10. Find the top 10 users with the most fans:

	SQL code used to arrive at answer:
	
SELECT name, 
sum(fans) AS total_fans
FROM user
GROUP BY id
ORDER BY total_fans DESC
LIMIT 10

	
	Copy and Paste the Result Below:

+-----------+------------+
| name      | total_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.
	
i. Do the two groups you chose to analyze have a different distribution of hours?
Answer-Yes, the restaurants(category) in Las Vegas(city) with 2-3 and 4-5 ratings have slightly different distribution of hours, even for the same day of a week.

+-----------+-----------+-------------+----------------------+
| star_rank | city      | category    | hours                |
+-----------+-----------+-------------+----------------------+
| 2-3 stars | Las Vegas | Restaurants | Saturday|11:00-0:00  |
| 4-5 stars | Las Vegas | Restaurants | Saturday|10:00-23:00 |
+-----------+-----------+-------------+----------------------+

SELECT CASE WHEN b.stars >= 4 THEN "4-5 stars"
WHEN b.stars >= 2 AND b.stars <4 THEN "2-3 stars"
ELSE "below 2"
END star_rank,
 b.city, c.category, h.hours
FROM business b
INNER JOIN  category c ON b.id=c.business_id
INNER JOIN hours h ON c.business_id=h.business_id
WHERE b.city='Las Vegas' AND c.category='Restaurants'
GROUP BY star_rank;

ii. Do the two groups you chose to analyze have a different number of reviews?
Answer-Yes, restaurants with 4-5 star rank have more total number of reviews than that with 2-3star rank.

+-----------+-----------+-------------+----------------------+---------------+
| star_rank | city      | category    | hours                | total_reviews |
+-----------+-----------+-------------+----------------------+---------------+
| 2-3 stars | Las Vegas | Restaurants | Saturday|11:00-0:00  |           861 |
| 4-5 stars | Las Vegas | Restaurants | Saturday|10:00-23:00 |          6552 |
+-----------+-----------+-------------+----------------------+---------------+
         
SELECT CASE WHEN b.stars >= 4 THEN "4-5 stars"
WHEN b.stars >= 2 AND b.stars <=4 THEN "2-3 stars"
ELSE "below 2"
END star_rank,
 b.city, c.category, h.hours, SUM(b.review_count) AS total_reviews
FROM business b
INNER JOIN  category c ON b.id=c.business_id
INNER JOIN hours h ON c.business_id=h.business_id
WHERE b.city='Las Vegas' AND c.category='Restaurants'
GROUP BY star_rank;
         
iii. Are you able to infer anything from the location data provided between these two groups? Explain.
 Answer- Restaurants in two groups differ in postal codes. Precisely, they vary slightly in their latitude and are close by.
Nothing conclusive can be said by comparing address or neighborhood.

+-----------+-----------+-------------+----------------------+---------------+----------+-----------+-------------+
| star_rank | city      | category    | hours                | total_reviews | latitude | longitude | postal_code |
+-----------+-----------+-------------+----------------------+---------------+----------+-----------+-------------+
| 2-3 stars | Las Vegas | Restaurants | Saturday|11:00-0:00  |           861 |  36.1003 |   -115.21 | 89103       |
| 4-5 stars | Las Vegas | Restaurants | Saturday|10:00-23:00 |          6552 |  36.1267 |   -115.21 | 89146       |
+-----------+-----------+-------------+----------------------+---------------+----------+-----------+-------------+

SQL code used for analysis:

SELECT CASE WHEN b.stars >= 4 THEN "4-5 stars"
WHEN b.stars >= 2 AND b.stars <=4 THEN "2-3 stars"
ELSE "below 2"
END star_rank,
 b.city, c.category, h.hours, SUM(b.review_count) AS total_reviews, b.latitude, b.longitude, b.postal_code
 FROM business b
INNER JOIN  category c ON b.id=c.business_id
INNER JOIN hours h ON c.business_id=h.business_id
WHERE b.city='Las Vegas' AND c.category='Restaurants'
GROUP BY star_rank;

		
		
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.
		
i. Difference 1: stars or avg_rating
         
         
ii. Difference 2: review_count or total_reviews
         
+--------+--------------+---------------+------------+
| status | num_business | total_reviews | avg_rating |
+--------+--------------+---------------+------------+
| OPEN   |         8480 |        269300 |       3.68 |
| CLOSED |         1520 |         35261 |       3.52 |
+--------+--------------+---------------+------------+
         
         
SQL code used for analysis:

SELECT  CASE    WHEN is_open = 1 THEN "OPEN"
WHEN is_open = 0 THEN "CLOSED"
END status, 
count(distinct id) AS num_business,
sum(review_count) AS total_reviews,
round(avg(stars),2) AS avg_rating
FROM business
GROUP BY is_open
ORDER BY status DESC

	
	
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:

In the city of Las Vegas, I wanted to check how well the business star rating correlate with the review ratings and review counts for different business names.
         
         
ii. Write 1-2 brief paragraphs on the type of data you will need for your analysis and why you chose that data:

For the intended analysis, I first need to join the two tables: business and review. So I performed the inner join for the reviews having the same business id. Then I grouped the business by their names since its more human -readable. For easy comparison, the average of business and review star rating are presented as business_rating and review_rating respectively. Along with the review_count, this table provides a overview of how well does the business star rating correlate with reviewer’s rating for a specific business name.

                           
                  
iii. Output of your finished dataset:

+----------------------------------+-----------+-----------------+--------------+---------------+
| name                             | city      | business_rating | review_count | review_rating |
+----------------------------------+-----------+-----------------+--------------+---------------+
| 4E Kennels                       | Las Vegas |             5.0 |           24 |           5.0 |
| 808 Sushi                        | Las Vegas |             3.5 |          435 |           4.0 |
| 99 Ranch Market                  | Las Vegas |             3.0 |          136 |           3.0 |
| AG Jeans                         | Las Vegas |             4.5 |            3 |           4.0 |
| AT&T                             | Las Vegas |             3.5 |           23 |           5.0 |
| Albertsons                       | Las Vegas |             3.5 |           40 |           1.0 |
| AquaKnox                         | Las Vegas |             4.0 |          423 | 4.33333333333 |
| Arabesque Dance & Fitness Studio | Las Vegas |             4.5 |           12 |           5.0 |
| Art of Flavors                   | Las Vegas |             4.5 |          132 |           4.5 |
| BLT Steak                        | Las Vegas |             4.0 |          426 |           5.0 |
| Baja Fresh Mexican Grill         | Las Vegas |             3.5 |           38 |           4.0 |
| Big O Tires                      | Las Vegas |             3.5 |           77 |           1.0 |
| Big Wong Restaurant              | Las Vegas |             4.0 |          768 |           4.5 |
| Blueberry Hill Family Restaurant | Las Vegas |             4.0 |          618 |           5.0 |
| Braddah's Island Style           | Las Vegas |             4.0 |          566 |           5.0 |
| Bruce Scher's Tires              | Las Vegas |             2.5 |            7 |           1.0 |
| Buca di Beppo                    | Las Vegas |             3.0 |           94 |           4.0 |
| Budget Rent A Car                | Las Vegas |             2.0 |            7 |           4.0 |
| Caramel Bar & Lounge             | Las Vegas |             3.5 |           99 |           3.0 |
| Carson Kitchen                   | Las Vegas |             4.5 |         1410 |           3.0 |
| China Joy                        | Las Vegas |             2.5 |           26 |           5.0 |
| China One - West Charleston      | Las Vegas |             3.5 |          101 |           2.5 |
| Coffee Pub                       | Las Vegas |             4.5 |          371 |           5.0 |
| Crazy Horse Paris                | Las Vegas |             3.5 |          114 |           5.0 |
| Dal Toro Ristorante              | Las Vegas |             2.5 |          324 | 4.66666666667 |
+----------------------------------+-----------+-----------------+--------------+---------------+
(Output limit exceeded, 25 of 131 total rows shown)
         
         
iv. Provide the SQL code you used to create your final dataset:

SELECT b.name, b.city, AVG(b.stars) AS business_rating, b.review_count, AVG(r.stars) AS review_rating
FROM business b
INNER JOIN review r ON b.id=r.business_id
WHERE b.city='Las Vegas'
GROUP BY b.name;