#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

#89. [LeetCode / MySQL] 620. Not Boring Movies

문제 입력 코드 1. MOD SELECT * FROM Cinema WHERE (MOD(id, 2) = 1) AND (description != 'boring') ORDER BY rating DESC; 2. % SELECT * FROM Cinema WHERE (id % 2 = 1) AND (description != 'boring') ORDER BY rating DESC; 코드 설명 #SELECT #MOD #ORDER BY #% 문제 출처 Not Boring Movies - LeetCode Can you solve this real interview question? Not Boring Movies - Table: Cinema +----------------+----------+ | Column Name ..

• format_list_bulleted SQL
• · 2023. 9. 15.
• 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

#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