-
Notifications
You must be signed in to change notification settings - Fork 0
/
7.Study(Reviews&Stars).sql
40 lines (37 loc) · 1.91 KB
/
7.Study(Reviews&Stars).sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
/*
Study: Analyze whether there is a correlation between a user number of reviews
and average stars.
For this analysis, I needed data on review counts and average stars per user. I also needed to
create bins to group users by their review counts.
+-------------------------+------------------------------+
| reviewcountbins | ROUND(avg(average_stars), 2) |
+-------------------------+------------------------------+
| 8:500 or more reviews | 3.74 |
| 7:400 to 499 reviews | 3.68 |
| 6:300 to 399 reviews | 3.72 |
| 5:200 to 299 reviews | 3.77 |
| 4:100 to 199 reviews | 3.77 |
| 3:50 to 99 reviews | 3.75 |
| 2:25 to 49 reviews | 3.78 |
| 1:Fewer than 25 reviews | 3.68 |
| 0:No reviews | 3.89 |
+-------------------------+------------------------------+
I found no correlation between the users number of reviews and average stars.
*/
SELECT CASE
WHEN review_count = 0 THEN '0:No reviews'
WHEN review_count < 25 THEN '1:Fewer than 25 reviews'
WHEN review_count >= 25 AND review_count < 50 THEN '2:25 to 49 reviews'
WHEN review_count >= 50 AND review_count < 100 THEN '3:50 to 99 reviews'
WHEN review_count >= 100 AND review_count < 200 THEN '4:100 to 199 reviews'
WHEN review_count >= 200 AND review_count < 300 THEN '5:200 to 299 reviews'
WHEN review_count >= 300 AND review_count < 400 THEN '6:300 to 399 reviews'
WHEN review_count >= 400 AND review_count < 500 THEN '7:400 to 499 reviews'
WHEN review_count >= 500 THEN '8:500 or more reviews'
ELSE 'other'
END AS reviewcountbins
,ROUND(avg(average_stars), 2)
FROM user
GROUP BY reviewcountbins
ORDER BY reviewcountbins DESC
;