SQL for Data Science

Jorge Soto Avatar

Jorge Soto

Davita

About this project

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

Profile the data by finding the total number of records for each of the tables below:

i. Attribute table = 10,000

ii. Business table = 10,000

iii. Category table = 10,000

iv. Checkin table = 10,000

v. elite_years table = 10,000

vi. friend table = 10,000

vii. hours table = 10,000

viii. photo table = 10,000

ix. review table = 10,000

x. tip table = 10,000

xi. user table = 10,000

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 = id(10,000)

ii. Hours = business_id(1,562)

iii. Category = business_id(2,643)

iv. Attribute = business_id(1,115)

v. Review = business_id(8,090),user_id(1,562), id(10,000)

vi. Checkin = business_id(493)

vii. Photo = business_id(6493), id(10,000)

viii. Tip = id(3,979), user_id(537)

ix. User = id(10,000)

x. Friend = user_id(11)

xi. Elite_years = user_id(2,780)

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

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

Answer: NO

SQL code used to arrive at answer: Ran the code below on all columns in the table

SELECT

FROM

user

WHERE

compliment_photos IS NULL

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

List the cities with the most reviews in descending order:

SELECT

SUM(review_count), city

FROM

business

GROUP BY city

ORDER BY SUM(review_count) DESC

Copy and Paste the Result Below:

sum(review_count) | city |

+-------------------+-----------------+

| 82854 | Las Vegas |

| 34503 | Phoenix |

| 24113 | Toronto |

| 20614 | Scottsdale |

| 12523 | Charlotte |

| 10871 | Henderson |

| 10504 | Tempe |

| 9798 | Pittsburgh |

| 9448 | MontrÃ©al |

| 8112 | Chandler |

| 6875 | Mesa |

| 6380 | Gilbert |

| 5593 | Cleveland |

| 5265 | Madison |

| 4406 | Glendale |

| 3814 | Mississauga |

| 2792 | Edinburgh |

| 2624 | Peoria |

| 2438 | North Las Vegas |

| 2352 | Markham |

| 2029 | Champaign |

| 1849 | Stuttgart |

| 1520 | Surprise |

| 1465 | Lakewood |

| 1155 | Goodyear |

+-------------------+-----------------+

Find the distribution of star ratings to the business in the following cities:

i. Avon

SQL code used to arrive at answer:

SELECT

stars, COUNT(review_count) AS count

FROM

business

WHERE

city = 'Avon'

GROUP BY stars

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

stars | count |

+-------+-------+

| 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

stars, COUNT(review_count) AS count

FROM

business

WHERE

city = 'Beachwood'

GROUP BY stars

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

stars | count |

+-------+-------+

| 2.0 | 1 |

| 2.5 | 1 |

| 3.0 | 2 |

| 3.5 | 2 |

| 4.0 | 1 |

| 4.5 | 2 |

| 5.0 | 5 |

Find the top 3 users based on their total number of reviews:

SQL code used to arrive at answer:

SELECT

id, name, review_count

FROM

user

ORDER BY review_count DESC

LIMIT 3

Copy and Paste the Result Below:

id | name | review_count |

+------------------------+--------+--------------+

| -G7Zkl1wIWBBmD0KRy_sCw | Gerald | 2000 |

| -3s52C4zL_DHRK0ULG6qtg | Sara | 1629 |

| -8lbUNlXVSoXqaRRiHiSNg | Yuri | 1339 |

+------------------------+--------+--------------+

Does posing more reviews correlate with more fans?

Please explain your findings and interpretation of the results:

No, we can see in the results below that only 1/3 user with the most review

made it to the top 10 of users with the most fans. That was Gerald.

SELECT

id, name, review_count, fans

FROM

user

ORDER BY fans DESC , review_count DESC

LIMIT 10

id | name | review_count | fans |

+------------------------+-----------+--------------+------+

| -9I98YbNQnLdAmcYfb324Q | Amy | 609 | 503 |

