본문 바로가기

SQL

LeetCode / 180. Consecutive Numbers / MySQL / Medium / OVER, LAG,LEAD 2023. 12. 21. 08:41 📚문제 📝풀이 # Write your MySQL query statement below SELECT DISTINCT num AS ConsecutiveNums FROM (SELECT id, num, LAG(num) OVER (ORDER BY id) AS prev_num, LEAD(num) OVER (ORDER BY id) AS next_num FROM Logs) consecutive WHERE num = prev_num AND num = next_num ✅OVER - SQL에서 윈도우 함수(window function)를 적용할 때 사용하는 구문 - 특정 창(window) 또는 범위(range)내에서 계산 수행 - OVER 구문은 창이나 범위 의미 id를 기준으로 정렬하여 윈도우함수 생성하고 앞뒤 행의 n..
LeetCode / 185. Department Top Three Salaries / MySQL / Hard 2023. 12. 20. 09:24 📚문제 📝풀이 # Write your MySQL query statement below 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 rnk FROM Employee e JOIN Department d ON e.departmentId = d.id) rnk_etc WHERE rnk
LeetCode 1484. Group Sold Products By The Date / MySQL / Easy / GROUP_CONCAT 2023. 12. 18. 11:45 📚문제 📝풀이 # Write your MySQL query statement below SELECT sell_date , COUNT(DISTINCT product) AS num_sold , GROUP_CONCAT(DISTINCT product order by product) AS products FROM Activities GROUP BY sell_date sell_date로 그룹화 했을 때의 모든 품목들을 나열해야하는 문제 이걸 어떻게 풀지..? 혹시 그룹화된 항목들을 쭉 나열하는 함수는 없으려나? 하고 구글링 하다가 GROUP_CONCAT 함수를 찾았다 기본적으로 ','(쉼표)를 기준으로 문자열을 나열한다 다른 구분자를 사용하고 싶으면 seperator 명령을 사용하면 된다 (ex seperator ..
LeetCode / 1517. Find Users With Valid E-Mails / MySQL / Easy / SUBSTRING_INDEX / REGEX(정규표현식) 2023. 12. 18. 10:54 📚문제 📝풀이 # Write your MySQL query statement below # 내 제출 - 오답 SELECT * FROM Users WHERE SUBSTRING_INDEX(mail,'@',1) REGEXP '^[A-Za-z][A-Za-z0-9._-]+$' AND SUBSTRING_INDEX(mail,'@',-1) = 'leetcode.com' 정규표현식으로 풀어야할 것 같아서 구글링하여 풀어봤다 근데 테스트 케이스까진 맞는데 제출에서 틀린다 답과 대조해봤는데 어디서 왜 틀린지가 안나온다..생략된듯 + 그래도 mysql에서 split 기능을 하는 SUBSTRING_INDEX 함수를 배웠다 SUBSTRING_INDEX(col,'구분자',1) 해당 열을 지정하고(col) 문자열 내에서 어떤 구분자..
LeetCode / 196. Delete Duplicate Emails / MySQL / Easy / Join 2023. 12. 15. 13:57 📚문제 📝풀이 # Write your MySQL query statement below DELETE p2 FROM Person p1 JOIN Person p2 ON p1.email = p2.email AND p1.id < p2.id 아주 간단하고 신박한 방법... 다른 함수를 사용하거나 줄이 길어질 필요 없이 깔끔하게 JOIN 조건으로 나오는 테이블 통째로 제거 🧐
LeetCode / 602. Friend Requests II: Who Has the Most Friends / MySQL(Medium) / Subquery 2023. 12. 13. 11:10 📚문제 📝풀이 # Write your MySQL query statement below WITH cte_re AS( SELECT requester_id AS id , COUNT(accepter_id) AS cnt FROM RequestAccepted GROUP BY requester_id) , cte_ac AS( SELECT accepter_id AS id , COUNT(accepter_id) AS cnt FROM RequestAccepted GROUP BY accepter_id) , cte_union AS ( SELECT * FROM cte_re UNION ALL SELECT * FROM cte_ac) SELECT id , SUM(cnt) AS num FROM cte_union GROUP BY id O..
LeetCode / 1341. Movie Rating / MySQL(Medium) / Subquery 2023. 12. 12. 10:31 📚문제 📝풀이 # Write your MySQL query statement below SELECT MIN(result) AS results FROM ( SELECT u.name AS result FROM MovieRating r INNER JOIN Users u ON r.user_id = u.user_id GROUP BY u.name ORDER BY COUNT(*) DESC, u.name LIMIT 1) AS names UNION ALL SELECT MIN(result) AS results FROM ( SELECT m.title AS result FROM MovieRating r INNER JOIN Movies m ON r.movie_id = m.movie_id WHERE LEFT(created_at,..
LeetCode / 1978. Employees Whose Manager Left the Company / MySQL(Easy) / Subquery 2023. 12. 8. 21:57 📚문제 📝풀이 # Write your MySQL query statement below SELECT employee_id FROM Employees WHERE manager_id NOT IN (SELECT employee_id FROM Employees ) AND salary < 30000 ORDER BY employee_id 풀이는 간단한데 의외로 시간 오래걸렸다... 엄청 복잡하게만 생각해서 그런 것 같다 한 번 풀이 방향이 보이지 않으면 꽤 힘들어진다 🫠
LeetCode / 1907. Count Salary Categories / MySQL(Medium) 2023. 12. 7. 14:02 📚문제 📝풀이 SELECT 'High Salary' AS category , SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) AS accounts_count FROM Accounts UNION SELECT 'Average Salary' AS category , SUM(CASE WHEN income BETWEEN 20000 AND 50000 THEN 1 ELSE 0 END) AS accounts_count FROM Accounts UNION SELECT 'Low Salary' AS category , SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count FROM Accounts; 그냥 GROUP BY 해서..
LeetCode / 1204. Last Person to Fit in the Bus / MySQL(Medium) 2023. 12. 6. 12:24 📚문제 📝풀이 SELECT person_name FROM Queue WHERE turn = (SELECT MAX(turn) AS turn FROM (SELECT q1.turn , SUM(q2.weight) AS total_weight FROM Queue q1 INNER JOIN Queue q2 ON q1.turn >= q2.turn GROUP BY q1.turn HAVING total_weight = q2.turn ORDER BY q1.turn; 왼쪽테이블(q1)의 turn으로 그룹화하면 오른쪽테이블은 q1의 turn에 대해 작거나 같은 turn들이 정렬 된다 2. SUM(q2.weight) SELECT q1.turn , SUM(q2.weight) AS total_weight FROM Queue q1 I..

티스토리툴바