Section 02 : Basic Agg : part 01

Farahat612 · Farahat612 · commit 7c4a12c543fe · 2024-04-06T23:49:30.000+02:00
diff --git a/3. Basic Aggregate Functions/01  Code Testcase Test Result Test Result 620. Not Boring Movies.sql b/3. Basic Aggregate Functions/01  Code Testcase Test Result Test Result 620. Not Boring Movies.sql
@@ -0,0 +1,5 @@
+SELECT id, movie, description, rating
+FROM Cinema
+WHERE id % 2 = 1
+AND description != 'boring'
+ORDER BY rating DESC;
diff --git a/3. Basic Aggregate Functions/02 1251. Average Selling Price.sql b/3. Basic Aggregate Functions/02 1251. Average Selling Price.sql
@@ -0,0 +1,4 @@
+SELECT u.product_id, ROUND(SUM(p.price * u.units) / SUM(u.units), 2) AS average_price
+FROM Prices p
+JOIN UnitsSold u ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date
+GROUP BY u.product_id;
diff --git a/3. Basic Aggregate Functions/03 1075. Project Employees I.sql b/3. Basic Aggregate Functions/03 1075. Project Employees I.sql
@@ -0,0 +1,4 @@
+SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years
+FROM Project p
+JOIN Employee e ON p.employee_id = e.employee_id
+GROUP BY p.project_id;
diff --git a/3. Basic Aggregate Functions/04 1633. Percentage of Users Attended a Contest.sql b/3. Basic Aggregate Functions/04 1633. Percentage of Users Attended a Contest.sql
@@ -0,0 +1,6 @@
+SELECT 
+    r.contest_id,
+    ROUND(COUNT(r.user_id) * 100.0 / (SELECT COUNT(*) FROM Users), 2) AS percentage
+FROM Register r
+GROUP BY r.contest_id
+ORDER BY percentage DESC, r.contest_id ASC;