| -8EnCioUmDygAbsYZmTeRQ | Mimi | 968 | 497 |

| --2vR0DIsmQ6WfcSzKWigw | Harald | 1153 | 311 |

| -G7Zkl1wIWBBmD0KRy_sCw | Gerald | 2000 | 253 |

| -0IiMAZI2SsQ7VmyzJjokQ | Christine | 930 | 173 |

| -g3XIcCb2b-BD0QBCcq2Sw | Lisa | 813 | 159 |

| -9bbDysuiWeo2VShFJJtcw | Cat | 377 | 133 |

| -FZBTkAZEXoP7CYvRV2ZwQ | William | 1215 | 126 |

| -9da1xk7zgnnfO1uTVYGkA | Fran | 862 | 124 |

| -lh59ko3dxChBSZ9U7LfUw | Lissa | 834 | 120 |

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

Answer: love returned 1,780 and hate returned 232. So more reviews included the word love

SQL code used to arrive at answer:

SELECT

text

FROM

review

WHERE

text LIKE '%love%'

-------------------------

SELECT

text

FROM

review

WHERE

text LIKE '%hate%'

Find the top 10 users with the most fans:

SQL code used to arrive at answer:

SELECT

id, name, fans

FROM

user

ORDER BY fans DESC

LIMIT 10

Copy and Paste the Result Below:

id | name | fans |

+------------------------+-----------+------+

| -9I98YbNQnLdAmcYfb324Q | Amy | 503 |

| -8EnCioUmDygAbsYZmTeRQ | Mimi | 497 |

| --2vR0DIsmQ6WfcSzKWigw | Harald | 311 |

| -G7Zkl1wIWBBmD0KRy_sCw | Gerald | 253 |

| -0IiMAZI2SsQ7VmyzJjokQ | Christine | 173 |

| -g3XIcCb2b-BD0QBCcq2Sw | Lisa | 159 |

| -9bbDysuiWeo2VShFJJtcw | Cat | 133 |

| -FZBTkAZEXoP7CYvRV2ZwQ | William | 126 |

| -9da1xk7zgnnfO1uTVYGkA | Fran | 124 |

| -lh59ko3dxChBSZ9U7LfUw | Lissa | 120

Part 2: Inferences and Analysis

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.

SELECT

b.id,

city,

category,

stars,

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 city ='Las Vegas' AND category = 'Shopping'

ORDER BY stars DESC, hours DESC

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

It looks like the location with the lowest star rating also has the most hours opened.

While the second lowest had the shortest hours of 6 hours. So it looks like 8 - 9 hour open

is the ideal hours of operation. To long could lead to poor store conditions and to short could

lead to customers going else where to shop.

2RhICgMZI6DK-t374VRoow

Monday - Friday 8 - 17:00 (9 hours)

0K2rKvqdBmiOAUTebcUohQ

Everyday 8 -16:30 (8.5 hours)

-9y2L9qSbqukVl8LzEOGdg

Monday - Saturday 10 -16:00 (6 hours)

1q44aWEcDN7uRvA2l8xpvQ

Everyday 8 - 22:00 (14 hours)

+------------------------+-----------+----------+-------+-----------------------+

ii. Do the two groups you chose to analyze have a different number of reviews?

SELECT

b.id,

city,

stars,

review_count

FROM

business b

INNER JOIN category c

ON  b.id = c.business_id

WHERE city ='Las Vegas' AND category = 'Shopping'

ORDER BY stars DESC

id | city | stars | review_count |

+------------------------+-----------+-------+--------------+

| 2RhICgMZI6DK-t374VRoow | Las Vegas | 5.0 | 4 |

| 0K2rKvqdBmiOAUTebcUohQ | Las Vegas | 4.5 | 32 |

| -9y2L9qSbqukVl8LzEOGdg | Las Vegas | 3.5 | 11 |

| 1q44aWEcDN7uRvA2l8xpvQ | Las Vegas | 2.5 | 6 |

iii. Are you able to infer anything from the location data provided between these two groups? Explain.

