응엉이의 엉엉코딩일기

WITH temp AS ( SELECT id, SUM(cnt) AS num FROM ( (SELECT requester_id AS id, COUNT(accepter_id) AS cnt FROM RequestAccepted GROUP BY requester_id) UNION ALL (SELECT accepter_id AS id, COUNT(requester_id) AS cnt FROM RequestAccepted GROUP BY accepter_id) ) a GROUP BY id ) SELECT id, num FROM temp WHERE num = (SELECT MAX(num) FROM temp) 1. UNION ALL을 이용해서 requester_id와 accepter_id를 모두 COUNT 해준 후 중..

https://leetcode.com/problems/restaurant-growth/?envType=study-plan-v2&envId=top-sql-50 날짜별 이동합과 이동평균을 구하는게 어려웟던 문제 1. 날짜를 만들어주고 2. 이동합을 구해주고 3. 이동평균을 구해주는 식으로 문제를 풀어야 한다 1. 날짜 만들어주기 SELECT visited_on FROM customer c WHERE visited_on >= (SELECT DATE_ADD(MIN(visited_on),INTERVAL 6 DAY) FROM customer) GROUP BY visited_on WHERE절 서브쿼리에 DATE_ADD(날짜, INTERVAL n DAY) 함수를 사용해서 테이블 내의 최소일자 + 6일 이상 인 날짜들에..

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/movie-rating/submissions/1213326562/?envType=study-plan-v2&envId=top-sql-50 맨 처음 작성한 쿼리는 다음과 같다. -- 가장 많은 리뷰 남긴 사람 (SELECT name AS results FROM ( SELECT u.name, COUNT(r.movie_id) AS cnt FROM MovieRating r JOIN Users u ON r.user_id = u.user_id GROUP BY u.name ORDER BY cnt DESC, u.name ) a LIMIT 1) UNION -- Feb, 2020에 평균 평점 가장 높은 영화 (SELECT title AS results FROM ( SEL..

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 ..

https://leetcode.com/problems/game-play-analysis-iv/?envType=study-plan-v2&envId=top-sql-50 Game Play Analysis IV - LeetCode Can you solve this real interview question? Game Play Analysis IV - Table: Activity +--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------- leetcode.com 전체 ..