응엉이의 엉엉코딩일기

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

https://leetcode.com/problems/immediate-food-delivery-ii/?envType=study-plan-v2&envId=top-sql-50 Immediate Food Delivery II - LeetCode Can you solve this real interview question? Immediate Food Delivery II - Table: Delivery +-----------------------------+---------+ | Column Name | Type | +-----------------------------+---------+ | delivery_id | int | | customer_id | int | | order_date | d leetco..

https://leetcode.com/problems/average-selling-price/submissions/1194313569/?envType=study-plan-v2&envId=top-sql-50 Average Selling Price - LeetCode Can you solve this real interview question? Average Selling Price - Table: Prices +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | start_date | date | | end_date | date | | price | int | +---------..