LeetCode SQL Problem Solving Questions With Solutions LeetCode SQL Solutions 175. Combine Two Tables | Easy | LeetCode Table: PersonTable: address | Column Name | Type | | Addresstd | int | | PersoniId | int | | city | varchar | | state | varchar | AddressId is the primary key colunn for this table. Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people: FirstName, LastName, City, State Solution SELECT p.FirstName, p.LastName, a.City, a.State FROM Person p LEFT JOIN Address a ON p.PersonId = a.PersonId; 176. Second Highest Salary | Easy | LeetCode Write a SQL query to get the second highest salary from the Enployee table.For example, given the above Employee table, the query should return 220 as the second highest salary. If there is no second highest salary, then the query should return null. Solution SELECT Max(Salary) SecondHighestSalary FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee) WITH CTE AS (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 2) SELECT Salary as SecondHighestSalary FROM CTE ORDER BY Salary Asc LIMIT 43 aWITH CTE AS ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DENSERANK FROM Employee ) SELECT Salary SecondHighestSalary FROM CTE WHERE DENSERANK 177. Nth Highest Salary | Medium | LeetCode Write a SQL query to get the nth highest salary from the Employee table. |a [100 | [2 | 20 | 13 | 300 | For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null. | getNthHighestsalary(2) |CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN SET N= N-2; RETURN( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET N uw END 178. Rank Scores | Medium | LeetCode Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks. For example, given the above Scores table, your query should generate the following report (order by highest score):| score | Rank a 4 3.85 3. 3. 3. . Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example Rank. Solution SELECT score, DENSE_RANK() OVER (ORDER By Score DESC) AS “Rank” FROM Scores; 180. Consecutive Numbers Mew Table: Logs | Column Name | Type — | | id | int | | num | varchar | id is the primary key for this table.Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in any order. The query result format is in the following example: Logs table: Result table 1 is the only nunber that appears consecutively for at least three times. Solution SELECT a.Num as ConsecutiveNuns FROM Logs a JOIN Logs b ON acid = b-id#2 AND a.num = b.num JOIN Logs ¢ ON a.id = c.id+2 AND a.num = c.num;181. Employees Earning More Than Their Managers | Easy | eetCode The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. | Id | Name | Salary | Managertd | ].1 | Joe | 7eeea | 3 \ |.2 | Henry | seaea | 4 \ [3 | Sam | 6e000 | NULL \ [4 [max | 9¢000 | NULL | Given the Enployee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager. Solution SELECT E.Name as "Employee" FROM Employee & JOIN Employee MON E.Managerid = M.1d AND E.Salary > M.Salary; 182. Duplicate Emails | Easy | LeetCode Write a SQL query to find all duplicate emails in a table named Person. | td | email | | 2@b.com | la 1.2. | c@d.com | [3 | a@b.com | For example, your query should return the following for the above table: | email | | a@b.com | Note: All emails are in lowercase. Solution SELECT Email FROM Person GROUP BY Email HAVING count(*) > 1.WITH CTE AS( SELECT Email, ROW_NUMBER() OVER(PARTITION BY Email ORDER BY Email) AS RN FROM Person SELECT €mail FROM CTE WHERE RN > 15 183. Customers Who Never Order | Easy | LeetCode Suppose that a website contains two tables, the custoners table and the orders table. Write a SQL query to find all customers who never order anything. Table: Customers . Table: orders. | 1d | customerra | fa 13 | f2 42 |Using the above tables as example, return the following: Solution SELECT Name AS Custoners FROM Customers LEFT JOIN Orders ON Customers.Id = Orders.CustomerId WHERE CustomerId IS NULL; SELECT Name as Customers FROM Customers WHERE Id NOT INC SELECT CustonerId FROM Orders 184. Department Highest Salary | Medium | Lee(Code The Enployee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.| Id | Name | Salary | Departmentid | ].1 [Joe | 7eeea | 4 | [2 | aim | 9ee0@ | 2 | [3 | Henry | seaea | 2 | | 4 | sam | 6ee0e | 2 | [5 [max | 90000 | 4 | Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter). | Department | Employee | Salary | |r | Max | seeee | [ar | 34m | see00 | | sales | Henry | se0ee | Explanation: Max and Jim both have the highest salary in the IT department and Henry has thehighest salary in the Sales department. Solution SELECT Department.Name AS Department, Employee.Name AS Employee, Salary FROM Employee JOIN Department ON Employee.DepartmentId = Department.1d WHERE (DepartmentId, Salary) IN( SELECT Departmentid, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId 3 185. Department Top Three Salaries Hard | LeetCode The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. | 1d | Name” | Salary | Departmentid | Joe | 85000 Henry | 89000 sam | 69000 Janet | 69000 Randy | 85000 will | 70000 Ia 12 13 14 [5 6 \7 | | | | | | | max | 90000 | | | | | | | The vepartnent table holds all departments of the company.Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter). | Department | Employee | Salary sales sales Explanation: In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary. Solution WITH department_ranking AS ( SELECT Name AS Enployee, Salary ,DepartmentId ,DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS rnk FROM EmployeeSELECT d.Name AS Department, r.Enployee, r.Salary FROM department_ranking AS r JOIN Department AS d ON r.DepartmentId = d.1d WHERE r.pnk <= 3 ORDER BY d.Name ASC, r.Salary DESC; 196. Delete Duplicate Emails | Easy | LeetCode Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id. | Id | email | john@exanple.com | i | 2. | bob@exanple.com | 13 | john@example.com | Id is the primary key column for this table. For example, after running your query, the above Person table should have the following rows: 1 | john@example.com | 2 | bob@example.com | Note: Your output is the whole Person table after executing your sql. Use deletestatement. Solution DELETE p2 FROM Person pt JOIN Person p2 ON pl.tmail = p2.€mail AND pi.id < p2.id 197. Rising Temperature | Easy | LeetCode Table: weather | column Name | Type | +e | id Jant | | recordpate date | | temperature | int — | id is the primary key for this table. This table contains information about the tenperature in a certain day. Write an SQL query to find all dates’ id with higher temperature compared to its previous dates (yesterday). Return the result table in any order. The query result format is in the following example:Weather | dd | recordbate | Temperature | [1 | 2015-01-01 | 10 | | 2. | 2015-01-02 | 25 I | 3. | 2015-e1-03 | 20 I | 4 | 2015-01-04 | 30 | table: In 2015-01-02, temperature was higher than the previous day (10 -> 25). In 2015-01-04, temperature was higher than the previous day (20 -> 30). Solution SELECT t.1d FROM Weather AS t, Weather AS y WHERE DATEDIFF(t.RecordDate, y.RecordDate) = 1 AND t.Tenperature > y. Temperature; SELECT t.1d FROM Weather t JOIN Weather y ON DATEDIFF(t.recordbate, y.recordDate) = 1 AND tetemperature > y.temperatures262. Trips and Users | Hard | LeetCode Table: Trips | Column Name | Type | | ad | ant \ | Client_td | int | | river_td | int \ | city_td | int | | status | enum | | Request_at | date | Id is the primary key for this table. The table holds all taxi trips. Each trip has a unique Id, while Client_Id and or Status is an ENUM type of (‘completed’, ‘cancelled_by driver’, ‘cancelled by_clie — Table: Users | Colunn Nare | Type | + | Userstd | int \ | Banned | enum | | Role | enum | Users_Id is the primary key for this table. ‘The table holds all users. Each user has a unique Users_Id, and Role is an ENUM t Status is an ENUM type of (“Yes’, No’). — Write a SQL query to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03".The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day. Return the result table in any order. Round Cancellation Rate to two decimal points. The query result format is in the following example: rer Trips table | Id | Client_td | Driver_td | City Id | status | Request_at | pada | 10 [a | completed | 2013-10-01 | 12 12 Jaa [a | cancelled_by_driver | 2013-10-01 | 13 13 | a2 le | completed | 2013-10-01 | lala [33 lé | cancelled_by_client | 2013-10-01 | [sya | 16 Ja | completed | 2013-10-02 | 16 12 ja Is | completed | 2013-10-02 | 17 13 | a2 le | completed | 2013-10-02 | |a |2 | 12 [2 | completed | 2013-10-03 | 1913 | 10 | a2 | completed | 2013-10-03 | 19] 4 [33 [2 | cancelled_by_driver | 2013-10-03 | Users table | Users_td | Banned | Role | fa [No | client | 12 | yes | client | 13 | No | client | l4 | No | client | | 10 [No | driver | Jaa [No | driver | | a2 [No | driver | [3 [No | driver | Result table:| bay | Cancellation Rate | | 2013-10-21 | 0.33 | | 2013-10-02 | 0.08 \ | 2013-10-23 | 0.50 | on 2013-10-21: - There were 4 requests in total, 2 of which were canceled. - However, the request with Id=2 was made by a banned client (User_Id=2), so it i - Hence there are 3 unbanned requests in total, 1 of which was canceled. ~ The Cancellation Rate is (1 / 3) = 0.33 on 2013-10-02: - There were 3 requests in total, @ of which were canceled. - The request with Id=6 was made by a banned client, so it is ignored. - Hence there are 2 unbanned requests in total, @ of which were canceled. - The Cancellation Rate is (@ / 2) = 0.00 On 2013-10-03: - There were 3 requests in total, 1 of which was canceled. - The request with Id=8 was made by a banned client, so it is ignored. - Hence there are 2 unbanned request in total, 1 of which were canceled. ~ The Cancellation Rate is (1 / 2) = 0.50 Solution SELECT Request_at AS Day, ROUND(SUM(IF(Status<>"completed", 1, @))/COUNT(Status),2) AS "Cancellation Rate” FROM Trips WHERE Request_at BETWEEN "2013-10-01" AND "2013-10-03" AND Client_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes') AND Driver_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes') GROUP BY Request_at;511. Game Play Analysis I | Easy | {@ LeetCode Table: activity | Column Name | Type | | player id | int | | deviceid | int | J event_date | date | | games_played | int I (player_id, event_date) is the primary key of this table. This table shows the activity of players of some game. Each row is a record of a player who logged in and played a number of games (poss —,, Write an SQL query that reports the first login date for each player. The query result format is in the following example: ter Activity table: | player_id | device_id | event_date | games_played | ja | 2 | 2016-03-01 | 5 \ Ia l2 | 2016-05-02 | 6 \ |2 13 | 2017-06-25 | 2 \ 13 fa | 2016-03-02 | @ | 13 [4 | 2018-07-03 | 5 | Result table: | player_id | first_login | ia | 2016-03-01 |2 | 2017-06-25 | 3 | 2016-03-02 | Solution SELECT player_id, MIN(event_date) as first_login FROM Activity GROUP BY player_id 512. Game Play Analysis Il | Easy | @ LeetCode Table: activity | Column Name | Type | | playerid | int | | device id | int | J event_date | date | | ganes_played | int | e (player_id, event_date) is the primary key of this table. This table shows the activity of players of some game. Each row is a record of a player who logged in and played a number of games (poss ———— Write a SQL query that reports the device that is first logged in for each player. The query result format is in the following example:Activity table: | player_id | device_id | event_date | games_played | la 12 | 2016-03-01 | 5 | [a |2 | 2016-05-02 | 6 \ [2 13 | 2017-06-25 | 1 | 13 Ja | 2016-03-02 | 0 \ 13 14 | 2018-07-03 | 5 | device_id | Solution SELECT DISTINCT player_ FROM Activity d, device id WHERE (player_id, event_date) in ( SELECT player_id, min(event_date) FROM Activity GROUP BY player_id) SELECT a.player_id, b.device_id FROM (SELECT player_id, MIN(event_date) AS event_date FROM Activity GROUP BY player_id) a 3OIN Activity bON a.player_id = b.player_id AND a.event_date = b.event_date; SELECT player_id, device_id FROM (SELECT player_id, device_id, event_date, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS r FROM Activity) lookup WHERE r = 4; 534. Game Play Analysis III | Medium | (@ LeetCode Table: activity | column Nae | Type | | playerid | int | | deviceid | int | | event_date | date | | games_played | int | (player_id, event_date) is the primary key of this table This table shows the activity of players of some game. Each row is a record of a player who logged in and played a number of games (poss — Write an SQL query that reports for each player and date, how many games played so far by the player. That is, the total number of games played by the player until that date. Check the example for clarity. The query result format is in the following example: Activity table:| player_id | device_id | event_date | games_played | Ja [2 | 2016-03-01 | 5 \ fa |2 | 2016-05-02 | 6 \ [a 13 | 2017-06-25 | 1 \ 13 [a | 2016-03-02 | @ \ 13 [4 | 2018-07-03 | 5 | | player_id | event_date | games_played_so_far | | 2016-03-01 | 5 | | 2o16-05-02 | 11 | | 2017-06-25 | 12 | | 2016-03-02 | @ | | 2e18-07-e3 | 5 | la la fa 13 13 For the player with id 1, 5 + 6 = 11 games played by 2016-05-02, and 5 +6 +1 = For the player with id 3, @ + 5 = 5 games played by 2018-07-03. Note that for each player we only care about the days when the player logged in. — Solution SELECT t1.player_id, tl.event_date, SUM(t2.games_played) as games_played_so_far FROM Activity ti JOIN Activity #2 ON ti.player_id = t2.player_id WHERE t1.event_date >= t2.event_date GROUP BY t1.player_id, t1.event_date; SELECT player_id, event_date,‘SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) AS games_play FROM Activity; 550. Game Play Analysis IV | Medium | (@ LeetCode Table: Activity | Column Name | Type | | playerid | int | | deviceid | int | J event_date | date | | games_played | int I (player_id, event_date) is the primary key of this table. This table shows the activity of players of some game. Each row is a record of a player who logged in and played a number of games (poss XX Write an SQL query that reports the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players. The query result format is in the following example: Activity table: | player_id | device_id | event_date | games_played | | 2016-23-01 fa 12 Is fa l2 | 2016-03-02 | 6 \ 12 13 | 2017-06-25 | 11 | 2016-03-02 | 0 \ 4 | 2018-07-03 | 5 | Only the player with id 1 logged back in after the first day he had logged in so ——T—TT. Solution SELECT ROUND(sum(CASE WHEN t1.event_date = t2.first_event+1 THEN 1 ELSE @ END)/CO FROM Activity t1 JOIN (SELECT player_id, MIN(event_date) AS first_event FROM Activity GROUP BY player_id) t2 ON t1.player_id = t2.player_id; SELECT ROUND(COUNT (DISTINCT b.player_id)/COUNT(DISTINCT a.player_id),2) AS fracti FROM (SELECT player_id, MIN(event_date) AS event_date FROM Activity GROUP BY player_id) a LEFT JOIN Activity b ON a.player_id = b.player_id AND a.event_date+1 = b.event_date; — 569. Median Employee Salary | Hard | ( LeetCodeThe Employee table holds all employees. The employee table has three columns: Employee Id, Company Name, and Salary. [td | Company — | Salary | In oda | 2341 | 2 1A [3a1 | 131A fas I4oda | as314 | Is 1A last | Is 1a [513 | 7 18 fas | Is 18 3 | Is 18 | aasa | jw 18 [3345 | Iu [8 Jaz | 2 18 | 2340 | Ii | 2345 | Iu dc | 2645 | fas fc | 2645 | le | c | 2652 | lz dc les | Write a SQL query to find the median salary of each company. Bonus points if you can solve it without using any built-in SQL functions. [td | Company | Salary | |9 | | | | | | jz | 8 | 234 | | | Il laa | | |Solution SELECT t1.Id AS Id, t1.Company, ti.Salary FROM Employee AS t1 JOIN Employee AS t2 ON t1.Company = t2.Company GROUP BY t1.Id HAVING abs(sum(CASE WHEN t2.Salaryct1.Salary THEN 1 WHEN t2.Salary>t1.Salary THEN -1 WHEN t2.Salary=ti.Salary AND t2.Idct1.Id THEN 1 WHEN t2.Salary=ti.Salary AND t2.Id>ti.Id THEN -1 ELSE @ END)) <= 1 ORDER BY t1.Company, t1.Salary, ti.Id 570. Managers with at Least 5 Direct Reports | Medium | @ LeetCode The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. [td [Name [Department |Managertd | |2e1 — |3ohn la [null \ [102 [pan Ia [101 \ |1e3 | James la {102 | |104 Any la [101 \ [105 [Anne la [101 \ |106 [Ron IB [181 | Given the enployee table, write a SQL query that finds out managers with at least 5 direct report. For the above table, your SQL query should return:Note: No one would report to himself. Solution SELECT Name FROM Employee WHERE id IN (SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT(DISTINCT Id) >= 5) @ LeetCode 571. Find Median Given Frequency of Numbers The Nunbers table keeps the value of number and its frequency.In this table, the numbers are 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3, so the median is (0 + 0) / 2=0. | median | = | 0.0000 | Write a query to find the median of all numbers and name the result as median. Solution SELECT avg(t3.Number) as median FROM Nunbers as t3 JOIN (SELECT t1.Nunber, abs(SUM(CASE WHEN t1.Number>t2.Number THEN t2.Frequency ELSE @ END) - SUM(CASE WHEN t1.Nunberct2.Nunber THEN t2.Frequency ELSE @ END)) AS FROM numbers AS ti, numbers AS t2 GROUP BY t1.Nunber) AS ta ON t3.Number = t4.Number WHERE t3.Frequency>=t4.count_diff 574. Winning Candidate | Medium | (@ LeetCode Table: Candidate | id | Name | laTable: vote | id | Candidatetd | wn wan 1 2 3 4 5 id is the auto-increment primary key, Candidaterd is the id appeared in Candidate table. Write a sql to find the name of the winning candidate, the above example will return the winner B. Notes: You may assume there is no tie, in other words there will be at most one winning candidate. SolutionSELECT Name FROM Candidate WHERE id = (SELECT Candidaterd FROM Vote GROUP BY Candidateld ORDER BY COUNT(1) desc LIMIT 1) 577. Employee Bonus | Easy | ( LeetCode Select all employee's name and bonus whose bonus is < 1000. Table:Employee | empté | name | supervisor] salary | | 2 [ohn | 3 | 1000 | | 2 [oan | 3 | 2000 | | 3 | Brad | null = | 4000 | | 4 | Thomas | 3 | 4000 | empid is the primary key column for this table.Table: Bonus | empté | bonus | empId is the primary key column for this table. Example ouput: Solution SELECT name, bonus FROM Employee LEFT JOIN Bonus ON Employee.empid = Bonus.enpId WHERE bonus<1@@@ OR bonus IS NULL; 578. Get Highest Answer Rate Question | Medium | (@ LeetCode Get the highest answer rate question from a table surveylog with these columns: id, action, questionid, answerid, qnum, timestamp.uid means user id; action has these kind of values: “show’, “answer’, “skip”, answerid is not null when action column is “answer’; while is null for “show” and “skip’; qnum is the numeral order of the question in current session. Write a sql query to identify the question which has the highest answer rate. Example: Input: | uid | action | question_id | answer_id | qnum | timestamp | 15 | show | 285 | nul [a | 123 | 15 | answer | 285 | 124124 fa | 124 | 15 | show | 369 | nua 12 | 125 | 15 | skip | 369 | nuit [2 | 126 | Explanation: question 285 has answer rate 1/1, while question 369 has 0/1 answer rate, so output 285. Note: The highest answer rate meaning is: answer number's ratio in show number in the same question. SolutSELECT question_id AS survey_log FRON (SELECT question_i¢, SUN(IF(action="show', 1, @)) AS num_show, SUM(IF(action='answer', 1, @)) AS num_answer FROM survey log GROUP BY question_id) AS t ORDER BY (num_answer/num_show) DESC LIMIT 1; SELECT question_: FROM (SELECT question_id, sum(CASE WHEN actior sum(CASE WHEN actior FROM survey_log GROUP BY question_id) AS t ORDER BY answer_count/show_count DESC LIMIT 13 AS survey_log show’ THEN 1 ELSE @ END) AS show_count, answer’ THEN 1 ELSE @ END) AS answer_count 579. Find Cumulative Salary of an Employee | Hard | (® LeetCode The Enployee table holds the salary information in a year. Write a SQL to get the cumulative sum of an employee's salary over a period of 3 months but exclude the most recent month. The result should be displayed by ‘Id’ ascending, and then by ‘Month’ descending. Example Input salary | “| 20 | 2 | 38 | se 40 | 1 1 2 2 22 13 | 40 | 1313 |e | lr 14 | 60 | 13 14 | 78 | Output | Id | Month | salary | Explanation Employee ‘1’ has 3 salary records for the following 3 months except the most recent month ‘4’: salary 40 for month ‘3; 30 for month ‘2’ and 20 for month ‘1’ So the cumulative sum of salary of this employee over 3 months is 90(40+30+20), 50(30+20) and 20 respectively. | Id | Month | Salary | Employee '2’ only has one salary record (month ‘t’) except its most recent month ‘2! | td | Month | salary |Employ ‘3’ has two salary records except its most recent pay month ‘4’: month ‘3° with 60 and month ‘2’ with 40. So the cumulative salary is as following. | Id | Month | Salary | Solution SELECT aid, a.month, SUM(b. salary) Salary FROM Employee a JOIN Employee b ON acid = beid AND a.month - b.month >= @ AND a.month - b.month <3 GROUP BY acid, a.month HAVING (a.id, a.month) NOT IN (SELECT id, MAX(month) FROM Employee GROUP BY id) ORDER BY a.id, a.month DESC 580. Count Student Number in Departments | Medium | @ LeetCode A university uses 2 data tables, student and department , to store data about its students and the departments associated with each major. Write a query to print the respective department name and number of students majoring in each department for all departments in the department table (even oneswith no current students). Sort your results by descending number of students; if two or more departments have the same number of students, then sort those departments alphabetically by department name. The student is described as follow: | Column Name | Type | | “I | | student_id | Integer | | student_name | String | | gender | Character | | dept_id | integer | where studentid is the student's ID number, studentname is the student's name, gender is their gender, and dept_id is the department ID associated with their declared major. And the department table is described as below: | column Nane | Type | I- | deptid | Integer | dept_name | String where deptid is the department's ID number and deptname is the department name. Here is an example input: student table: dept_iddepartnent table: dept_nane | | | | | Engineering | | | Science | Law | The Output should be: | dept_name | student_nunber | | Engineering | 2 I | | Science 14 I | | Law |e Solution SELECT dept_name, SUM(CASE WHEN student_id TS NULL THEN @ ELSE 1 END) AS student_nunber FROM department LEFT 30IN student ON department .dept_id = student.dept_id GROUP BY department.dept_id ORDER BY student_number DESC, dept_name 584. Find Customer Referee | Easy | (@ LeetCode Given a table customer holding customers information and the referee.id | nane | | a] wana | NULL | | 2] dane | NULL | | 3 | Alex | 21 | 4 | Bill | NULL | | 5 | Zack | a] 16] mark | 21 Write a query to return the list of customers NOT referred by the person with id ‘2: For the sample data above, the result is: Solution SELECT name FROM customer WHERE referee_id != '2' OR referee_id IS NULL} 585. Investments in 2016 | Medium | (@ LeetCode aWrite a query to print the sum of all total investment values in 2016 (TIV_2016), to a scale of 2 decimal places, for all policy holders who meet the following criteria: 1. Have the same TIV_2015 value as one or more other policyholders. 2. Are not located in the same city as any other policyholder (i.e.: the (latitude, longitude) attribute pairs must be unique). Input Format: The insurance table is described as follows: | Column Name | Type | =| | | Px | INTEGER(A2) | | TIv_2e15 | NUMERIC(15,2) | | THv_2016 | NUNERIC(5,2) | | Lar | NUMERIC(S,2) | | Lon | NUMERTC(S,2) | where PID is the policyholder’s policy 1D, TIV2015 is the total investment value in 2015, TIV2016 is the total investment value in 2016, LAT is the latitude of the policy holder's city, and LON is the longitude of the policy holder's city, Sample Input tea | Pro | TIv_2e15 | TIV_2016 | LAT | LON | }a | 20 Is [10 | 10 | 12 | 20 | 20 | 20 | 2 | 13 | 20 | 32 | 20 | 2 | l4 | 1 | 40 |4e | 40 | Sample Output | TIv_2016 |Explanation The first record in the table, like the last record, meets both of the two criter The TIV_2015 value '10' is as the same as the third and forth record, and its loc The second record does not meet any of the two criteria. Its TIV_2015 is not like And its location is the same with the third record, which makes the third record So, the result is the sun of TIV_2@16 of the first and last record, which is 45. Solution SELECT SUM(TIV_2016) AS TIV_ze16 FROM insurance WHERE CONCAT(LAT, ‘sy LON) IN (SELECT CONCAT(LAT, *,', LON) FROM insurance GROUP BY LAT, LON HAVING COUNT(1) = 1) AND TIV_2015 in (SELECT T1v_z015 FROM insurance GRouP BY TIV_ze15 HAVING COUNT(1)>1) 586. Customer Placing the Largest Number of Orders @ LeetCode Query the customer_number from the orders table for the customer who has placed the largest number of orders. ——TTT3 Easy | .It is guaranteed that exactly one customer will have placed more orders than any other customer. The orders table is defined as follows: | order_number (Pk) | custoner_nunber order_date required date shipped_date status comment Sample Input Type int int date date date char(15) char(20) | order_nunber | customer_nunber Sample Output | custoner_nunber I- 13 | Explanation order_date 2017-04-08 2017-04-15 2017-04-16 2017-04-18 | required_date | 2017-04-13 2017-04-20 2017-04-25 2017-04-28 shipped_date 2017-04-12 | Cl 2017-04-18 | C1 2017-4-28 | Cl 2017-04-25 | Cl —‘The customer with number '3' has two orders, which is greater than either custome So the result is customer_number 3". Solution t SELECT customer_number FROM orders GROUP BY customer_nunber ORDER BY COUNT(1) DESC LIMIT 1 595. Big Countries Easy | LeetCode There is a table world| name | continent | area | population | gdp | | Afghanistan | Asia | 652230 | 255e0100 ©—|_20343000 | | Albania | Europe | 28748 | 2831741 | 12960000 | | Algeria | Africa | 2381741 | 37100000 «=| 188681000 =| | Andorra | Europe | 468 | 78135 | 3712e0e | | Angola | Africa | 1246708 | 20609284 | 100990000 | A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million. Write a SQL solution to output big countries’ name, population and area. For example, according to the above table, we should output: | nane | population | area | | Afghanistan | 2550100 | 652230 | | Algeria | 37100000 | 2381742 | Solution SELECT name, population, area FROM World WHERE area >= 3000002 OR population > 25000000; 596. Classes More Than 5 Students | Easy | LeetCodeThere is a table courses with columns: student and class Please list out all classes which have more than or equal to 5 students. For example, the table: | student | class math English Math Biology amon ee Computer math math I | I | Math I | I | | | Math Solution SELECT class FROM courses GROUP BY class HAVING count (DISTINCT Student)>=5597. Friend Requests LeetCode : Overall Acceptance Rate | Easy | In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well. Now given two tables as below: Table: friend_request | sender_id | send_to_id |request_date| Ja 12 | 2016_96-01 | ja 13 | 2016_06-01 | Ja [4 | 2016_96-01 | |2 13 | 2016_06-02 | 13 [4 | 2016-06-09 | Table: request_accepted | requester_id | accepter_id |accept_date | | 2016_96-03 2016-06-08 2016-26-09 1 1 2 3 3 The rows with ids 2 and 3 are not included because we need at least three consecuSELECT DISTINCT s1.* FROM Stadium s1 JOIN Stadium s2 JOIN Stadium s3 ON (s1.id = s2.id-1 AND st.id = s3.id-2) OR, (slid = s2.id+1 AND s1.id = s3.id-1) oR (S1.id = s2.idtd AND st.id = s3.id2) WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people>=100 ORDER BY visit_date In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well. Table request_accepted holds the data of friend acceptance, while requesterid and accepterid both are the id of a person. | requester_id | accepter_id | accept_date| |n-= = | 2016_26-03 | | 2016-06-08 | | 2016-06-08 | | 2016-06-09 | Write a query to find the the people who has most friends and the most friends. number. For the sample data above, the result is: | id | num |Note: It is guaranteed there is only 1 people having the most friends. The friend request could only been accepted once, which mean there is no multiple records with the same requesterid and accepterid value. Explanation: The person with id ‘3’ is a friend of people ‘1, '2' and ‘4; so he has 3 friends in total, which is the most number than any others. Follow-up: In the real world, multiple people could have the same most number of friends, can you find all these people in this case? SELECT t.id, sum(t.num) AS num FROM ( (SELECT requester_id AS id, COUNT(1) AS num FROM request_accepted GROUP BY requester_id) union all (SELECT accepter_id AS id, COUNT(1) AS num FROM request_accepted GROUP BY accepter_id)) AS t GROUP BY t.id ORDER BY num DESC LIMIT 23 603. Consecutive Available Seats | Easy | (@ LeetCode Several friends at a cinema ticket office would like to reserve consecutive available seats. Can you help to query all the consecutive available seats order by the seatid using the following cinema table?Your query should return the following result for the sample case above. | seat_id | I- “I 13 | 14 | Is | Note: The seat_id is an auto increment int, and free is bool ('1’ means free, and ‘0’ means occupied.). Consecutive available seats are more than 2(inclusive) seats consecutively available. Solution SELECT DISTINCT t1.seat_id FROM cinema AS t1 JOIN cinena AS t2 ON abs(t1.seat_id-t2.seat_i WHERE t1.frees'1' AND t2.fre ORDER BY t1.seat_id “ 607.Sales Person | Easy | ( LeetCode Description Given three tables: salesperson, company, orders . Output all the names in the table salesperson, who didn’t have sales to company ‘RED! Example InputTable: salesperson | sales_id | name | salary | conmission_rate | hire_date | loa | John | 190000 | 6 | 4/1/2006 | | 2 | amy | 120000 | = 5 | s/2010 | 13 | Mark | 65000 | 12 | 12/25/2008] |o4 | Pan | 25000 | 25 | 1/1/2005 | los | Alex | 50000 | 10 | 2/3/2007 | The table salesperson holds the salesperson information. Every salesperson has a sales_id and a name. Table: company | comid | name | city | | 2 | Re | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | The table company holds the company information. Every company has a com_id and a name. Table: orders | order_id | date | com_id | sales_id | amount | fa laa | 3 | 4 | 188880 | |2 lara | 4 | 5 | S088 | 13 laa} 1 | 1 | see8e | l4 42a} 1 | & | 25000 |The table orders holds the sales record information, salesperson and customer company are represented by salesid and comid. output Explanation According to order '3' and ‘4’ in table orders, it is easy to tell only salesperson John’ and ‘Alex’ have sales to company ‘RED; so we need to output all the other names in table salesperson. Solution SELECT name FROM salesperson WHERE name NOT IN (SELECT DISTINCT salesperson.nane FROM salesperson, orders, company WHERE company.name = "RED" AND salesperson.sales_id = orders.sales_id AND orders.com_id = company.com_id) 608. Tree Node | Medium | (@ LeetCode Given a table tree, id is identifier of the tree node and p_id is its parent node's id.Each node in the tree can be one of three types: Leaf: if the node is a leaf node. Root: if the node is the root of the tree. Inner: If the node is neither a leaf node nor a root node. Write a query to print the node id and the type of the node. Sort your output by the node id. The result for the above sample is: Explanation Node ‘1’ is root node, because its parent node is NULL and it has child node ‘2’ and '3! Node ‘2’ is inner node, because it has parent node ‘’ and child node ‘4’ and ‘5: Node 3; ‘4’ and ‘5’ is Leaf node, because they have parent node and they don't have child node. And here is the image of the sample tree as below:Note If there is only one node on the tree, you only need to output its root attributes. Solution SELECT tl.id, CASE WHEN TSNULL(t2.p_id) THEN ‘Root’ WHEN ISNULL(MAX(t2.id)) THEN ‘Leaf” ELSE ‘Inner’ END AS Type FROM tree AS t1 LEFT JOIN tree AS t2 ON th.id = t2.pid GROUP BY t1.id, t1.p_id .610. Triangle Judgement | Easy | ( LeetCode A pupil Tim gets homework to identify whether three line segments could possibly form a triangle. However, this assignment is very heavy because there are hundreds of records to calculate. Could you help Tim by writing a query to judge whether these three sides can form a triangle, assuming table triangle holds the length of the three sides x, y and z. Ix ly Iz] [evse|es |e | 13 | 15 | 3e | | 10 | 20 | a5 | For the sample data above, your query should return the follow result: |_z | triangle | I- “I | 13 | 15 | 30 | No \ | 1@ | 20 | 15 | Yes | Solution LT SELECT x, y, 2, case WHEN x+y>z AND y+z>x AND x+z9y THEN Yes" ELSE 'No" END AS triangle FROM triangle 612. Shortest Distance in a Plane | Medium | (@ LeetCodeTable point_2d holds the coordinates (x,y) of some unique points (more than two) in a plane. Write a query to find the shortest distance between these points rounded to 2 decimals. The shortest distance is 1.00 from point (-1,-1) to (-1,2). So the output should be: | shortest | Note: The longest distance among all the points are less than 10000. Solution rr) SELECT ROUND(MIN(SQRT((t2.x-t2.x)*(tL.x-t2.x) + (tLy-t2.y)*(ELy-t2.y)))s 2) as FROM point_2d AS t1, point_2d as t2 WHERE tl.x!=t2.x OR tL.yl=t2.y 613. Shortest Distance in a Line | Easy | (@ LeetCodeTable point holds the x coordinate of some points on x-axis in a plane, which are all integers. Write a query to find the shortest distance between two points in these points. The shortest distance is’ obviously, which is from point '-1' to ‘0: So the output is as below: | shortest | I- “I fa | Note: Every point is unique, which means there is no duplicates in table point. Follow-up: What if all these points have an id and are arranged from the left most to the right most of x axis? Solution SELECT tl.x-t2.x AS shortest FROM point AS ti JOIN point AS t2 WHERE t1.xot2.x ORDER BY (t1.x-t2.x) ASC LIMIT 1. 614. Second Degree Follower | Medium | (@j LeetCodeIn facebook, there is a follow table with two columns: followee, follower. Please write a sql query to get the amount of each follower's follower if he/she has one. For example: | followee | follower | | follower | num Explanation: Both B and D exist in the follower list, when as a followee, B's follower is Cand D, and D's follower is E. A does not exist in follower list. Note: Followee would not follow himself/herself in all cases. Please display the result in follower's alphabet order. SolutionSELECT f1.follower, COUNT(DISTINCT £2.follower) AS nun FROM follow AS f1 JOIN follow AS #2 ON #1.follower = £2.followee GROUP BY #1.follower; 615. Average Salary: Departments VS Company | Hard | @ LeetCode Given two tables as below, write @ query to display the comparison result {higher/lower/same) of the average salary of employees in a department to the company’s average salary. Table: salary employee_id | amount | pay_date | | I----1 | Jaqa | 9200 | 2017-03-31 | 12 |2 | 6ee@ | 2017-03-31 | 13 13 | 1@000 | 2017-03-31 | l4 1a | 7ee0 | 2017-02-28 | [5 ]2 | 6200 | 2017-02-28 | le 13 | sega | 2017-02-28 | The employeeid column refers to the employeeid in the following table employee. | employee_id | department_id |So for the sample data above, the result is: | pay_month | | “I | | 2017-03 | 1 | higher | | 2017-03 | 2 | lower \ | 2017-02 | 1 | same | | 2017-02 | 2 | same | Explanation In March, the company’s average salary is (9000+6000+10000)/3 = 8333.33... The average salary for department ‘I’ is 9000, which is the salary of ‘employeeid ‘1’ since there is only one employee in this department. So the comparison result is ‘higher’ since 9000 > 8333.33 obviously. The average salary of department '2' is (6000 + 10000)/2 = 8000, which is the average of employeeid '2' and ‘3: So the comparison result is lower’ since 8000 < 8333.33. With he same formula for the average salary comparison in February, the result is ‘same’ since both the department ’ and ‘2’ have the same average salary with the company, which is 7000. Solution SELECT t1.pay_month, t1.department_id, (CASE WHEN t1.amount = t2.amount THEN "same" WHEN t1.amount > t2.amount THEN "higher" WHEN t1.amount < t2.amount THEN ‘lower’ END) AS conparison FROM (SELECT leFt(pay_date, 7) AS pay_month, department_id, avg(amount) AS amount FROM salary JOIN enployee ON salary.employee_id = enployee.enployee_id GROUP BY pay_month, department_id ORDER BY pay _month DESC, department_id) AS ti JOIN (SELECT left(pay_date, 7) AS pay_month, avg(amount) AS amount FROM salary JOIN employeeON salary.employee_id = employee. employee_id GROUP BY pay_month) AS t2 ON 1. pay_month *2.pay_month 618. Students Report By Geography | Hard | (@ LeetCode AUS graduate schoo! has students from Asia, Europe and America. The students’ location information are stored in table student as below. | name | continent | | Jack | America | | Pascal | Europe | | xi | Asia | | | Jane | America Pivot the continent column in this table so that each name is sorted alphabetically and displayed underneath its corresponding continent. The output headers should be America, Asia and Europe respectively. It is guaranteed that the student number from America is no less than either Asia or Europe. For the sample input, the output ist | America | Asia | Europe | 1 | gack | Xi | Pascal | | dane | | | Follow-up: If it is unknown which continent has the most students, can you write a query to generate the student report? SolutionSELECT tl.name AS America, t2. FROM, (SELECT (@ent1 : FROM student CROSS JOIN (SELECT @cnt1 @enta + WHERE continent="America’ ORDER BY name) AS ti LEFT JOIN (SELECT (@cnt2 FROM student r= @ent2 + CROSS JOIN (SELECT @cnt2 WHERE continent Asia’ ORDER BY name) AS t2 ON th.id = t2.4d LEFT JOIN (SELECT (@ent3 FROM student CROSS JOIN (SELECT @ent3 WHERE continen r= @ent3 + Europe’ ORDER BY name) AS t3 ON ti.id = t3.id name AS Asia, t3.name AS Europe 1) AS id, name = @) AS dummy 1) AS id, name = @) AS dummy 1) AS id, name @) AS dummy 619. Biggest Single Number | Easy | (@ LeetCode Table number contains many numbers in column num including duplicated ones. Can you write a SQL query to find the biggest number, which only appears once. 8 8 3 3 a 4For the sample data above, your query should return the following result: Note: If there is no such number, just output null. Solution SELECT TFNULL(( SELECT nun FROM number GROUP BY nun HAVING count(1) = 1 ORDER BY num DESC LIMIT @, 2), NULL) AS num 620. Not Boring Movies | Easy | LeetCode X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the movies’ ratings and descriptions. Please write a SQL query to output movies with an odd numbered ID and a description that is not ‘boring: Order the result by rating. For example, table cinena:| id | movie | description | rating | [oa | war | great 30 | 8.9 | 12 | science | fiction | 8.5 | | 3 [irish | boring = | 6.2 | 14 | Ice song | Fantacy | 8.6 | | 5 | House card] Interesting] 9.1 | | id | movie | description | rating | | 5 | House card] Interesting] 9.1 | [oa dwar | great 30 | 8.9 | Solution SELECT * FROM Cinema WHERE description <> ‘boring’ AND 1D % 2 = 1 ORDER BY rating DESC; 626. Exchange Seats Medium | LeetCode Mary is a teacher in a middle school and she has a table seat storing students’ names and their corresponding seat ids. The column id is continuous increment.Mary wants to change seats for the adjacent students. Can you write a SQL query to output the result for Mary? | id | student | + | 4 | Abbot | | 2 | doris | | 3 | Emerson | | 4 | Green | | 5 | eames | | id | student | | a | boris | | 2 | Abbot | 13 | Green | | 4 | €merson | | | deames | Note: If the number of students is odd, there is no need to change the last one’s seat. Solution SELECT IF (id¢(SELECT MAX(id) FROM seat) ,IF(id%2-0,id-1, id+1),IF(id¥2-0, id-2, id)) AS 4FROM seat ORDER BY id; 627. Swap Salary | LeetCode Table: salary | Column Name | Type | | ad | ant \ | name | varchar | | sex | enum | | salary | int | id is the primary key for this table. The sex column is ENUM value of type ('m', ‘f') The table contains information about an employee. Write an SQL query to swap all 'f" and ‘n’ values (.e., change all “#" values to ‘mn’ and vice versa) with a single update statement and no intermediate temp table(s). Note that you must write a single update statement, DO NOT write any select statement for this problem. The query result format is in the following example: Salary table: | id | name | sex | salary | fa [a |[m | 25¢0 |12 18 [Ff | 150 | 13 1c |m | s5see | l4 [0 |F | 500 | | id | name | sex | salary | la [a 1 | 25e | 12 18 [m | 15¢ | 13 lc lf [5s | |4 [0 |m | se | (2, A) and (2, C) were changed from ‘m' to 'f'. (2, 8) and (4, D) were changed from ‘f' to ‘nm’. Solution UPDATE Salary SET sex UPDATE Salary SET sex CASE WHEN sex= THEN '#" ELSE ‘m' END 1045. Customers Who Bought All Products LeetCode Medium | @ Table: customer | customer_id | int || product_key | int | product_key is a foreign key to Product table. Table: Product | Column Name | Type | | product_key | int | product_key is the primary key column for this table. Write an SQL query for a report that provides the customer ids from the Customer table that bought all the products in the Product table. For example: Customer table: | customer_id | product_key | Ia 12 13 13 fa Product table: | product_key |Result table: | custoner_id | ‘The customers who bought all the products (5 and 6) are customers with id 1 and 3 =a Solution SELECT customer_id FROM Customer GROUP NY customer_id HAVING count (DISTINCT product_key) = ( SELECT count(1) FROM Product) 1050. Actors and Directors Who Cooperated At Least Three ‘Times | Easy | (@) LeetCode Table: ActorDirector | Column Name | Type | | actorid = | int | | director_id | int | timestamp | int | timestamp is the primary key colunn for this table.Write a SQL query for a report that provides the pairs (actorid, directorid) where the actor have cooperated with the director at least 3 times. Example: ActorDirector table: | actor_id | director_id | timestamp | pune ja fa [a fa Ja [2 12 Result table: | actorid | director_id | Ia The only pair is (1, 1) where they cooperated exactly 3 times. Solution SELECT actor_id, director_id FROM ActorDirector GROUP BY actor_id, director_id HAVING COUNT(1)>=3 1068. Product Sales Analysis I | Easy @ LeetCodeTable: sales | saleid = | int | | product_id | int | | year [ant | | quantity = | int | | price [ant | (sale_id, year) is the primary key of this table. product_id is a foreign key to Product table. Note that the price is per unit. Table: Product | column Name | Type | | product_id | int | | product_name | varchar | product_id is the primary key of this table. Write an SQL query that reports all product names of the products in the Sales table along with their selling year and price. For example: Sales table | sale_id | product_id | year | quantity | price | la | 108 | 2008 | 10 | seee |2 | 100 | 2009 | 12 | seea | 7 | 208 | 2e12 | 15 | 9000 | Product table: | product_id | product_nane | | 100 | Nokia | | 200 | Apple | | 300 | Samsung | Result table: | product_name | year | price | | Nokia | 2008 | seee | | Nokia | 2009 | 5000 | | Apple | 2e11 | 9e0e | Solution SELECT product_name, year, price FROM Sales JOIN Product ON Product.product_id = Sales.product_id 1069. Product Sales Analysis II | Easy | @ LeetCode Table: sales | Column Name | Type || sale_id [int | | product_id | int | | year [int | | quantity | int | | price [int | sale_id is the primary key of this table. product_id is a foreign key to Product table. Note that the price is per unit. Table: product | Column Name | Type | | productid | int = | | product_name | varchar | product_id is the primary key of this table. Write an SQL query that reports the total quantity sold for every product id. The query result format is in the following example: Sales table | sale_id | product_id | year | quantity | price | fa | 1¢@ | 2008 | 10 | seee | [2 | 100 | 2009 | 12 | seea | \7 | 206 | 2611 | a5 | 9eee | Product table:| product_id | product_nane | | 100 | Nokia | | 200 | Apple | | 300 | Samsung | Result table: | product_id | total_quantity | Solution SELECT product_id, sum(quantity) AS total_quantity FROM Sales GROUP BY product_id; 1070. Product Sales Analysis Ill | Medium | {i LeetCode Table: sales | saleid | int | | product_id | int | | year [ant | | quantity | int | | price [ant |sale_id is the primary key of this table. product_id is a foreign key to Product table. Note that the price is per unit. Table: Product | Colum Name | Type = | | product_id | int | product_name | varchar | product_id is the primary key of this table. Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold. The query result format is in the following example: Sales table | sale_id | product_id | year | quantity | price | ha | 108 | 2608 | 10 | seee | 12 108 2009 | 12 5000 7 | 206 | 2611 | a5 | 9080 | Product table: | product_id | product_nane | | 100 | Nokia | Apple | 300 | Samsung |Result table: | product_id | first_year | quantity | price | | 100 | 2008 | 10 | see | | 200 | 2011 | a | 9000 | Solution a SELECT product_id, year first_year, quantity, price FROM Sales WHERE (product_id, year) IN (SELECT product_id, MIN(year) FROM Sales GROUP BY product_id) 1075. Project Employees I | Easy | (@ LeetCode Table: Project | Column Name | Type | | project_id | int | | enployee_id | int | (project_id, employee id) is the primary key of this table. employee_id is a foreign key to Employee table.Table: Employee | Column Name | type | | employee_id [aint | | name | varchar | | experience years | int | employee_id is the primary key of this table. Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits. The query result format is in the following example: Project table: | project_id | employee_id | Employee table: | employee_id | name | experience_years | | Khaled | [au | | ohn | | RUN | DoeResult table: | project_id | average_years | ‘The average experience years for the first project is (3 +2 +1) / 3 = 2.00 and — Solution SELECT p.project_id, ROUND(AVG(e.experience_years),2) average_years FROM Project p JOIN Employee e ON p-enployee_id = e.enployee_id GRouP BY p.project_id 1076. Project Employees II | Easy | (@ LeetCode Table: Project | Column Name | Type | | project_id | int | | employee_id | int | (project_id, employee_id) is the primary key of this table. employee_id is a foreign key to Employee table.Table: Employee | Column Name | type | | employee_id [aint | | name | varchar | | experience years | int | employee_id is the primary key of this table. Write an SQL query that reports all the projects that have the most employees. The query result format is in the following example: Project table: | project_id | employee_id | ha ja ja 2 12 Employee table: | employee_id | name | experience_years | | Khaled | Jai | | ohn | | | doe Result table:| project_id | ‘The first project has 3 employees while the second one has 2. SELECT project_id FROM Project GROUP BY project_id HAVING COUNT(employee_id) = (SELECT COUNT(employee_id) FROM Project GROUP BY project_id ORDER BY COUNT(employee_id) DESC LIMIT 1) 1077. Project Employees II | Medium | ( LeetCode Table: Project | Column Nane | Type | | project_id | int | | employee_id | int | (project_id, employee id) is the primary key of this table. employee_id is a foreign key to Employee table. Table: Employee| Column Name | type | | employee_id [aint | | name | varchar | | experience years | int | employee_id is the primary key of this table. Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years. The query result format is in the following exampl Project table: | project_id | employee_id | fa la fa 12 2 Employee table: | employee_id | name | experience_years | a | Khaled | 2 Jal | 3 | John | 4 | | Doe| project_id | employee id | la fa 13 | 12 Both employees with id 1 and 3 have the most experience among the employees of th a SELECT p-project_id, e.enployee_id FROM Project p LEFT JOIN Employee e ON p.enployee_id = e.enployee_id WHERE (p.project_id, e.experience_years) IN (SELECT P.project_id, MAX(e.experience_years) FROM Project p JOIN Employee e ON p.enployee_id = e.employee_id GROUP By P.project_id) 1082. Sales Analysis I | Easy | (@ LeetCode Table: Product | Column Wane | Type| productid | int | | product_name | varchar | | unit_price | int | product_id is the primary key of this table. Table: Sales | Column Name | Type = | | seller_id | int I | product_id | int | | buyerid | int | | saledate | date | | quantity — | int | | price [aint | This table has no primary key, it can have repeated rows. product_id is a foreign key to Product table. Write an SQL query that reports the best seller by total sales price, If there is a tie, report them all. The query result format is in the following exampl Product table: | product_id | product_nane | unit_price | ja | sa | 1000 | 2 G4 800 | 13 | Phone | 1400 | Sales table| seller_id | product_id | buyer_id | sale_date | quantity | price | \a ie \a | 2e19-e1-21 | 2 | 2000 | ja 12 12 | 2019-02-17 | 2 | 800 | j2 12 13 | 2019-06-02 | 1 | 800 | 13 13 \4 | 2019-05-13 | 2 | 2800 | | sellerid | la | 13 | Both sellers with id 1 and 3 sold products with the most total price of 2800. Solution SELECT seller_id FROM Sales GROUP BY seller_id HAVING SUM(price) = (SELECT SUM(price) FROM Sales GROUP BY seller_id ORDER BY SUM(price) DESC LIMIT 1) 1083. Sales Analysis II | Easy | @ LeetCode Table: Product| column Nae | Type | | productid | int = | | product_name | varchar | | unit_price | int | product_id is the primary key of this table. Table: sales | Column Name | Type = | | sellerid | int | | product_id | int | | buyerid = | int | | saledate | date | | quantity — | int | | price [aint | This table has no primary key, it can have repeated rows. product_id is a foreign key to Product table. Write an SQL query that reports the buyers who have bought $8 but not iPhone. Note that S8 and iPhone are products present in the Product table. The query result format is in the following example: Product table: | product_id | product_nane | unit_price | | s8 | 1000 I | 800 |13 | 4Phone | 1400 Sales table | seller_id | product_id | buyer_id | sale_date | quantity | price | ja Ia |a | 2019-01-22 | 2 | 2000 | \a 12 12 | 2019-02-17 | 1 | 800 | j2 ie 13 | 2019-06-02 | 1 | 800 | 13 13 13 | 2019-05-13 | 2 | 2800 | Result table: | buyer_id = | ‘The buyer with id 1 bought an $8 but didn*t buy an iPhone. The buyer with id 3 bo = Solution SELECT DISTINCT s.buyer_id FROM Sales s LEFT JOIN Product p ON s.product_id = p.product_id WHERE p.product_name = 'S8" AND s.buyer_id NOT IN (SELECT s.buyer_id FROM Sales s LEFT JOIN Product p ON s.product_id = p.product_id WHERE p.product_nane = 'iPhone’) 1084. Sales Analysis III | Easy | (@ LeetCodeReports the products that were only sold in spring 2019. That is, between 2019-01- 01 and 2019-03-31 inclusive. Select the product that were only sold in spring 2019. Product table: | product_id | product_nane | unit_price | [a | s8 | 1000 | 12 | 64 | 800 | 13 | 4Phone | 1400 | | seller_id | product_id | buyer_id | saledate | quantity | price | [a [a Ja | 2019-e1-21 | 2 | 200e | \a 12 12 | 2019-02-17 | 4 | 800 | j2 12 13 | 2019-06-02 | 4 | 800 | 13 13 \4 | 2019-05-13 | 2 | 2800 | Result table: | product_id | product_name | The product with id 1 was only sold in spring 2019 while the other two were sold = Solution (SELECT DISTINCT s.product_id, p.product_nane FROM Sales s LEFT JOIN Product p ON s-product_id = p.product_idWHERE s.sale_date >= '2019-01-01' AND s.sale_date <= '2019-03-31') EXCEPT (SELECT DISTINCT s.product_id, p.product_nane FROM Sales s LEFT JOIN Product p ON s.product_id = p.product_id WHERE s.sale_date < '2019-01-01' OR s.sale_date > '2019-03-31') 1097. Game Play Analysis V | Hard | (@ LeetCode We define the install date of a player to be the first login day of that player. We also define day 1 retention of some date X to be the number of players whose install date is X and they logged back in on the day right after X , divided by the number of players whose install date is X, rounded to 2 decimal places. Write an SQL query that reports for each install date, the number of players that installed the game on that, day and the day 1 retention. The query result format is in the following example: Activity table: | player_id | device_id | event_date | games_played | fa 12 | 2016-03-01 | 5 | [a [2 | 2016-03-02 | 6 \ [2 13 | 2017-06-25 | 1 | 13 [a | 2016-03-01 | @ \ 13 14 | 2016-07-03 | 5 | | install_dt | installs | Dayi_retention | | 2016-03-01 | 2 | 2.50 I | 2017-06-25 | 1 | 0.20 |Player 1 and 3 installed the game on 2016-03-@1 but only player 1 logged back in Player 2 installed the game on 2017 -06-25 but didn't log back in on 2017-06-26 s —_YS Solution SELECT install_dt, COUNT(player_id) installs, ROUND (COUNT (retention) /COUNT(player_id),2) Day1_retention FROM ¢ SELECT a.player_id, a.install_dt, b.event_date retention FROM (SELECT player_id, MIN(event_date) install_dt FROM Activity GROUP BY player_id) a LEFT JOIN Activity b ON a.player_id = b.player_id AND a-install_dt + 1=b.event_date ) aS tmp GROUP By install_dt — 1098. Unpopular Books | Medium | (@ LeetCode Table: Books | Column Name | Type | book_id | int | | name | varchar | | available from | datebook_id is the primary key of this table. Table: orders | column Name | Type | | order_id [int | | book_id | int | | quantity [int | | dispatch_date | date | order_id is the primary key of this table. book_id is a foreign key to the Books table. Write an SQL query that reports the books that have sold less than 10 copies in the last year, excluding books that have been available for less than 1 month from today. Assume today is 2019-06-23. The query result format is in the following example: Books table | book_id | name | available_from | ja | "kalila And Denna” | 2010-01-01 | 12 | "28 Letters" | 2012-05-12 | 13 | "The Hobbit" | 2819-06-10 | l4 | "13 Reasons Why" | 2019-@6-@1 | Is | "The Hunger Games" | 2008-09-21 | orders table: | order_id | book_id | quantity | dispatch_date || 2018-07-26 | | 2ers-11-05 | | 2e19-06-11 | | 2019-06-05 | | 2019-06-20 | | 2009-02-02 | | | la |2 13 la Is le |7 wue awe 2010-04-13 | book_id | nane | | “kalila And Denna" | | "28 Letters™ | | “The Hunger Games" | Solution SELECT b.book_id, b.name FROM Books b LEFT JOIN ( SELECT book_id, SUM(quantity) nsold FROM Orders WHERE dispatch_date BETWEEN '2@18-@6-23' AND '2019-06-23' GROUP BY book_id yo ON b.book_id = 0.book_id WHERE (o.nsold < 10 OR o.nsold IS NULL) AND DATEDIFF('2019-06-23", b.available from) > 3@1107. New Users Daily Count | Medium | (@ LeetCode Table: Traffic | column Name | Type | | user_id [int | | activity [enum | | activity date | date | There is no primary key for this table, it may have duplicate rows. The activity colunn is an ENUM type of (‘login', ‘logout’, ‘jobs’, ‘groups’, "hom = Write an SQL query that reports for every date within at most 90 days from today, the number of users that logged in for the first time on that date. Assume today is 2019-06-30. The query result format is in the following exampl text Traffic table: | user_id | activity | activity date | fa | login | 2e19-@5-e1 | Ia | honepage | 2019-@5-01 | fa | logout | 2e19-@5-e1 | 12 | login | 2e19-@6-21 | |2 | logout | 2e19-@6-21 | 13 | login | 2e19-e1-e1 | 13 | jobs | 2e19-e1-e1 | 13 | logout | 2019-e1-01 | 14 | login | 2e19-@6-21 | ja | groups | 2019-06-21 | l4 | logout | 2e19-e6-21 | Is | login | 2019-03-21 |Is | logout | 2019-83-01 | [5 | login | 2e19-@6-21 | Is | logout | 2019-06-21 | Result table: | login_date | user_count | 2019-@5-e1 | 2019-06-22 Note that we only care about dates with non zero user count. ‘The user with id 5 first logged in on 2019-03-01 so he's not counted on 2619-26-2 = Solution SELECT login_date, COUNT(user_id) AS user_count: FROM (SELECT user_id, MIN(activity_date) AS login_date FROM Traffic WHERE activity = ‘login’ GROUP BY user_id) AS t WHERE login_date >= DATE_ADD('2019-06-30", INTERVAL -9@ DAY) AND login date « GROUP BY login_date SELECT login_date, COUNT(user_id) user_count FROM, (SELECT user_id, MIN(activity_date) as login_date FROM Traffic WHERE activity="login* GROUP BY user_id) as t WHERE DATEDIFF('2019-@6-30", login_date) <= 90 GROUP BY login_date1112. Highest Grade For Each Student | Medium | (@ LeetCode Table: Enrollments | column Name | Type | | student_id | int | | course_id | int | | grade [int | (student_id, course_id) is the primary key of this table Write a SQL query to find the highest grade with its corresponding course for each student. In case of a tie, you should find the course with the smallest courseid, The output must be sorted by increasing studentid. The query result format is in the following example: Enrollments table: | student_id | course_id | grade |Solution SELECT student_id, MIN(course_id) course_id, grade FROM Enrollments WHERE (student_id, grade) IN (SELECT student_id, MAX(grade) FROM Enrollments GROUP BY student_id) GROUP BY student_id ORDER BY student_id; 1113.Reported Posts Easy | (@ LeetCode Table: Actions | column Name | Type — | | user_id [int | | post_id [ant | | actiondate | date | | action Jenun | | extra | varchar | There is no primary key for this table, it may have duplicate rows. The action column is an ENUM type of (‘view', ‘like’, ‘reaction’, ‘comment’, ‘rep The extra column has optional information about the action such as a reason for r —Write an SQL query that reports the number of posts reported yesterday for each report reason. Assume today is 2019-07-05. The query result format is in the following example: Actions table: | user_id | post_id | action_date | action | extra | ha ha | 2019-07-01 | view | null | fa la | 2e19-07-e1 | like | null | ia ja | 2019-07-01 | share | null | [2 la | 2@19-07-e4 | view | null | 12 la | 2019-07-04 | report | span | 13 14 | 2019-07-04 | view | mull | 13 la | 2019-07-04 | report | span | ja [3 | 2019-07-22 | view | mull | la 13 | 2019-07-02 | report | span | 1s [2 | 2019-07-04 | view | null | Is 12 | 2019-07-04 | report | racism | 15 1s | 2019-07-04 | view | mull | Is Is | 2019-07-04 | report | racism | + | spam | racism Note that we only care about report reasons with non zero number of reports. SolutionSELECT extra report_reason, COUNT(DISTINCT post_id) report_count. FROM, (SELECT post_id, extra FROM Actions WHERE action_date = DATE_SUB('2019-07-05", INTERVAL 1 DAY) AND action = ‘report') aS tmp GROUP BY extra 1126. Active Businesses Medium | (@ LeetCode Table: Events | column Wane | Type — | | business id | int | | event_type | varchar | | occurences | int | (business_id, event type) is the primary key of this table. Each row in the table logs the info that an event of some type occured at some bu —_ Write an SQL query to find all active businesses. ‘An active business is a business that has more than one event type with occurences greater than the average occurences of that event type among all businesses. The query result format is in the following exampl | business_id | event_type | occurencesja | reviews | 7 I [3 | reviews | 3 | Ja | ads Jaa | |2 | ads Ll? | 13 | ads l6 I fa | page views | 3 | 12 | page views | 12 | Result table: | business_id | Average for ‘reviews’, ‘ads’ and ‘page views’ are (7+3)/2+5, (114746)/3=8, (3+12) Business with id 1 has 7 ‘reviews’ events (more than 5) and 11 ‘ads’ events (more —, Solution SELECT business_id FROM (SELECT a.business_id, a.event_type, a.occurences, b.event_avg FROM Events a LEFT JOIN (SELECT event_type, AVG(occurences) event_avg FROM Events GROUP BY event_type) b ON a.event_type = b.event_type) tmp WHERE occurences > event_avg GROUP BY business_id HAVING COUNT(event_type) > 1 1127. User Purchase Platform | Hard | (@ LeetCode Table: spending| column Name | Type | Juserid = | int | | spend_date | date | | platforn | enum | | amount, | int | The table logs the spendings history of users that make purchases from an online (user_id, spend_date, platform) is the primary key of this table. The platform column is an ENUM type of (‘desktop', ‘mobile'). —_-, Write an SQL query to find the total number of users and the total amount spent using mobile only, desktop only and both mobile and desktop together for each date. The query result format is in the following example: Spending table: | user_id | spend_date | platform | anount | ja | 2019-07-01 | mobile | 100 | fa | 2019-@7-@1 | desktop | 100 12 | 2019-07-01 | mobile | 100 | [2 | 2019-@7-@2 | mobile | 106 13 | 2019-07-01 | desktop | 100 | 13 | 2019-27-02 | desktop | 100 | | 2019-07-01 | desktop | 100 | 2e19-@7-@1 | mobile | 10¢| 2019-07-01 | both | 200 Ja \ | 2e19-@7-@2 | desktop | 100 fa | | 2019-07-02 | mobile | 100 Ja \ | 2019-@7-@2 | both le le | On 2019-07-01, user 1 purchased using both desktop and mobile, user 2 purchased u On 2019-07-@2, user 2 purchased using mobile only, user 3 purchased using desktop —_—_—_—,1 Solution SELECT aa.spend_date, 2a.platforn, COALESCE (bb.total_anount, @) total_anount, COALESCE(bb.total_users,@) total_users FROM (SELECT DISTINCT(spend_date), a.platforn FROM Spending JOIN (SELECT ‘desktop’ AS platform UNION SELECT ‘mobile’ AS platform UNION SELECT "both’ AS platform da ) 2a LEFT 30IN (SELECT spend_date, platform, SUM(anount) total_amount, COUNT(user_id) total_users FROM (SELECT spend_date, user_id, (CASE COUNT (DISTINCT platform) WHEN 1 THEN platform WHEN 2 THEN ‘both END) platform, SUM(amount) anount FROM SpendingGROUP BY spend_date, user_id yb GROUP BY spend_date, platform ) bb ON aa.platform = bb.platform AND aa.spend_date = bb.spend_date 1132. Reported Posts II | Medium | (@ LeetCode Table: Actions | column Name | Type | | user_id | int | | post_id Jaint | | actiondate | date | | action J enum | | extra | varchar | There is no primary key for this table, it may have duplicate rows. The action column is an ENUM type of (‘view', ‘like’, ‘reaction’, ‘comment’, ‘rep ‘The extra column has optional information about the action such as a reason for r — Table: Removals | Column Name | Type | | post_id | int | | remove date | date | post_id is the primary key of this table. Each row in this table indicates that some post was removed as a result of being— Write an SQL query to find the average for daily percentage of posts that got removed after being reported as spam, rounded to 2 decimal places. The query result format is in the following example: Actions table: | user_id | post_id | action_date | 2019-07-01 2019-07-01 2019-07-01 2019-07-04 2019-07-04 | | | | Il | 2019-07-04 | 2019-07-04 | | | I | Il 2019-07-02 2019-07-02 1 1 1 2 2 3 3 4 4 5 2019-07-03 2019-07-03 2019-07-03 VUN NYU Re N Be 2019-07-03 action view Like share report report report report report extra | null | null | null | null | spam | null | spam | null | spam | null | racism | null | racism | Removals table: | post_id | remove_date | 12 | 2019-07-20 | 13 | 2019-07-18 |The percentage for 2019-07-04 is 5% because only one post of two spam reported p The percentage for 2019-07-02 is 100% because one post was reported as spam and i ‘The other days had no spam reports so the average is (5@ + 100) / 2 = 75% Note that the output is only one number and that we do not care about the remove Solution WITH t2 AS( ——TT— v SELECT a.action_date, (COUNT(DISTINCT r.post_id))/(COUNT (DISTINCT a.post_id)) AS FROM (SELECT action date, post_id FROM actions WHERE extra = ‘spam’ AND action = ‘report') a LEFT 301N removals r ON a.post_id = r.post_id GROUP BY a.action_date) SELECT ROUND(AVG(t1.result)*10@,2) AS average_daily_percent FROM t1. 1141. User Activity for the Past 30 Days I | Easy | (@ LeetCode Table: activity | column Name | Type | user_id | int session id | int activity date | date activity_type | enumThere is no primary key for this table, it may have duplicate rows. ‘The activity_type column is an ENUM of type (‘open_session’, ‘end_session', ‘scro The table shows the user activities for a social media website. Note that each session belongs to exactly one user. — Write an SQL query to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on some day if he/she made at least one activity on that day. The query result format is in the following example: Activity table: | user_id | session_id | activity date | | 2019-07-20 | 2019-07-20 | 2019-07-20 | 2019-07-28 | 2019-07-21 | 2019-07-21 | | | | | ee Ree 2019-07-21 2019-07-21 2019-07-21 2019-06-25 Ja la fa |2 |2 2 [3 13 13 l4 l4 2019-96-25 | 2¢19-07-26 | 2 Il | 2019-07-21 | 2 | Note that we do not care about days with zero active users. activity type | open_session scroll_down end_session open_session send_message end_session open_session send_message end_session open_session end_sessionSolution SELECT activity date AS day, COUNT(DISTINCT user_id) AS active_users FROM activity WHERE activity date > '2019-06-26' AND activity date < '2019-@7-27" GROUP BY activity date 1142. User Activity for the Past 30 Days II | Easy | @ LeetCode Table: activity | Column Name | Type = | user_id | int | session id | int | | | | | | activitydate | date | activity type | enum There is no primary key for this table, it may have duplicate rows. The activity type column is an ENUM of type (‘open_session’, ‘end_session', ‘scro The table shows the user activities for a social media website. Note that each session belongs to exactly one user. — Write an SQL query to find the average number of sessions per user for a period of 30 days ending 2019-07-27 inclusively, rounded to 2 decimal places. The sessions we want to count for a user are those with at least one activity in that time period. The query result format is in the following example: Activity table: | user_id | session_id | activity date | activity type |2019-07-20 2019-07-20 2019-07-20 2019-07-20 2019-07-21 2019-07-21 2019-07-21 2019-07-21 2019-07-21 2019-07-21 2019-07-21 2019-07-21 2619-06-25 we ununnne eee 1 1 1 2 2 2 3 3 3 3 3 3 4 4 2019-96-25 open_session scroll_down end_session open_session send_message end_session open_session send_message end_session open_session scroll_down end_session open_session end_session Result table: | average_sessions_per_user | User 1 and 2 each had 1 session in the past 30 days while user 3 had 2 sessions s Solution — SELECT TFNULL(ROUND(AVG(a.num),2),) AS average_sessions_per_user FROM ( SELECT COUNT(DISTINCT session_id) AS num FROM activity WHERE activity date BETWEEN '2019-@6-28" AND ‘2019-07-27' GROUP BY user_id) a1148. Article Views I | Easy | (i LeetCode Table: views | column Name | Type | J article id | int | | author_id | int | | viewerid = | int | | view_date [date | There is no primary key for this table, it may have duplicate rows. Each row of this table indicates that some viewer viewed an article (written by s Note that equal author_id and viewer_id indicate the same person. — Write an SQL query to find all the authors that viewed at least one of their own articles, sorted in ascending order by their id. The query result format is in the following example: Views table | article_id | author_id | viewer_id | viewdate | 2019-08-01 2019-08-01 2019-07-22 2019-07-21 | | 2019-08-02 | | | | | we RN Nee I | I 2019-08-22 | | I | 2019-07-21SELECT DISTINCT author_id AS id FROM Views WHERE author_id = viewer_id ORDER BY author_id 1149. Article Views II | Medium | (@ LeetCode Table: views | column Name | Type | J articleid | int | | authorid = | int | | viewerid = | int | | view date | date | There is no primary key for this table, it may have duplicate rows. Each row of this table indicates that some viewer viewed an article (written by s Note that equal author_id and viewer_id indicate the same person. — Write an SQL query to find all the people who viewed more than one article on the same date, sorted in ascending order by their id.The query result format is in the following example: Views table | article_id | author_id | viewer_id | view date | 2019-08-01 2019-28-01 2019-08-02 2019-08-01 2019-08-02 2019-07-22 2019-07-21 2019-07-21 Solution SELECT DISTINCT viewer_id AS id FROM views GROUP BY viewer_id, view_date HAVING count (DISTINCT article_id)>1 ORDER BY 1 1158. Market Analysis I | Medium | (@ LeetCodeTable: users | column Nave | Type | | user_id [ant | | join_date ldate | | favorite_brand | varchar | user_id is the primary key of this table. This table has the info of the users of an online shopping website where users ca Table: orders | Column Name | Type | | order_id | int | | orderdate | date | | itemid | int | | buyer_id [int | | seller_id | int | order_id is the primary key of this table. item_id is a foreign key to the Items table. buyer_id and seller_id are foreign keys to the Users table. Table: rtens | column Name | Type | | itemid [int | | itembrand — | varchar | —item_id is the primary key of this table. Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019. The query result format is in the following example: Users table | userid | join.date | favorite_brand | a | 2018-01-01 | Lenovo I 2 | 2018-02-29 | Samsung | 3 | 2018-01-19 | LG I 4 | 2018-05-21 | HP | | order_id | order_date | itemid | buyer_id | seller_id | | 2019-08-01 | 4 | 2e18-e8-e2 | 2 | 2019-08-03 | 3 | 2e18-e8-e4 | 1 | 2018-08-04 | 1 | 2e19-e8-5 | 2 auhune Rue eR NUe DN Items table | itemid | item_brand | | Sansung | LenovoResult table: | buyer_id | join_date | orders _in_2019 | | 2018-01-01 | | 2018-02-09 | | 2018-01-19 | | 2018-05-21 | ia [2 13 |4 Solution SELECT user_id AS buyer_id, join_date, coalesce(a.orders_in_2019,0) FROM users LEFT 30IN ( SELECT buyer_id, coalesce(count(*), @) AS orders_in_2019 FROM orders 0 JOIN users u ON u.user_id = o.buyer_id WHERE extract(‘year’ FROM order_date) = 2019 GROUP BY buyer_id) a ON users.user_id = a.buyer_id 1159. Market Analysis II | Hard | (@ LeetCode Table: Users | column Name | Type | | user_id | int || join_date | date | | favorite_brand | varchar | user_id is the primary key of this table. This table has the info of the users of an online shopping website where users ca ——T Table: orders | column Wane | Type | | order_id [int | | order_date | date | | itemid [int | | buyer_id | int | | sellerid = | int | order_id is the primary key of this table. item_id is a foreign key to the Items table. buyer_id and seller_id are foreign keys to the Users table. Table: Items | Column Name | Type | | itemid | int | | itembrand — | varchar | item_id is the primary key of this table. Write an SQL query to find for each user, whether the brand of the second item (by date) they sold is their favorite brand, If a user sold less than two items, report the answer for that user as no.It is guaranteed that no seller sold more than one item on a day. The query result format is in the following example: Users table | user_id | join_date | favorite_brand | | 2019-01-01 | Lenovo | 2019-02-09 | Samsung | 2e19-e1-19 | LG | 2019-05-21 | HP orders table: | order_id | order_date | item_id | buyer_id | seller_id | | 2e19-08-01 | 4 | | 2019-08-02 | 2 | | 2019-08-03 | 3 | | 2019-08-04 | 1 | | 2e19-e8-04 | 1 | | 2019-08-05 | 2 | a RN UUW | Sansung | Lenovo | us | HP Result table: | seller_id | 2nd_item_fav_brand |l2 13 yes yes ‘The answer for the user with id 1 is no because they sold nothing. The answer for the users with id 2 and 3 is yes because the brands of their secon ‘The answer for the user with id 4 is no because the brand of their second sold it — Solution SELECT user_id AS seller_i IF(ISNULL(item_brand), “no”, “yes") AS 2nd_item_fav_brand FROM Users LEFT JOIN (SELECT seller_id, item_brand FROM Orders INNER JOIN Items ON Orders. itemid = Items.iten_id WHERE (seller_id, order_date) IN (SELECT seller_id, MIN(order_date) AS order_date FROM Orders WHERE (seller id, order_date) NOT IN (SELECT seller_id, MIN(order_date) FROM Orders GROUP BY seller_id) GROUP BY seller_id) past ON Users.user_id = t.seller_id and favorite_brand = item_brand WITH €1 AS( SELECT user_id, CASE WHEN favorite_brand = item_brand THEN "yes" ELSE “no” END AS 2nd_iten_fav_brand FROM users u LEFT JOIN, item_brand, RANK() OVER(PARTITION BY seller_id ORDE (SELECT o.item_id, seller_i FROM orders o join items i USING (item_id)) a ON u.user_id = a.seller_id WHERE a.rk = 2) SELECT u.user_id AS seller_id, COALESCE(2nd_iten_fav_brand,"no") AS 2nd_iten_fav_ FROM users u LEFT JOIN t1 USING(user_id) 1164, Product Price at a Given Date | Medium | LeetCode Table: Products | Column Name Type | productid | int | | newprice | int | | change_date | date | (product_id, change date) is the primary key of this table. Each row of this table indicates that the price of some product was changed to a ——TT—3 Write an SQL query to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10. The query result format is in the following example: Products table: | product_id | new_price | change_date | Ja | 20 | 2019-08-14 |l2 | se | 2019-08-14 | fa | 38 | 2@19-e8-15 | Ja | 35 | 2019-08-16 | |2 | 6s | 2019-08-17 | 13 | 20 | 2019-08-18 | Result table: | product_id | price | |se | 35 | |. | l2 fa | 13 Solution WITH t1 AS ( SELECT a.product_id, new_price FROM( SELECT product_id, max(change_date) AS date FROM products WHERE change_date<="2019-08-16" GROUP BY product_id) a JOIN products p ON a.product_id = p.product_id AND a.date = p.change_date), 2 As ( SELECT distinct product_id FROM products) SELECT t2.product_id, coalesce(new_price,1®) AS price FROM t2 LEFT JOIN ti ON t2.product_id = t1.product_id ORDER BY price DESC .SELECT t1.product_id AS product_id, IF(TSNULL(t2.price), 18, t2.price) AS price FROM (SELECT distinct product_id FROM Products) AS ti 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 1173. Immediate Food Delivery I | Easy | ® LeetCode Table: velivery | Column Name | type | | delivery_id | int | | custoner_id [ant | | order_date | date | | custoner_pref_delivery date | date | delivery_id is the primary key of this table. The table holds information about food delivery to customers that make orders at —!YJYSYXS———— If the preferred delivery date of the customer is the same as the order date then the order is called immediate otherwise it's called scheduled. Write an SQL query to find the percentage of immediate orders in the table, rounded to 2 decimal places.The query result format is in the following example: Delivery table: | delivery_id | customer_id | order_date | custoner_pref_delivery date | 2019-08-01 | 2019-8-02 2019-08-02 | 2019-08-02 2019-08-11 | 2019-08-11 2019-08-24 | 2019-08-26 2019-08-21 | 2019-@8-22 ae ken 2019-@8-11 | 2019-@8-13 Result table: | inmediate_percentage | ‘The orders with delivery id 2 and 3 are immediate while the others are scheduled. a Solution SELECT ROUND(SUM(CASE WHEN order_dati FROM Delivery; ustomer_pref_delivery date THEN 1 ELSE @ END)/cou SELECT ROUND(avg(CASE WHEN order_date=customer_pref_delivery date THEN 1 ELSE @ END)*100 FROM delivery