Section 06 : Subqueries - part 02 - P 3 , 4

Farahat612 · Farahat612 · commit e6653a4afcdf · 2024-04-07T20:55:18.000+02:00
diff --git a/6. Subqueries/03 1341. Movie Rating.sql b/6. Subqueries/03 1341. Movie Rating.sql
@@ -0,0 +1,16 @@
+(SELECT u.name AS results 
+  FROM MovieRating AS mr 
+  LEFT JOIN  Users AS u 
+  USING (user_id) 
+  GROUP BY user_id 
+  ORDER BY COUNT(movie_id) DESC, u.name ASC LIMIT 1) 
+ 
+ UNION ALL 
+ 
+ (SELECT m.title  
+  FROM MovieRating AS mr 
+  LEFT JOIN Movies AS m 
+  USING (movie_id) 
+  WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29' 
+  GROUP BY movie_id 
+  ORDER BY AVG(rating) DESC, m.title ASC LIMIT 1)
diff --git a/6. Subqueries/04 1321. Restaurant Growth.sql b/6. Subqueries/04 1321. Restaurant Growth.sql
@@ -0,0 +1,11 @@
+SELECT 
+    a.visited_on, 
+    SUM(b.amount) AS amount, 
+    ROUND(SUM(b.amount)/7, 2) AS average_amount
+
+FROM 
+    (SELECT DISTINCT visited_on FROM Customer) a 
+    JOIN Customer b ON DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
+
+GROUP BY a.visited_on
+HAVING a.visited_on >= MIN(b.visited_on) + 6
