part_1_yelp_dataset_profiling_and_understanding.txt

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
SELECT COUNT(*) AS total_attribute
FROM attribute;

ii. Business table = 10000
SELECT COUNT (*) AS total_business
FROM business;

iii. Category table = 10000
SELECT COUNT (*) AS total_category
FROM category;

iv. Checkin table = 10000
SELECT COUNT (*) AS total_checkin
FROM checkin;

v. elite_years table = 10000
SELECT COUNT (*) AS total_elite_years
FROM elite_years;

vi. friend table = 10000
SELECT COUNT (*) AS total_friend
FROM friend;

vii. hours table = 10000
SELECT COUNT (*) AS total_hours
FROM hours;

viii. photo table = 10000
SELECT COUNT (*) AS total_photo
FROM photo;

ix. review table = 10000
SELECT COUNT (*) AS total_review
FROM review;

x. tip table = 10000
SELECT COUNT (*) AS total_tip
FROM tip;

xi. user table = 10000
SELECT COUNT (*) AS total_user
FROM user;

	


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)
FROM business;

ii. Hours = 1562
SELECT COUNT(DISTINCT business_id)
FROM hours;

iii. Category = 2643
SELECT COUNT(DISTINCT business_id)
FROM category;

iv. Attribute = 1115
SELECT COUNT(DISTINCT business_id)
FROM attribute;

v. Review = 1115
SELECT COUNT(DISTINCT id)
FROM review;

vi. Checkin = 493
SELECT COUNT(DISTINCT business_id)
FROM checkin;

vii. Photo = 10000
SELECT COUNT(DISTINCT id)
FROM photo;

viii. Tip = 537
SELECT COUNT(DISTINCT user_id)
FROM tip;

ix. User = 10000
SELECT COUNT(DISTINCT id)
FROM user;

x. Friend = 11
SELECT COUNT(DISTINCT user_id)
FROM friend;

xi. Elite_years = 2780
SELECT COUNT(DISTINCT user_id)
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
	
	
	SQL code used to arrive at answer:
	
SELECT *
FROM user
WHERE 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
		
SELECT min(stars)
FROM review;

SELECT max(stars)
FROM review;
SELECT avg(stars)
FROM review;

	
	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)
FROM business
GROUP BY city
ORDER BY sum(review_count) DESC;




	
	Copy and Paste the Result Below:
	
+-----------------+-------------------+
| city            | sum(review_count) |
+-----------------+-------------------+
| 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

SQL code used to arrive at answer:

SELECT stars
    ,count(stars) 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(stars) 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 |
+-------+-------+

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 LIMIT 3;


		
	Copy and Paste the Result Below:
		
+--------+--------------+
| name   | review_count |
+--------+--------------+
| Gerald |         2000 |
| Sara   |         1629 |
| Yuri   |         1339 |
+--------+--------------+


8. Does posing more reviews correlate with more fans?

No. 

	Please explain your findings and interpretation of the results:

We don't see the number of fans decreasing as the review_count decreases, hence there seems to be no correlation (althoug the ideal in this cases would be a correlation function).
	

SELECT NAME
    ,review_count
    ,fans
FROM user
ORDER BY review_count DESC;

+-----------+--------------+------+
| name      | review_count | fans |
+-----------+--------------+------+
| Gerald    |         2000 |  253 |
| Sara      |         1629 |   50 |
| Yuri      |         1339 |   76 |
| .Hon      |         1246 |  101 |
| William   |         1215 |  126 |
| Harald    |         1153 |  311 |
| eric      |         1116 |   16 |
| Roanna    |         1039 |  104 |
| Mimi      |          968 |  497 |
| Christine |          930 |  173 |
| Ed        |          904 |   38 |
| Nicole    |          864 |   43 |
| Fran      |          862 |  124 |
| Mark      |          861 |  115 |
| Christina |          842 |   85 |
| Dominic   |          836 |   37 |
| Lissa     |          834 |  120 |
| Lisa      |          813 |  159 |
| Alison    |          775 |   61 |
| Sui       |          754 |   78 |
| Tim       |          702 |   35 |
| L         |          696 |   10 |
| Angela    |          694 |  101 |
| Crissy    |          676 |   25 |
| Lyn       |          675 |   45 |
+-----------+--------------+------+
(Output limit exceeded, 25 of 10000 total rows shown)

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

	Answer: There are more reviews with “love”

	
	SQL code used to arrive at answer:

	
SELECT (
		SELECT COUNT(TEXT)
		FROM review
		WHERE TEXT LIKE '%love%'
		) AS love
	,(
		SELECT COUNT(TEXT)
		FROM review
		WHERE TEXT LIKE '%hate%'
		) AS hate
FROM review LIMIT 1;


/* output of this query: 

+------+
| love |
+------+
| 1780 |
+------+
*/

SELECT count(*) AS hate
FROM review
WHERE TEXT LIKE "%hate%";

/* Output of this query: 

+------+
| hate |
+------+
|  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 |
+-----------+------+