-
Notifications
You must be signed in to change notification settings - Fork 7
/
business_queries.sql
66 lines (61 loc) · 2.03 KB
/
business_queries.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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- Number of movies
unload
('SELECT company, COUNT(title)
FROM avg_ratings
GROUP BY company')
to 's3://bucket/insights/number_of_movies_'
iam_role '<SUBST-ROLE-ARN>'
allowoverwrite;
-- Percent of Amazon movies on Netflix
unload
('SELECT ROUND(
CAST((SELECT COUNT(*)
FROM (SELECT * FROM avg_ratings WHERE company = \'amazon\') a
INNER JOIN (SELECT * FROM avg_ratings WHERE company = \'netflix\') b
ON a.title = b.title) AS DECIMAL(8, 2))
/ CAST(COUNT(*) AS DECIMAL(8, 2))
, 3) AS percent_of_amazon_on_netflix
FROM avg_ratings
WHERE company = \'amazon\'')
to 's3://bucket/insights/percent_of_amazon_on_netflix_'
iam_role '<SUBST-ROLE-ARN>'
allowoverwrite;
-- Global average movie rating
unload
('SELECT company, AVG(CAST(avg_rating AS DECIMAL(3,2))) AS global_avg_rating
FROM avg_ratings
GROUP BY company')
to 's3://bucket/insights/global_avg_rating_'
iam_role '<SUBST-ROLE-ARN>'
allowoverwrite;
-- Most common release year
unload
('SELECT company, year
FROM
(SELECT year, company, COUNT(title) count, ROW_NUMBER() OVER (PARTITION BY company ORDER BY COUNT(year) DESC) rank
FROM avg_ratings
GROUP BY company, year)
WHERE rank = 1')
to 's3://bucket/insights/most_common_release_year_'
iam_role '<SUBST-ROLE-ARN>'
allowoverwrite;
-- Best Amazon
unload
('SELECT a.title, a.avg_rating
FROM (SELECT * FROM avg_ratings WHERE company = \'amazon\') a
LEFT JOIN (SELECT * FROM avg_ratings WHERE company = \'netflix\') b
ON a.title = b.title
WHERE a.avg_rating >= 4 AND b.title IS NULL')
to 's3://bucket/insights/best_amazon_'
iam_role '<SUBST-ROLE-ARN>'
allowoverwrite;
-- Best Netflix
unload
('SELECT a.title, a.avg_rating
FROM (SELECT * FROM avg_ratings WHERE company = \'netflix\') a
LEFT JOIN (SELECT * FROM avg_ratings WHERE company = \'amazon\') b
ON a.title = b.title
WHERE a.avg_rating >= 4 AND b.title IS NULL')
to 's3://bucket/insights/best_netflix_'
iam_role '<SUBST-ROLE-ARN>'
allowoverwrite;