응엉이의 엉엉코딩일기

GROUP BY, ORDER BY, LIMIT 이용SELECT months * salary AS earnings, COUNT(employee_id) AS cntFROM EmployeeGROUP BY earningsORDER BY earnings DESCLIMIT 1 WHERE절 서브쿼리 이용earnings의 값이 max_earnings와 동일한 경우를 WHERE절 서브쿼리를 통해 필터링SELECT months * salary AS earnings, COUNT(name)FROM employeeWHERE months * salary = (SELECT MAX(months * salary) FROM employee) -- SELECT의 AS 쓸 수 없음GROUP BY salary -- SEL..

https://leetcode.com/problems/department-top-three-salaries/-- department별로 연봉 순위 매기기SELECT Department, Employee, SalaryFROM ( SELECT d.name AS Department, e.name AS Employee, e.salary, DENSE_RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) AS rnk FROM employee e INNER JOIN department d ON e.departmentId = d.id) subWHERE rnk SELECT 절에서 연산한 내용 (rn..

https://leetcode.com/problems/department-highest-salary/0. FROM절 서브쿼리-- 1. 각 department별로 어떤 salary가 highest인지SELECT departmentId, MAX(salary)FROM employeeGROUP BY departmentId -- 2. 전체 employee중 해당 department의 가장 salary를 많이 받는 사람만 뽑아낼 것-- 1번 쿼리 결과를 Employee table에 INNER JOIN 해줌-- 3. 원하는 형태로 출력 위해 Department를 JOINSELECT d.name AS Department, e.name AS Employee, e.salaryFROM Employee e ..

https://leetcode.com/problems/consecutive-numbers/description/ 1. INNER JOIN을 이용한 풀이SELECT a.num AS ConsecutiveNumsFROM logs a LEFT JOIN logs b ON a.id + 1 = b.id LEFT JOIN logs c ON a.id + 2 = c.idWHERE a.num = b.num AND b.num = c.num 2. LEAD 함수를 활용한 풀이1) 2행에 나오는 숫자, 3행에 나오는 숫자를 1행에 당겨오는 작업SELECT num, LEAD(num, 1) OVER(ORDER BY id) AS next1, -- id column을 기준으로 순서를 파악한 후 num column을 1칸 당겨줌 ..

https://leetcode.com/problems/investments-in-2016/description/?envType=study-plan-v2&envId=top-sql-50 1. tiv_2015가 중복 2. lat, lon이 유일 2가지 조건을 WHERE절 서브쿼리를 이용해 작성해주면 된다 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 -- 해당 tiv_2015가 2번 이상 등장해야 함 ) AND WHERE lat, lon IN ( SELECT lat, lon FROM insuran..

https://leetcode.com/problems/exchange-seats/?envType=study-plan-v2&envId=top-sql-50 SELECT a.id, CASE WHEN a.id % 2 = 1 THEN b.student ELSE c.student END AS student FROM seat a LEFT JOIN seat b ON a.id + 1 = b.id LEFT JOIN seat c ON a.id = c.id + 1 id가 홀수인 경우와 짝수인 경우를 나눠서 문제를 해결하려고 했는데 table의 마지막 열에 대해서 어떻게 처리가 안되더라 .. 원래 table은 다음과 같다. SELECT CASE WHEN id = (SELECT MAX(id) FROM seat) AND id % ..

https://leetcode.com/problems/last-person-to-fit-in-the-bus/?envType=study-plan-v2&envId=top-sql-50 누적합을 어떻게 구해야하는지 몰라서 문제에 접근을 못했다! 1. JOIN 이용 SELECT * FROM Queue a JOIN Queue b ON a.turn >= b.turn ORDER BY a.turn, b.turn ex) a의 turn이 2 --> turn이 1, 2인 row가 JOIN 됨 2. a.turn으로 GROUP BY 후 해당 순서의 누적 weight를 구하기 위해 SUM(b.weight)를 계산 HAVING절에 1000 이하라는 조건을 걸어주고 누적 weight가 가장 큰 순서대로 정렬한다 이때 a.person_..

내가 처음에 짠 쿼리는 다음과 같다 ... SELECT category, COUNT(account_id) AS accounts_count FROM ( SELECT account_id, CASE WHEN income < 20000 THEN "Low Salary" WHEN income 50000) AS accounts_count) SELECT 문을 이용해서 table 형태를 만들어주는 방식도 요 문제에서 처음 접했다.

https://leetcode.com/problems/product-price-at-a-given-date/?envType=study-plan-v2&envId=top-sql-50 SELECT product_id, new_price AS price FROM products WHERE (product_id, change_date) IN ( SELECT product_id, MAX(change_date) as new_date FROM products WHERE change_date '2019-08-16' THEN 10 END AS price FROM products GROUP BY product_id HAVING MIN(change_date) > '2019-08-16') ORDER BY product_id 내..

https://leetcode.com/problems/primary-department-for-each-employee/?envType=study-plan-v2&envId=top-sql-50 -- department가 1개 (SELECT employee_id, department_id FROM employee WHERE employee_id IN ( SELECT employee_id FROM employee GROUP BY employee_id HAVING COUNT(DISTINCT department_id) = 1) ) UNION -- department가 2개 (SELECT employee_id, department_id FROM employee WHERE employee_id IN ( SELECT ..