updating sql queries

rezzcode · web-flow · commit cc7cdca5338e · 2024-03-03T13:03:11.000+03:00
Mysql branch brings updates
diff --git a/MySQL/consecutive-numbers.sql b/MySQL/consecutive-numbers.sql
@@ -1,17 +1,11 @@
+/*
 # Time:  O(n)
 # Space: O(n)
+*/
 
-SELECT DISTINCT(Num) AS ConsecutiveNums
-FROM (
-    SELECT
-    Num,
-    @counter := IF(@prev = Num, @counter + 1, 1) AS how_many_cnt_in_a_row,
-    @prev := Num
-    FROM Logs y, (SELECT @counter:=1, @prev:=NULL) vars
-) sq
-WHERE how_many_cnt_in_a_row >= 3
-
-SELECT DISTINCT l1.Num  as ConsecutiveNums
-FROM Logs l1, Logs l2, Logs l3  
-WHERE l1.Id + 1 = l2.Id AND l2.Id + 1 = l3.Id AND l1.Num = l2.Num AND l2.Num = l3.Num 
+/** this is a ms sql script **/
 
+/* Write your T-SQL query statement below */
+SELECT DISTINCT l1.num AS ConsecutiveNums 
+FROM Logs l1, Logs l2, Logs l3 
+WHERE l1.id = l2.id - 1 AND l2.id = l3.id - 1 AND l1.num = l2.num AND l2.num = l3.num;
diff --git a/MySQL/duplicate-emails.sql b/MySQL/duplicate-emails.sql
@@ -1,5 +1,10 @@
 # Time:  O(n^2)
 # Space: O(n)
 
-SELECT Email FROM Person GROUP BY Email HAVING COUNT(*) > 1
+/** # Write your MySQL query statement below **/
+
+SELECT Email
+FROM Person
+GROUP BY Email
+HAVING COUNT(*) > 1
 
diff --git a/MySQL/employees-earning-more-than-their-managers.sql b/MySQL/employees-earning-more-than-their-managers.sql
@@ -1,13 +1,13 @@
 # Time: O(n^2)
 # Space: O(1)
 
-SELECT e.Name AS Employee  FROM Employee e LEFT JOIN Employee b
-       ON e.ManagerId=b.Id
-       WHERE e.Salary > b.Salary
-
-SELECT Name AS Employee 
-    FROM Employee e 
-    WHERE e.ManagerId IS NOT NULL AND e.Salary > (SELECT Salary 
-                          FROM Employee 
-                          WHERE e.ManagerId = Id)
+SELECT e.Name AS Employee  FROM Employee e LEFT JOIN Employee b 
+        ON e.ManagerId=b.Id WHERE e.Salary > b.Salary
+
+/** # Write your MySQL query statement below **/
+       /** solution two **/
+
+SELECT e.name AS employee
+FROM employee AS e JOIN employee AS e1 ON e.managerid = e1.id
+WHERE e.salary > e1.salary;
 
diff --git a/MySQL/product-price-at-a-given-date.sql b/MySQL/product-price-at-a-given-date.sql
@@ -1,17 +1,32 @@
+/*
 # Time:  O(mlogn), m is the number of unique product id, n is the number of changed dates
 # Space: O(m)
+       */
+
+SELECT 
+    t1.product_id AS product_id, 
+    IF(ISNULL(t2.price), 10, t2.price) AS price 
+FROM 
+    (SELECT DISTINCT product_id 
+     FROM products) AS t1 
+LEFT JOIN 
+    (SELECT 
+         product_id, 
+         new_price AS price 
+     FROM 
+         products 
+     WHERE 
+         (product_id, change_date) IN 
+         (SELECT 
+              product_id, 
+              MAX(change_date) 
+          FROM 
+              products 
+          WHERE 
+              change_date <= '2019-08-16' 
+          GROUP BY 
+              product_id)
+    ) AS t2 
+ON 
+    t1.product_id = t2.product_id;
 
-SELECT t1.product_id                      AS product_id, 
-       IF(Isnull(t2.price), 10, t2.price) AS price 
-FROM   (SELECT DISTINCT product_id 
-        FROM   products) AS t1 
-       left join (SELECT product_id, 
-                         new_price AS price 
-                  FROM   products 
-                  WHERE  ( product_id, change_date ) IN (SELECT product_id, 
-                                                                Max(change_date) 
-                                                         FROM   products 
-                                                         WHERE change_date <= '2019-08-16' 
-                                                         GROUP  BY product_id)) 
-                 AS t2 
-              ON t1.product_id = t2.product_id 
