Section 06 : Subqueries - part 03 - P 5 , 6

Farahat612 · Farahat612 · commit 0b20bd99984a · 2024-04-07T20:57:18.000+02:00
diff --git a/6. Subqueries/05 602. Friend Requests II Who Has the Most Friends.sql b/6. Subqueries/05 602. Friend Requests II Who Has the Most Friends.sql
@@ -0,0 +1,20 @@
+SELECT requester_id AS id, 
+    ( SELECT COUNT(*) 
+    FROM RequestAccepted 
+    WHERE requester_id = id OR accepter_id = id
+    ) AS num
+    
+FROM RequestAccepted 
+GROUP BY requester_id
+
+UNION
+
+SELECT accepter_id as id,
+    ( SELECT COUNT(*) 
+    FROM RequestAccepted  
+    WHERE requester_id = id OR accepter_id = id
+    ) AS num
+FROM RequestAccepted
+GROUP BY accepter_id
+
+ORDER BY num DESC LIMIT 1
diff --git a/6. Subqueries/06 585. Investments in 2016.sql b/6. Subqueries/06 585. Investments in 2016.sql
@@ -0,0 +1,16 @@
+SELECT ROUND(SUM(insurance.TIV_2016),2) AS TIV_2016
+FROM insurance
+WHERE insurance.TIV_2015 IN
+    (
+      SELECT TIV_2015
+      FROM insurance
+      GROUP BY TIV_2015
+      HAVING COUNT(*) > 1
+    )
+    AND CONCAT(LAT, LON) IN
+    (
+      SELECT CONCAT(LAT, LON)
+      FROM insurance
+      GROUP BY LAT , LON
+      HAVING COUNT(*) = 1
+    )