It looks like the location that is open 8.5 hours also has the most reviews.

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:

Locations that are open have 8,480 reviews compared to 1,520 reviews for closed locations

ii. Difference 2:

The number of locations with ratings 3.0 + is significantly higher compared to ratings below 3.0

id | name | stars | OpenLocations |

+------------------------+---------------------------------------------------+-------+---------------+

| 38s4jUZBkei3Gy-U5mtEJA | WH Smith | 4.0 | 2005 |

| 38rXDufRtJeGSMP6ducaCw | Cold Stone Creamery | 3.5 | 1778 |

| 38cVxRnCm9cYY_di-qaUQg | SanoGym | 5.0 | 1565 |

| 38OrCpBBQG-dzhxfXrFQWQ | Zack's Hamburgers | 4.5 | 1438 |

| 38Q56Fgl0OF1iLqq_Wwivg | Manna Food Truck | 3.0 | 1396 |

| 38tScZkvRLoa5h-wNPyjkw | Scott Roofing Company | 2.5 | 890 |

| 382Kmrk5rdFSMlL7iJG_qg | AZ City Movers | 2.0 | 566 |

| 37pHO_A0Zsx46X7zUEkvoQ | Extended Stay America - Pittsburgh - West Mifflin | 1.5 | 206 |

| 35jzGQtpvAoAbxNrjYYCEg | Galaxie Bleue | 1.0 | 156 |

+--------------------

SQL code used for analysis:

SELECT

id,

name,

is_open, COUNT(review_count)

FROM

business

GROUP BY is_open

-------------------------------------

SELECT

id,

name,

stars,

COUNT(is_open) OpenLocations

FROM

business

GROUP BY 3

ORDER BY 4 DESC, stars DESC

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:

I want to analysis 5 keywords that would indicate a good

review (excellent, perfect, best, amazing, wonderful) and 5 keywords that indicate a bad review (terrible,poor,awful, worst, avoid)

ii. Write 1-2 brief paragraphs on the type of data you will need for your analysis and why you chose that data:

In this analysis I wanted to see the average rating based on 5 positive and 5 negative keywords. I then wanted

to see how many reviews were being left and see if the users had a high number of fans as well. Since we won't want more negative reviews being left from user with high fan count. What I found was that more positive reviews were being left compared to negative. The positive reviews have 54 fans compared to zero fans from the negative reviews.

More positive reviews will be seen by more people so aim to get more positive reviews.

iii. Output of your finished dataset:

(excellent, perfect, best, amazing, wonderful)

NumberOfReviews | NumberOfFans | AvgRating |

+-----------------+--------------+-----------+

| 1081 | 54 | 4.12 |

+-----------------+--------------+-----------+

(terrible, poor, awful, worst, avoid)

| NumberOfReviews | NumberOfFans | AvgRating |

+-----------------+--------------+-----------+

| 13 | 0 | 2.4 |

+-----------------+--------------+-----------+

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

SELECT

SUM(review_count) AS NumberOfReviews,

SUM(fans) AS NumberOfFans,

AVG(stars) AS NumberOfFans

FROM

review r

INNER JOIN

user u ON r.id = u.id

WHERE

text LIKE '%perfect%'

OR text LIKE '%excellent%'

OR text LIKE '%best%'

OR text LIKE '%amazing%'

OR text LIKE '%wonderful%'

----------------------------------------

SELECT

SUM(review_count) AS NumberOfReviews,

SUM(fans) AS NumberOfFans,

AVG(stars) AS AvgRating

FROM

review r

INNER JOIN

user u ON r.id = u.id

WHERE

text LIKE '%terrible%'

OR text LIKE '%poor%'

OR text LIKE '%awful%'

OR text LIKE '%worst%'

OR text LIKE '%avoid%'

Jorge Soto Avatar

Jorge SotoDavita

More projects by Jorge

Restaurant Order Analysis

WDW/Universal Incident Analysis

Discussion and feedback(0 comments)