Quarto enables you to weave together content and executable code into a finished document. To learn more about Quarto see https://quarto.org.
Running Code
When you click the Render button a document will be generated that includes both content and the output of embedded code. You can embed code like this:
# 2. VIEW THE MOVIE RATINGS TABLEratings <-dbGetQuery( con," SELECT * FROM movie_ratings; ")ratings
rating_id person movie rating
1 1 Alice Spider-Man: Brand New Day 5
2 2 Alice The Odyssey 4
3 3 Alice Toy Story 5 4
4 4 Alice The Super Mario Galaxy Movie 5
5 5 Alice Project Hail Mary 4
6 6 Brian Spider-Man: Brand New Day 4
7 7 Brian The Odyssey 5
8 8 Brian Toy Story 5 3
9 9 Brian The Super Mario Galaxy Movie 4
10 10 Brian Project Hail Mary 5
11 11 Carla Spider-Man: Brand New Day 5
12 12 Carla The Odyssey 4
13 13 Carla Toy Story 5 5
14 14 Carla The Super Mario Galaxy Movie 4
15 15 Carla Project Hail Mary 4
16 16 David Spider-Man: Brand New Day 3
17 17 David The Odyssey 5
18 18 David Toy Story 5 4
19 19 David The Super Mario Galaxy Movie 5
20 20 David Project Hail Mary 5
21 21 Elena Spider-Man: Brand New Day 4
22 22 Elena The Odyssey 4
23 23 Elena Toy Story 5 5
24 24 Elena The Super Mario Galaxy Movie 4
25 25 Elena Project Hail Mary 3
26 26 Frank Spider-Man: Brand New Day 5
27 27 Frank The Odyssey 3
28 28 Frank Toy Story 5 4
29 29 Frank The Super Mario Galaxy Movie 5
30 30 Frank Project Hail Mary 4
31 31 Grace Spider-Man: Brand New Day 4
32 32 Grace The Odyssey 5
33 33 Grace Toy Story 5 4
34 34 Grace The Super Mario Galaxy Movie 3
35 35 Grace Project Hail Mary 5
36 36 Alice Spider-Man: Brand New Day 5
37 37 Alice The Odyssey 4
38 38 Alice Toy Story 5 4
39 39 Alice The Super Mario Galaxy Movie 5
40 40 Alice Project Hail Mary 4
41 41 Brian Spider-Man: Brand New Day 4
42 42 Brian The Odyssey 5
43 43 Brian Toy Story 5 3
44 44 Brian The Super Mario Galaxy Movie 4
45 45 Brian Project Hail Mary 5
46 46 Carla Spider-Man: Brand New Day 5
47 47 Carla The Odyssey 4
48 48 Carla Toy Story 5 5
49 49 Carla The Super Mario Galaxy Movie 4
50 50 Carla Project Hail Mary 4
51 51 David Spider-Man: Brand New Day 3
52 52 David The Odyssey 5
53 53 David Toy Story 5 4
54 54 David The Super Mario Galaxy Movie 5
55 55 David Project Hail Mary 5
56 56 Elena Spider-Man: Brand New Day 4
57 57 Elena The Odyssey 4
58 58 Elena Toy Story 5 5
59 59 Elena The Super Mario Galaxy Movie 4
60 60 Elena Project Hail Mary 3
61 61 Frank Spider-Man: Brand New Day 5
62 62 Frank The Odyssey 3
63 63 Frank Toy Story 5 4
64 64 Frank The Super Mario Galaxy Movie 5
65 65 Frank Project Hail Mary 4
66 66 Grace Spider-Man: Brand New Day 4
67 67 Grace The Odyssey 5
68 68 Grace Toy Story 5 4
69 69 Grace The Super Mario Galaxy Movie 3
70 70 Grace Project Hail Mary 5
71 71 Alice Spider-Man: Brand New Day 5
72 72 Alice The Odyssey 4
73 73 Alice Toy Story 5 4
74 74 Alice The Super Mario Galaxy Movie 5
75 75 Alice Project Hail Mary 4
76 76 Brian Spider-Man: Brand New Day 4
77 77 Brian The Odyssey 5
78 78 Brian Toy Story 5 3
79 79 Brian The Super Mario Galaxy Movie 4
80 80 Brian Project Hail Mary 5
81 81 Carla Spider-Man: Brand New Day 5
82 82 Carla The Odyssey 4
83 83 Carla Toy Story 5 5
84 84 Carla The Super Mario Galaxy Movie 4
85 85 Carla Project Hail Mary 4
86 86 David Spider-Man: Brand New Day 3
87 87 David The Odyssey 5
88 88 David Toy Story 5 4
89 89 David The Super Mario Galaxy Movie 5
90 90 David Project Hail Mary 5
91 91 Elena Spider-Man: Brand New Day 4
92 92 Elena The Odyssey 4
93 93 Elena Toy Story 5 5
94 94 Elena The Super Mario Galaxy Movie 4
95 95 Elena Project Hail Mary 3
96 96 Frank Spider-Man: Brand New Day 5
97 97 Frank The Odyssey 3
98 98 Frank Toy Story 5 4
99 99 Frank The Super Mario Galaxy Movie 5
100 100 Frank Project Hail Mary 4
101 101 Grace Spider-Man: Brand New Day 4
102 102 Grace The Odyssey 5
103 103 Grace Toy Story 5 4
104 104 Grace The Super Mario Galaxy Movie 3
105 105 Grace Project Hail Mary 5
106 106 Alice Spider-Man: Brand New Day 5
107 107 Alice The Odyssey 4
108 108 Alice Toy Story 5 4
109 109 Alice The Super Mario Galaxy Movie 5
110 110 Alice Project Hail Mary 4
111 111 Brian Spider-Man: Brand New Day 4
112 112 Brian The Odyssey 5
113 113 Brian Toy Story 5 3
114 114 Brian The Super Mario Galaxy Movie 4
115 115 Brian Project Hail Mary 5
116 116 Carla Spider-Man: Brand New Day 5
117 117 Carla The Odyssey 4
118 118 Carla Toy Story 5 5
119 119 Carla The Super Mario Galaxy Movie 4
120 120 Carla Project Hail Mary 4
121 121 David Spider-Man: Brand New Day 3
122 122 David The Odyssey 5
123 123 David Toy Story 5 4
124 124 David The Super Mario Galaxy Movie 5
125 125 David Project Hail Mary 5
126 126 Elena Spider-Man: Brand New Day 4
127 127 Elena The Odyssey 4
128 128 Elena Toy Story 5 5
129 129 Elena The Super Mario Galaxy Movie 4
130 130 Elena Project Hail Mary 3
131 131 Frank Spider-Man: Brand New Day 5
132 132 Frank The Odyssey 3
133 133 Frank Toy Story 5 4
134 134 Frank The Super Mario Galaxy Movie 5
135 135 Frank Project Hail Mary 4
136 136 Grace Spider-Man: Brand New Day 4
137 137 Grace The Odyssey 5
138 138 Grace Toy Story 5 4
139 139 Grace The Super Mario Galaxy Movie 3
140 140 Grace Project Hail Mary 5
141 141 Alice Spider-Man: Brand New Day 5
142 142 Alice The Odyssey 4
143 143 Alice Toy Story 5 4
144 144 Alice The Super Mario Galaxy Movie 5
145 145 Alice Project Hail Mary 4
146 146 Brian Spider-Man: Brand New Day 4
147 147 Brian The Odyssey 5
148 148 Brian Toy Story 5 3
149 149 Brian The Super Mario Galaxy Movie 4
150 150 Brian Project Hail Mary 5
151 151 Carla Spider-Man: Brand New Day 5
152 152 Carla The Odyssey 4
153 153 Carla Toy Story 5 5
154 154 Carla The Super Mario Galaxy Movie 4
155 155 Carla Project Hail Mary 4
156 156 David Spider-Man: Brand New Day 3
157 157 David The Odyssey 5
158 158 David Toy Story 5 4
159 159 David The Super Mario Galaxy Movie 5
160 160 David Project Hail Mary 5
161 161 Elena Spider-Man: Brand New Day 4
162 162 Elena The Odyssey 4
163 163 Elena Toy Story 5 5
164 164 Elena The Super Mario Galaxy Movie 4
165 165 Elena Project Hail Mary 3
166 166 Frank Spider-Man: Brand New Day 5
167 167 Frank The Odyssey 3
168 168 Frank Toy Story 5 4
169 169 Frank The Super Mario Galaxy Movie 5
170 170 Frank Project Hail Mary 4
171 171 Grace Spider-Man: Brand New Day 4
172 172 Grace The Odyssey 5
173 173 Grace Toy Story 5 4
174 174 Grace The Super Mario Galaxy Movie 3
175 175 Grace Project Hail Mary 5
176 176 Alice Spider-Man: Brand New Day 5
177 177 Alice The Odyssey 4
178 178 Alice Toy Story 5 4
179 179 Alice The Super Mario Galaxy Movie 5
180 180 Alice Project Hail Mary 4
181 181 Brian Spider-Man: Brand New Day 4
182 182 Brian The Odyssey 5
183 183 Brian Toy Story 5 3
184 184 Brian The Super Mario Galaxy Movie 4
185 185 Brian Project Hail Mary 5
186 186 Carla Spider-Man: Brand New Day 5
187 187 Carla The Odyssey 4
188 188 Carla Toy Story 5 5
189 189 Carla The Super Mario Galaxy Movie 4
190 190 Carla Project Hail Mary 4
191 191 David Spider-Man: Brand New Day 3
192 192 David The Odyssey 5
193 193 David Toy Story 5 4
194 194 David The Super Mario Galaxy Movie 5
195 195 David Project Hail Mary 5
196 196 Elena Spider-Man: Brand New Day 4
197 197 Elena The Odyssey 4
198 198 Elena Toy Story 5 5
199 199 Elena The Super Mario Galaxy Movie 4
200 200 Elena Project Hail Mary 3
201 201 Frank Spider-Man: Brand New Day 5
202 202 Frank The Odyssey 3
203 203 Frank Toy Story 5 4
204 204 Frank The Super Mario Galaxy Movie 5
205 205 Frank Project Hail Mary 4
206 206 Grace Spider-Man: Brand New Day 4
207 207 Grace The Odyssey 5
208 208 Grace Toy Story 5 4
209 209 Grace The Super Mario Galaxy Movie 3
210 210 Grace Project Hail Mary 5
211 211 Alice Spider-Man: Brand New Day 5
212 212 Alice The Odyssey 4
213 213 Alice Toy Story 5 4
214 214 Alice The Super Mario Galaxy Movie 5
215 215 Alice Project Hail Mary 4
216 216 Brian Spider-Man: Brand New Day 4
217 217 Brian The Odyssey 5
218 218 Brian Toy Story 5 3
219 219 Brian The Super Mario Galaxy Movie 4
220 220 Brian Project Hail Mary 5
221 221 Carla Spider-Man: Brand New Day 5
222 222 Carla The Odyssey 4
223 223 Carla Toy Story 5 5
224 224 Carla The Super Mario Galaxy Movie 4
225 225 Carla Project Hail Mary 4
226 226 David Spider-Man: Brand New Day 3
227 227 David The Odyssey 5
228 228 David Toy Story 5 4
229 229 David The Super Mario Galaxy Movie 5
230 230 David Project Hail Mary 5
231 231 Elena Spider-Man: Brand New Day 4
232 232 Elena The Odyssey 4
233 233 Elena Toy Story 5 5
234 234 Elena The Super Mario Galaxy Movie 4
235 235 Elena Project Hail Mary 3
236 236 Frank Spider-Man: Brand New Day 5
237 237 Frank The Odyssey 3
238 238 Frank Toy Story 5 4
239 239 Frank The Super Mario Galaxy Movie 5
240 240 Frank Project Hail Mary 4
241 241 Grace Spider-Man: Brand New Day 4
242 242 Grace The Odyssey 5
243 243 Grace Toy Story 5 4
244 244 Grace The Super Mario Galaxy Movie 3
245 245 Grace Project Hail Mary 5
# 3. WINDOW FUNCTIONSwindow_functions <-dbGetQuery( con," SELECT person, movie, rating, -- Assign a unique row number within each person ROW_NUMBER() OVER ( PARTITION BY person ORDER BY rating DESC ) AS row_number, -- Rank movies for each person RANK() OVER ( PARTITION BY person ORDER BY rating DESC ) AS movie_rank, -- Rank movies without skipping numbers after ties DENSE_RANK() OVER ( PARTITION BY person ORDER BY rating DESC ) AS dense_rank, -- Average rating given by each person ROUND( AVG(rating) OVER ( PARTITION BY person ), 2 ) AS person_average, -- Average rating received by each movie ROUND( AVG(rating) OVER ( PARTITION BY movie ), 2 ) AS movie_average, -- Overall average rating ROUND( AVG(rating) OVER (), 2 ) AS global_average FROM movie_ratings ORDER BY person, movie_rank, movie; ")# View resultswindow_functions
person movie rating row_number movie_rank dense_rank
1 Alice Spider-Man: Brand New Day 5 8 1 1
2 Alice Spider-Man: Brand New Day 5 9 1 1
3 Alice Spider-Man: Brand New Day 5 6 1 1
4 Alice Spider-Man: Brand New Day 5 3 1 1
5 Alice Spider-Man: Brand New Day 5 1 1 1
6 Alice Spider-Man: Brand New Day 5 14 1 1
7 Alice Spider-Man: Brand New Day 5 13 1 1
8 Alice The Super Mario Galaxy Movie 5 4 1 1
9 Alice The Super Mario Galaxy Movie 5 7 1 1
10 Alice The Super Mario Galaxy Movie 5 10 1 1
11 Alice The Super Mario Galaxy Movie 5 11 1 1
12 Alice The Super Mario Galaxy Movie 5 12 1 1
13 Alice The Super Mario Galaxy Movie 5 2 1 1
14 Alice The Super Mario Galaxy Movie 5 5 1 1
15 Alice Project Hail Mary 4 34 15 2
16 Alice Project Hail Mary 4 16 15 2
17 Alice Project Hail Mary 4 33 15 2
18 Alice Project Hail Mary 4 26 15 2
19 Alice Project Hail Mary 4 32 15 2
20 Alice Project Hail Mary 4 30 15 2
21 Alice Project Hail Mary 4 27 15 2
22 Alice The Odyssey 4 18 15 2
23 Alice The Odyssey 4 19 15 2
24 Alice The Odyssey 4 20 15 2
25 Alice The Odyssey 4 21 15 2
26 Alice The Odyssey 4 22 15 2
27 Alice The Odyssey 4 23 15 2
28 Alice The Odyssey 4 24 15 2
29 Alice Toy Story 5 4 29 15 2
30 Alice Toy Story 5 4 28 15 2
31 Alice Toy Story 5 4 31 15 2
32 Alice Toy Story 5 4 25 15 2
33 Alice Toy Story 5 4 17 15 2
34 Alice Toy Story 5 4 15 15 2
35 Alice Toy Story 5 4 35 15 2
36 Brian Project Hail Mary 5 5 1 1
37 Brian Project Hail Mary 5 6 1 1
38 Brian Project Hail Mary 5 10 1 1
39 Brian Project Hail Mary 5 7 1 1
40 Brian Project Hail Mary 5 12 1 1
41 Brian Project Hail Mary 5 13 1 1
42 Brian Project Hail Mary 5 2 1 1
43 Brian The Odyssey 5 14 1 1
44 Brian The Odyssey 5 3 1 1
45 Brian The Odyssey 5 1 1 1
46 Brian The Odyssey 5 4 1 1
47 Brian The Odyssey 5 8 1 1
48 Brian The Odyssey 5 9 1 1
49 Brian The Odyssey 5 11 1 1
50 Brian Spider-Man: Brand New Day 4 18 15 2
51 Brian Spider-Man: Brand New Day 4 15 15 2
52 Brian Spider-Man: Brand New Day 4 16 15 2
53 Brian Spider-Man: Brand New Day 4 20 15 2
54 Brian Spider-Man: Brand New Day 4 22 15 2
55 Brian Spider-Man: Brand New Day 4 27 15 2
56 Brian Spider-Man: Brand New Day 4 28 15 2
57 Brian The Super Mario Galaxy Movie 4 25 15 2
58 Brian The Super Mario Galaxy Movie 4 26 15 2
59 Brian The Super Mario Galaxy Movie 4 23 15 2
60 Brian The Super Mario Galaxy Movie 4 17 15 2
61 Brian The Super Mario Galaxy Movie 4 19 15 2
62 Brian The Super Mario Galaxy Movie 4 24 15 2
63 Brian The Super Mario Galaxy Movie 4 21 15 2
64 Brian Toy Story 5 3 29 29 3
65 Brian Toy Story 5 3 34 29 3
66 Brian Toy Story 5 3 35 29 3
67 Brian Toy Story 5 3 33 29 3
68 Brian Toy Story 5 3 30 29 3
69 Brian Toy Story 5 3 31 29 3
70 Brian Toy Story 5 3 32 29 3
71 Carla Spider-Man: Brand New Day 5 2 1 1
72 Carla Spider-Man: Brand New Day 5 9 1 1
73 Carla Spider-Man: Brand New Day 5 7 1 1
74 Carla Spider-Man: Brand New Day 5 11 1 1
75 Carla Spider-Man: Brand New Day 5 12 1 1
76 Carla Spider-Man: Brand New Day 5 13 1 1
77 Carla Spider-Man: Brand New Day 5 3 1 1
78 Carla Toy Story 5 5 14 1 1
79 Carla Toy Story 5 5 4 1 1
80 Carla Toy Story 5 5 1 1 1
81 Carla Toy Story 5 5 5 1 1
82 Carla Toy Story 5 5 6 1 1
83 Carla Toy Story 5 5 8 1 1
84 Carla Toy Story 5 5 10 1 1
85 Carla Project Hail Mary 4 21 15 2
86 Carla Project Hail Mary 4 30 15 2
87 Carla Project Hail Mary 4 15 15 2
88 Carla Project Hail Mary 4 16 15 2
89 Carla Project Hail Mary 4 19 15 2
90 Carla Project Hail Mary 4 20 15 2
91 Carla Project Hail Mary 4 22 15 2
92 Carla The Odyssey 4 35 15 2
93 Carla The Odyssey 4 17 15 2
94 Carla The Odyssey 4 23 15 2
95 Carla The Odyssey 4 28 15 2
96 Carla The Odyssey 4 32 15 2
97 Carla The Odyssey 4 33 15 2
98 Carla The Odyssey 4 34 15 2
99 Carla The Super Mario Galaxy Movie 4 29 15 2
100 Carla The Super Mario Galaxy Movie 4 24 15 2
101 Carla The Super Mario Galaxy Movie 4 31 15 2
102 Carla The Super Mario Galaxy Movie 4 25 15 2
103 Carla The Super Mario Galaxy Movie 4 26 15 2
104 Carla The Super Mario Galaxy Movie 4 27 15 2
105 Carla The Super Mario Galaxy Movie 4 18 15 2
106 David Project Hail Mary 5 1 1 1
107 David Project Hail Mary 5 2 1 1
108 David Project Hail Mary 5 3 1 1
109 David Project Hail Mary 5 4 1 1
110 David Project Hail Mary 5 5 1 1
111 David Project Hail Mary 5 6 1 1
112 David Project Hail Mary 5 7 1 1
113 David The Odyssey 5 8 1 1
114 David The Odyssey 5 9 1 1
115 David The Odyssey 5 10 1 1
116 David The Odyssey 5 11 1 1
117 David The Odyssey 5 12 1 1
118 David The Odyssey 5 13 1 1
119 David The Odyssey 5 14 1 1
120 David The Super Mario Galaxy Movie 5 15 1 1
121 David The Super Mario Galaxy Movie 5 16 1 1
122 David The Super Mario Galaxy Movie 5 17 1 1
123 David The Super Mario Galaxy Movie 5 18 1 1
124 David The Super Mario Galaxy Movie 5 19 1 1
125 David The Super Mario Galaxy Movie 5 20 1 1
126 David The Super Mario Galaxy Movie 5 21 1 1
127 David Toy Story 5 4 22 22 2
128 David Toy Story 5 4 23 22 2
129 David Toy Story 5 4 24 22 2
130 David Toy Story 5 4 25 22 2
131 David Toy Story 5 4 26 22 2
132 David Toy Story 5 4 27 22 2
133 David Toy Story 5 4 28 22 2
134 David Spider-Man: Brand New Day 3 29 29 3
135 David Spider-Man: Brand New Day 3 30 29 3
136 David Spider-Man: Brand New Day 3 31 29 3
137 David Spider-Man: Brand New Day 3 32 29 3
138 David Spider-Man: Brand New Day 3 33 29 3
139 David Spider-Man: Brand New Day 3 34 29 3
140 David Spider-Man: Brand New Day 3 35 29 3
141 Elena Toy Story 5 5 1 1 1
142 Elena Toy Story 5 5 2 1 1
143 Elena Toy Story 5 5 3 1 1
144 Elena Toy Story 5 5 4 1 1
145 Elena Toy Story 5 5 5 1 1
146 Elena Toy Story 5 5 6 1 1
147 Elena Toy Story 5 5 7 1 1
148 Elena Spider-Man: Brand New Day 4 18 8 2
149 Elena Spider-Man: Brand New Day 4 9 8 2
150 Elena Spider-Man: Brand New Day 4 15 8 2
151 Elena Spider-Man: Brand New Day 4 17 8 2
152 Elena Spider-Man: Brand New Day 4 20 8 2
153 Elena Spider-Man: Brand New Day 4 21 8 2
154 Elena Spider-Man: Brand New Day 4 26 8 2
155 Elena The Odyssey 4 22 8 2
156 Elena The Odyssey 4 16 8 2
157 Elena The Odyssey 4 14 8 2
158 Elena The Odyssey 4 11 8 2
159 Elena The Odyssey 4 10 8 2
160 Elena The Odyssey 4 13 8 2
161 Elena The Odyssey 4 12 8 2
162 Elena The Super Mario Galaxy Movie 4 25 8 2
163 Elena The Super Mario Galaxy Movie 4 8 8 2
164 Elena The Super Mario Galaxy Movie 4 19 8 2
165 Elena The Super Mario Galaxy Movie 4 23 8 2
166 Elena The Super Mario Galaxy Movie 4 24 8 2
167 Elena The Super Mario Galaxy Movie 4 27 8 2
168 Elena The Super Mario Galaxy Movie 4 28 8 2
169 Elena Project Hail Mary 3 29 29 3
170 Elena Project Hail Mary 3 30 29 3
171 Elena Project Hail Mary 3 35 29 3
172 Elena Project Hail Mary 3 31 29 3
173 Elena Project Hail Mary 3 34 29 3
174 Elena Project Hail Mary 3 33 29 3
175 Elena Project Hail Mary 3 32 29 3
176 Frank Spider-Man: Brand New Day 5 4 1 1
177 Frank Spider-Man: Brand New Day 5 5 1 1
178 Frank Spider-Man: Brand New Day 5 11 1 1
179 Frank Spider-Man: Brand New Day 5 12 1 1
180 Frank Spider-Man: Brand New Day 5 14 1 1
181 Frank Spider-Man: Brand New Day 5 1 1 1
182 Frank Spider-Man: Brand New Day 5 2 1 1
183 Frank The Super Mario Galaxy Movie 5 9 1 1
184 Frank The Super Mario Galaxy Movie 5 10 1 1
185 Frank The Super Mario Galaxy Movie 5 13 1 1
186 Frank The Super Mario Galaxy Movie 5 3 1 1
187 Frank The Super Mario Galaxy Movie 5 6 1 1
188 Frank The Super Mario Galaxy Movie 5 7 1 1
189 Frank The Super Mario Galaxy Movie 5 8 1 1
190 Frank Project Hail Mary 4 28 15 2
191 Frank Project Hail Mary 4 17 15 2
192 Frank Project Hail Mary 4 20 15 2
193 Frank Project Hail Mary 4 15 15 2
194 Frank Project Hail Mary 4 23 15 2
195 Frank Project Hail Mary 4 16 15 2
196 Frank Project Hail Mary 4 25 15 2
197 Frank Toy Story 5 4 18 15 2
198 Frank Toy Story 5 4 19 15 2
199 Frank Toy Story 5 4 21 15 2
200 Frank Toy Story 5 4 22 15 2
201 Frank Toy Story 5 4 24 15 2
202 Frank Toy Story 5 4 26 15 2
203 Frank Toy Story 5 4 27 15 2
204 Frank The Odyssey 3 29 29 3
205 Frank The Odyssey 3 30 29 3
206 Frank The Odyssey 3 31 29 3
207 Frank The Odyssey 3 32 29 3
208 Frank The Odyssey 3 33 29 3
209 Frank The Odyssey 3 34 29 3
210 Frank The Odyssey 3 35 29 3
211 Grace Project Hail Mary 5 8 1 1
212 Grace Project Hail Mary 5 1 1 1
213 Grace Project Hail Mary 5 10 1 1
214 Grace Project Hail Mary 5 11 1 1
215 Grace Project Hail Mary 5 6 1 1
216 Grace Project Hail Mary 5 13 1 1
217 Grace Project Hail Mary 5 7 1 1
218 Grace The Odyssey 5 14 1 1
219 Grace The Odyssey 5 3 1 1
220 Grace The Odyssey 5 4 1 1
221 Grace The Odyssey 5 5 1 1
222 Grace The Odyssey 5 9 1 1
223 Grace The Odyssey 5 12 1 1
224 Grace The Odyssey 5 2 1 1
225 Grace Spider-Man: Brand New Day 4 18 15 2
226 Grace Spider-Man: Brand New Day 4 15 15 2
227 Grace Spider-Man: Brand New Day 4 21 15 2
228 Grace Spider-Man: Brand New Day 4 24 15 2
229 Grace Spider-Man: Brand New Day 4 25 15 2
230 Grace Spider-Man: Brand New Day 4 26 15 2
231 Grace Spider-Man: Brand New Day 4 27 15 2
232 Grace Toy Story 5 4 23 15 2
233 Grace Toy Story 5 4 28 15 2
234 Grace Toy Story 5 4 17 15 2
235 Grace Toy Story 5 4 16 15 2
236 Grace Toy Story 5 4 19 15 2
237 Grace Toy Story 5 4 20 15 2
238 Grace Toy Story 5 4 22 15 2
239 Grace The Super Mario Galaxy Movie 3 31 29 3
240 Grace The Super Mario Galaxy Movie 3 32 29 3
241 Grace The Super Mario Galaxy Movie 3 33 29 3
242 Grace The Super Mario Galaxy Movie 3 34 29 3
243 Grace The Super Mario Galaxy Movie 3 35 29 3
244 Grace The Super Mario Galaxy Movie 3 29 29 3
245 Grace The Super Mario Galaxy Movie 3 30 29 3
person_average movie_average global_average
1 4.4 4.29 4.26
2 4.4 4.29 4.26
3 4.4 4.29 4.26
4 4.4 4.29 4.26
5 4.4 4.29 4.26
6 4.4 4.29 4.26
7 4.4 4.29 4.26
8 4.4 4.29 4.26
9 4.4 4.29 4.26
10 4.4 4.29 4.26
11 4.4 4.29 4.26
12 4.4 4.29 4.26
13 4.4 4.29 4.26
14 4.4 4.29 4.26
15 4.4 4.29 4.26
16 4.4 4.29 4.26
17 4.4 4.29 4.26
18 4.4 4.29 4.26
19 4.4 4.29 4.26
20 4.4 4.29 4.26
21 4.4 4.29 4.26
22 4.4 4.29 4.26
23 4.4 4.29 4.26
24 4.4 4.29 4.26
25 4.4 4.29 4.26
26 4.4 4.29 4.26
27 4.4 4.29 4.26
28 4.4 4.29 4.26
29 4.4 4.14 4.26
30 4.4 4.14 4.26
31 4.4 4.14 4.26
32 4.4 4.14 4.26
33 4.4 4.14 4.26
34 4.4 4.14 4.26
35 4.4 4.14 4.26
36 4.2 4.29 4.26
37 4.2 4.29 4.26
38 4.2 4.29 4.26
39 4.2 4.29 4.26
40 4.2 4.29 4.26
41 4.2 4.29 4.26
42 4.2 4.29 4.26
43 4.2 4.29 4.26
44 4.2 4.29 4.26
45 4.2 4.29 4.26
46 4.2 4.29 4.26
47 4.2 4.29 4.26
48 4.2 4.29 4.26
49 4.2 4.29 4.26
50 4.2 4.29 4.26
51 4.2 4.29 4.26
52 4.2 4.29 4.26
53 4.2 4.29 4.26
54 4.2 4.29 4.26
55 4.2 4.29 4.26
56 4.2 4.29 4.26
57 4.2 4.29 4.26
58 4.2 4.29 4.26
59 4.2 4.29 4.26
60 4.2 4.29 4.26
61 4.2 4.29 4.26
62 4.2 4.29 4.26
63 4.2 4.29 4.26
64 4.2 4.14 4.26
65 4.2 4.14 4.26
66 4.2 4.14 4.26
67 4.2 4.14 4.26
68 4.2 4.14 4.26
69 4.2 4.14 4.26
70 4.2 4.14 4.26
71 4.4 4.29 4.26
72 4.4 4.29 4.26
73 4.4 4.29 4.26
74 4.4 4.29 4.26
75 4.4 4.29 4.26
76 4.4 4.29 4.26
77 4.4 4.29 4.26
78 4.4 4.14 4.26
79 4.4 4.14 4.26
80 4.4 4.14 4.26
81 4.4 4.14 4.26
82 4.4 4.14 4.26
83 4.4 4.14 4.26
84 4.4 4.14 4.26
85 4.4 4.29 4.26
86 4.4 4.29 4.26
87 4.4 4.29 4.26
88 4.4 4.29 4.26
89 4.4 4.29 4.26
90 4.4 4.29 4.26
91 4.4 4.29 4.26
92 4.4 4.29 4.26
93 4.4 4.29 4.26
94 4.4 4.29 4.26
95 4.4 4.29 4.26
96 4.4 4.29 4.26
97 4.4 4.29 4.26
98 4.4 4.29 4.26
99 4.4 4.29 4.26
100 4.4 4.29 4.26
101 4.4 4.29 4.26
102 4.4 4.29 4.26
103 4.4 4.29 4.26
104 4.4 4.29 4.26
105 4.4 4.29 4.26
106 4.4 4.29 4.26
107 4.4 4.29 4.26
108 4.4 4.29 4.26
109 4.4 4.29 4.26
110 4.4 4.29 4.26
111 4.4 4.29 4.26
112 4.4 4.29 4.26
113 4.4 4.29 4.26
114 4.4 4.29 4.26
115 4.4 4.29 4.26
116 4.4 4.29 4.26
117 4.4 4.29 4.26
118 4.4 4.29 4.26
119 4.4 4.29 4.26
120 4.4 4.29 4.26
121 4.4 4.29 4.26
122 4.4 4.29 4.26
123 4.4 4.29 4.26
124 4.4 4.29 4.26
125 4.4 4.29 4.26
126 4.4 4.29 4.26
127 4.4 4.14 4.26
128 4.4 4.14 4.26
129 4.4 4.14 4.26
130 4.4 4.14 4.26
131 4.4 4.14 4.26
132 4.4 4.14 4.26
133 4.4 4.14 4.26
134 4.4 4.29 4.26
135 4.4 4.29 4.26
136 4.4 4.29 4.26
137 4.4 4.29 4.26
138 4.4 4.29 4.26
139 4.4 4.29 4.26
140 4.4 4.29 4.26
141 4.0 4.14 4.26
142 4.0 4.14 4.26
143 4.0 4.14 4.26
144 4.0 4.14 4.26
145 4.0 4.14 4.26
146 4.0 4.14 4.26
147 4.0 4.14 4.26
148 4.0 4.29 4.26
149 4.0 4.29 4.26
150 4.0 4.29 4.26
151 4.0 4.29 4.26
152 4.0 4.29 4.26
153 4.0 4.29 4.26
154 4.0 4.29 4.26
155 4.0 4.29 4.26
156 4.0 4.29 4.26
157 4.0 4.29 4.26
158 4.0 4.29 4.26
159 4.0 4.29 4.26
160 4.0 4.29 4.26
161 4.0 4.29 4.26
162 4.0 4.29 4.26
163 4.0 4.29 4.26
164 4.0 4.29 4.26
165 4.0 4.29 4.26
166 4.0 4.29 4.26
167 4.0 4.29 4.26
168 4.0 4.29 4.26
169 4.0 4.29 4.26
170 4.0 4.29 4.26
171 4.0 4.29 4.26
172 4.0 4.29 4.26
173 4.0 4.29 4.26
174 4.0 4.29 4.26
175 4.0 4.29 4.26
176 4.2 4.29 4.26
177 4.2 4.29 4.26
178 4.2 4.29 4.26
179 4.2 4.29 4.26
180 4.2 4.29 4.26
181 4.2 4.29 4.26
182 4.2 4.29 4.26
183 4.2 4.29 4.26
184 4.2 4.29 4.26
185 4.2 4.29 4.26
186 4.2 4.29 4.26
187 4.2 4.29 4.26
188 4.2 4.29 4.26
189 4.2 4.29 4.26
190 4.2 4.29 4.26
191 4.2 4.29 4.26
192 4.2 4.29 4.26
193 4.2 4.29 4.26
194 4.2 4.29 4.26
195 4.2 4.29 4.26
196 4.2 4.29 4.26
197 4.2 4.14 4.26
198 4.2 4.14 4.26
199 4.2 4.14 4.26
200 4.2 4.14 4.26
201 4.2 4.14 4.26
202 4.2 4.14 4.26
203 4.2 4.14 4.26
204 4.2 4.29 4.26
205 4.2 4.29 4.26
206 4.2 4.29 4.26
207 4.2 4.29 4.26
208 4.2 4.29 4.26
209 4.2 4.29 4.26
210 4.2 4.29 4.26
211 4.2 4.29 4.26
212 4.2 4.29 4.26
213 4.2 4.29 4.26
214 4.2 4.29 4.26
215 4.2 4.29 4.26
216 4.2 4.29 4.26
217 4.2 4.29 4.26
218 4.2 4.29 4.26
219 4.2 4.29 4.26
220 4.2 4.29 4.26
221 4.2 4.29 4.26
222 4.2 4.29 4.26
223 4.2 4.29 4.26
224 4.2 4.29 4.26
225 4.2 4.29 4.26
226 4.2 4.29 4.26
227 4.2 4.29 4.26
228 4.2 4.29 4.26
229 4.2 4.29 4.26
230 4.2 4.29 4.26
231 4.2 4.29 4.26
232 4.2 4.14 4.26
233 4.2 4.14 4.26
234 4.2 4.14 4.26
235 4.2 4.14 4.26
236 4.2 4.14 4.26
237 4.2 4.14 4.26
238 4.2 4.14 4.26
239 4.2 4.29 4.26
240 4.2 4.29 4.26
241 4.2 4.29 4.26
242 4.2 4.29 4.26
243 4.2 4.29 4.26
244 4.2 4.29 4.26
245 4.2 4.29 4.26
# 4. DISPLAY ONLY THE HIGHEST-RATED MOVIES FOR EACH PERSONtop_movies <-dbGetQuery( con," WITH ranked_movies AS ( SELECT person, movie, rating, RANK() OVER ( PARTITION BY person ORDER BY rating DESC ) AS movie_rank FROM movie_ratings ) SELECT person, movie, rating FROM ranked_movies WHERE movie_rank = 1 ORDER BY person; ")top_movies
person movie rating
1 Alice Spider-Man: Brand New Day 5
2 Alice The Super Mario Galaxy Movie 5
3 Alice The Super Mario Galaxy Movie 5
4 Alice The Super Mario Galaxy Movie 5
5 Alice Spider-Man: Brand New Day 5
6 Alice Spider-Man: Brand New Day 5
7 Alice Spider-Man: Brand New Day 5
8 Alice The Super Mario Galaxy Movie 5
9 Alice Spider-Man: Brand New Day 5
10 Alice Spider-Man: Brand New Day 5
11 Alice Spider-Man: Brand New Day 5
12 Alice The Super Mario Galaxy Movie 5
13 Alice The Super Mario Galaxy Movie 5
14 Alice The Super Mario Galaxy Movie 5
15 Brian Project Hail Mary 5
16 Brian Project Hail Mary 5
17 Brian The Odyssey 5
18 Brian Project Hail Mary 5
19 Brian Project Hail Mary 5
20 Brian Project Hail Mary 5
21 Brian Project Hail Mary 5
22 Brian The Odyssey 5
23 Brian Project Hail Mary 5
24 Brian The Odyssey 5
25 Brian The Odyssey 5
26 Brian The Odyssey 5
27 Brian The Odyssey 5
28 Brian The Odyssey 5
29 Carla Toy Story 5 5
30 Carla Spider-Man: Brand New Day 5
31 Carla Spider-Man: Brand New Day 5
32 Carla Spider-Man: Brand New Day 5
33 Carla Spider-Man: Brand New Day 5
34 Carla Toy Story 5 5
35 Carla Spider-Man: Brand New Day 5
36 Carla Toy Story 5 5
37 Carla Spider-Man: Brand New Day 5
38 Carla Toy Story 5 5
39 Carla Toy Story 5 5
40 Carla Toy Story 5 5
41 Carla Toy Story 5 5
42 Carla Spider-Man: Brand New Day 5
43 David Project Hail Mary 5
44 David The Super Mario Galaxy Movie 5
45 David The Odyssey 5
46 David Project Hail Mary 5
47 David Project Hail Mary 5
48 David The Super Mario Galaxy Movie 5
49 David The Super Mario Galaxy Movie 5
50 David The Odyssey 5
51 David Project Hail Mary 5
52 David The Odyssey 5
53 David The Odyssey 5
54 David The Odyssey 5
55 David The Super Mario Galaxy Movie 5
56 David The Odyssey 5
57 David The Super Mario Galaxy Movie 5
58 David Project Hail Mary 5
59 David Project Hail Mary 5
60 David The Odyssey 5
61 David The Super Mario Galaxy Movie 5
62 David Project Hail Mary 5
63 David The Super Mario Galaxy Movie 5
64 Elena Toy Story 5 5
65 Elena Toy Story 5 5
66 Elena Toy Story 5 5
67 Elena Toy Story 5 5
68 Elena Toy Story 5 5
69 Elena Toy Story 5 5
70 Elena Toy Story 5 5
71 Frank The Super Mario Galaxy Movie 5
72 Frank The Super Mario Galaxy Movie 5
73 Frank Spider-Man: Brand New Day 5
74 Frank Spider-Man: Brand New Day 5
75 Frank The Super Mario Galaxy Movie 5
76 Frank The Super Mario Galaxy Movie 5
77 Frank Spider-Man: Brand New Day 5
78 Frank Spider-Man: Brand New Day 5
79 Frank The Super Mario Galaxy Movie 5
80 Frank The Super Mario Galaxy Movie 5
81 Frank Spider-Man: Brand New Day 5
82 Frank Spider-Man: Brand New Day 5
83 Frank The Super Mario Galaxy Movie 5
84 Frank Spider-Man: Brand New Day 5
85 Grace Project Hail Mary 5
86 Grace The Odyssey 5
87 Grace Project Hail Mary 5
88 Grace The Odyssey 5
89 Grace The Odyssey 5
90 Grace The Odyssey 5
91 Grace Project Hail Mary 5
92 Grace Project Hail Mary 5
93 Grace Project Hail Mary 5
94 Grace The Odyssey 5
95 Grace The Odyssey 5
96 Grace The Odyssey 5
97 Grace Project Hail Mary 5
98 Grace Project Hail Mary 5
# 5. RANK MOVIES BY OVERALL AVERAGE RATINGoverall_movie_ranking <-dbGetQuery( con," WITH movie_averages AS ( SELECT movie, ROUND(AVG(rating), 2) AS average_rating FROM movie_ratings GROUP BY movie ) SELECT movie, average_rating, RANK() OVER ( ORDER BY average_rating DESC ) AS overall_rank FROM movie_averages ORDER BY overall_rank; ")overall_movie_ranking
movie average_rating overall_rank
1 Project Hail Mary 4.29 1
2 The Super Mario Galaxy Movie 4.29 1
3 The Odyssey 4.29 1
4 Spider-Man: Brand New Day 4.29 1
5 Toy Story 5 4.14 5