Subqueries

#1978. Employees Whose Manager Left the Company
#Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, 
but the reports still have their manager_id set to the manager that left. Return the result table ordered by employee_id.
SELECT employee_id FROM Employees WHERE salary < 30000 AND manager_id NOT IN (SELECT employee_id FROM Employees) ORDER BY employee_id;

____________________________________________________________________________________________________________________________________________________________________________________________________
#626. Exchange Seats
#Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.
# Write your MySQL query statement below
SELECT id, CASE WHEN id % 2 = 0 THEN LAG(student) OVER(ORDER BY id) ELSE COALESCE(LEAD(student) OVER(ORDER BY id), student) END AS student FROM Seat;


#1341. Movie Rating
#Write a solution to: Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name. 
#Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

(SELECT u.name AS results
 FROM MovieRating mr
 JOIN Users u ON u.user_id = mr.user_id
 GROUP BY u.name
 ORDER BY COUNT(*) DESC, u.name ASC
 LIMIT 1)

UNION ALL

(SELECT m.title AS results
 FROM MovieRating mr
 JOIN Movies m ON m.movie_id = mr.movie_id
 WHERE mr.created_at BETWEEN '2020-02-01' AND '2020-02-29'
 GROUP BY m.title
 ORDER BY AVG(mr.rating) DESC, m.title ASC
 LIMIT 1);


--------------------------------------------------------------------------------------------------------------------------------
#1321. Restaurant Growth
#Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.
select distinct visited_on, sum(amount) over w as amount, round((sum(amount) over w)/7, 2) as average_amount from customer WINDOW w AS ( order by visited_on
range between interval 6 day PRECEDING and current row) Limit 6, 999

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#602. Friend Requests II: Who Has the Most Friends
#Write a solution to find the people who have the most friends and the most friends number.The test cases are generated so that only one person has the most friends.

with base AS(select requester_id id from RequestAccepted Union ALL select accepter_id id from RequestAccepted)
select id, COUNT(*) as num from base group by 1 order by 2 DESC LIMIT 1;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#585. Investments in 2016
#Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who: have the same tiv_2015 value as one or more other policyholders, and
are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique).

Select Round(Sum(tiv_2016),2) as tiv_2016 from Insurance where tiv_2015 in (Select tiv_2015 from insurance group by tiv_2015 Having count(*)>1)
and (lat,lon) in (Select lat, lon from insurance group by lat,lon having count(*)=1)

________________________________________________________________________________________________________________________________________________________________________________________
#185. Department Top Three Salaries
#Write a solution to find the employees who are high earners in each of the departments.Return the result table in any order.
select Department, Employee, Salary from (select d.name AS Department, e.name AS Employee, e.salary AS Salary,  dense_rank() over (partition by d.name order by e.salary desc) as ranks
from Employee e join Department d ON e.departmentId=d.id)  t  where ranks <=3