#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

#86. [LeetCode / MySQL] 1280. Students and Examinations

문제 입력 코드 1. COUNT SELECT st.student_id, st.student_name, sub.subject_name, COUNT(ex.student_id) AS attended_exams FROM Students AS st CROSS JOIN Subjects AS sub LEFT JOIN Examinations AS ex ON (st.student_id = ex.student_id) AND (sub.subject_name = ex.subject_name) GROUP BY st.student_id, st.student_name, sub.subject_name ORDER BY st.student_id, sub.subject_name; 2. COALESCE SELECT st.student_id..

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

#85. [LeetCode / MySQL] 577. Employee Bonus

문제 입력 코드 SELECT E.name, B.bonus FROM Employee AS E LEFT JOIN Bonus AS B USING (empID) WHERE (B.bonus < 1000) OR (B.bonus IS NULL) 코드 설명 #SELECT #LEFT JOIN #JOIN #USING #WHERE #NULL 문제 출처 Employee Bonus - LeetCode Can you solve this real interview question? Employee Bonus - Table: Employee +-------------+---------+ | Column Name | Type | +-------------+---------+ | empId | int | | name | varchar ..

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

#84. [LeetCode / MySQL] 1661. Average Time of Process per Machine

문제 입력 코드 1. JOIN - AND 조건 SELECT a1.machine_id, ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time FROM Activity AS a1 JOIN Activity AS a2 ON (a1.machine_id = a2.machine_id) AND (a1.process_id = a2.process_id) AND (a1.activity_type='start') AND (a2.activity_type='end') GROUP BY a1.machine_id 2. JOIN SELECT a1.machine_id, ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time FR..

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

#83. [LeetCode / MySQL] 197. Rising Temperature

문제 입력 코드 1. SELECT w1.id FROM Weather w1, Weather w2 WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 AND w1.temperature > w2.temperature; 2. SELECT w1.id FROM Weather w1 JOIN Weather w2 ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY) WHERE w1.temperature > w2.temperature DATE_ADD(기준 날짜, INTERVAL) 함수 기준 날짜에 입력된 기간만큼 더하는 함수 SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND); # 1초 더하기 SELECT DAT..

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

#82. [LeetCode / MySQL] 1581. Customer Who Visited but Did Not Make Any Transactions

문제 입력 코드 1. SELECT V.customer_id, COUNT(V.visit_id) AS count_no_trans FROM Visits AS V LEFT JOIN Transactions AS T ON V.visit_id = T.visit_id WHERE T.transaction_id IS NULL GROUP BY V.customer_id 2. SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits WHERE visit_id NOT IN( SELECT DISTINCT visit_id FROM Transactions ) GROUP BY customer_id 코드 설명 #SELECT #COUNT #LEFT JOIN #JOIN #NULL ..

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