#109. [LeetCode / MySQL] 1204. Last Person to Fit in the Bus

문제 입력 코드 SELECT q1.person_name FROM Queue AS q1 JOIN Queue AS q2 ON q1.turn >= q2.turn GROUP BY q1.turn HAVING SUM(q2.weight)

• format_list_bulleted SQL
• · 2023. 10. 5.
• textsms

#107. [LeetCode / MySQL] 180. Consecutive Numbers

문제 입력 코드 1. SELECT DISTINCT a.num AS ConsecutiveNums FROM Logs AS a JOIN Logs AS b ON (a.id = b.id + 1) AND (a.num = b.num) JOIN Logs AS c ON (b.id = c.id + 1) AND (b.num = c.num) 2. WITH cte AS ( SELECT num, LAG(num) OVER (ORDER BY id) AS prev_num, LEAD(num) OVER (ORDER BY id) AS next_num FROM Logs ) SELECT num AS ConsecutiveNums FROM cte WHERE num = prev_num AND num = next_num GROUP BY num; 코드..

• format_list_bulleted SQL
• · 2023. 10. 3.
• textsms

#91. [LeetCode / MySQL] 1075. Project Employees I

문제 입력 코드 1. SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years FROM Project AS p JOIN Employee AS e ON p.employee_id = e.employee_id GROUP BY p.project_id 2. SELECT p.project_id, ROUND(SUM(e.experience_years)/COUNT(e.experience_years), 2) AS average_years FROM Project AS p JOIN Employee AS e ON p.employee_id = e.employee_id GROUP BY p.project_id 코드 설명 #SELECT #ROUND #AVG #SU..

• format_list_bulleted SQL
• · 2023. 9. 17.
• textsms

#90. [LeetCode / MySQL] 1251. Average Selling Price

문제 입력 코드 1. SELECT p.product_id, ROUND(SUM(units * price) / SUM(units), 2) AS average_price FROM Prices AS p JOIN UnitsSold AS u ON (p.product_id = u.product_id) AND (u.purchase_date BETWEEN start_date AND end_date) GROUP BY product_id 2. SELECT p.product_id, ROUND(SUM(units * price) / SUM(units), 2) AS average_price FROM Prices AS p INNER JOIN UnitsSold AS u ON (p.product_id = u.product_id) WHE..

• format_list_bulleted SQL
• · 2023. 9. 16.
• textsms

#88. [LeetCode / MySQL] 1934. Confirmation Rate

문제 입력 코드 1. SELECT s.user_id, ROUND(AVG(IF(c.action="confirmed",1,0)),2) AS confirmation_rate FROM Signups AS s LEFT JOIN Confirmations AS c USING(user_id) GROUP BY user_id 2. WINDOW - PARTITION BY SELECT DISTINCT s.user_id, ROUND(SUM( CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END ) OVER (PARTITION BY user_id) / COUNT(*) OVER (PARTITION BY user_id), 2) AS confirmation_rate FROM Signups AS s..

• format_list_bulleted SQL
• · 2023. 9. 14.
• textsms

#87. [LeetCode / MySQL] 570. Managers with at Least 5 Direct Reports

문제 입력 코드 1. SELF JOIN SELECT e1.name FROM Employee AS e1 JOIN Employee AS e2 ON e1.id = e2.managerId GROUP BY e1.id HAVING count(e2.managerId) >= 5; 2. SUBQUERY SELECT name FROM Employee WHERE id IN ( SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(managerId) >= 5 ) 코드 설명 #SELECT #JOIN #SELF JOIN #GROUP BY #HAVING #COUNT #SUBQUERY #IN 문제 출처 Managers with at Least 5 Direct Reports ..

• format_list_bulleted SQL
• · 2023. 9. 13.
• textsms