본문 바로가기

SQL/LeetCode&HackerRank

 (14)
[MySQL] Leetcode - 1907. Count Salary Categories 문제: https://leetcode.com/problems/count-salary-categories/description/ 나의 풀이: select category , count(*) as accounts_count from ( select case when income 50000 then 'High Salary' else 'Average Salary' end as category , account_id from accounts) sub group by category 근데 이렇게 하면 틀림!! 왜냐하면 문제는 케이스에 해당하는 값이 없을 경우에 0값을 보고싶은 건데 이렇게 하면 카운트 할 수 없는 카테고리는 결과값에서 제외가 된..
[MySQL] Leetcode - 585. Investments in 2016 문제: https://leetcode.com/problems/investments-in-2016/description/?envType=study-plan-v2&envId=top-sql-50 나의 풀이: select round(sum(tiv_2016), 2) as tiv_2016 from insurance where tiv_2015 in ( select tiv_2015 from insurance group by tiv_2015 having count(*) > 1) and (lat, lon) in ( select lat, lon from insurance group by lat, lon having count(*) = 1) 다른 방법: 상호연관서브쿼리 (correlated subquery) select ro..
[MySQL] Hackerrank - Occupations 문제: https://www.hackerrank.com/challenges/occupations/problem 한 번 풀었던 문젠데 못 풀었다!! ㅠㅠ 물론 예전에도 못 풀어서 풀이를 봤지만 이번에도 못 풂 ㅎ ㅠ SELECT MAX(IF(occupation = 'Doctor', name, NULL)) AS Doctor , MAX(IF(occupation = 'Professor', name, NULL))AS Professor , MAX(IF(occupation = 'Singer', name, NULL))AS Singer , MAX(IF(occupation = 'Actor', name, NULL))AS Actor FROM ( SELECT * , ROW_NUMBER() OVER (PARTITION BY oc..
[MySQL] Leetcode - 601. Human Traffic of Stadium 문제: 스스로 풀지 못 함 ㅠ! 다른 사람 풀이: 1. 관중 100이 넘는 조건이 걸린 테이블에서 id - ROW_NUMBER() OVER (ORDER BY id) AS diff 컬럼 만든 후, 위드문에 저장 WITH tb AS ( SELECT * , id - ROW_NUMBER() OVER (ORDER BY id) AS diff FROM stadium s WHERE people >= 100 ORDER BY visit_date ) 100 조건이 없었으면 id - row_number() OVER (ORDER BY id) = 0 이었을테지만, 조건 때문에 없어지는 id가 있어서 id가 끊길 때마다 diff 값이 증가하게 되고, 연속된 숫자끼리는 같은 diff 값을 가짐! 이 말이 무슨 뜻이냐면 | id | ..
[MySQL] Leetcode - 1045. Customers Who Bought All Products 문제: https://leetcode.com/problems/customers-who-bought-all-products/description/ 나의 풀이: select customer_id from ( select customer_id , count(distinct product_key) AS p_cnt from customer group by customer_id) sub where p_cnt = ( select count(distinct product_key) from product); 정답은 맞췄지만 뭔가... 돌아온 것 같아서 마음에 안 듦 그래서 솔루션을 보니 프롬절에 서브쿼리로 안 넣고 해빙으로 바로 조건 거는 방법이 있다 select customer_id from customer group..
[MySQL] HackerRank - SQL Project Planning 문제: https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true 풀이 1: SELECT start_date , MIN(end_date) FROM (SELECT start_date FROM projects WHERE start_date NOT IN (SELECT end_date FROM projects)) A, (SELECT end_date FROM projects WHERE end_date NOT IN (SELECT start_date FROM projects)) B WHERE start_date < end_date GROUP BY start_date ORDER BY DATEDIFF(MIN(end_date), start_da..
[MYSQL] LeetCode 184, 185 - Window Function 사용 풀이 note: MySQL 8.0 버전 이상부터 윈도우 함수를 지원한다. #184 문제 출처: https://leetcode.com/problems/department-highest-salary/ 문제: 부서별 가장 높은 샐러리 찾기 풀이: - MAX SELECT D.NAME AS DEPARTMENT , E.NAME AS EMPLOYEE , E.MAX_SALARY AS SALARY FROM DEPARTMENT D JOIN ( SELECT NAME , SALARY , DEPARTMENTID , MAX(SALARY) OVER (PARTITION BY DEPARTMENTID) AS MAX_SALARY FROM EMPLOYEE ) E ON D.ID = E.DEPARTMENTID WHERE E.SALARY = E.MA..
[MySQL] LeetCode 180 - Consecutive Numbers 풀이 3가지 (Join, Lead, Leg) 문제 출처: https://leetcode.com/problems/consecutive-numbers/description/ 1. SELF INNER JOIN을 이용한 풀이 SELECT DISTINCT L1.NUM AS CONSECUTIVENUMS FROM LOGS L1 JOIN LOGS L2 ON L1.ID + 1 = L2.ID JOIN LOGS L3 ON L2.ID + 1 = L3.ID WHERE L1.NUM = L2.NUM AND L2.NUM = L3.NUM 2. LEAD(당겨오기)를 이용한 풀이 SELECT DISTINCT NUM AS CONSECUTIVENUMS FROM( SELECT NUM , LEAD(NUM, 1) OVER (ORDER BY ID) AS L1 , LEAD(NUM, 2) OVE..

티스토리툴바