본문 바로가기

leetcode15

[leetcode / MySQL] 1158. Market Analysis I 문제 💡 Write a solution to find for each user, the join date and the number of orders they made as a buyer in 2019. Return the result table in any order. 테이블 형태 풀이 SELECT user_id AS buyer_id, join_date, SUM(CASE WHEN YEAR(order_date)=2019 THEN 1 ELSE 0 END) AS orders_in_2019 FROM Users AS u LEFT JOIN Orders AS o ON u.user_id=o.buyer_id GROUP BY user_id 해설 각 유저별 가입 일자와 2019년에 buyer로서 주문한 order 데이터의.. 2024. 2. 27.
[leetcode / MySQL] 1070. Product Sales Analysis III 문제 💡 Write a solution to select the product id, year, quantity, and price for the first year of every product sold. Return the resulting table in any order. 테이블 형태 풀이 SELECT product_id, year AS first_year, quantity, price FROM Sales WHERE (product_id, year) IN (SELECT product_id, MIN(year) AS year FROM Sales GROUP BY product_id) 해설 SELECT product_id, year AS first_year, quantity, price FROM Sale.. 2024. 2. 27.
[leetcode / MySQL] 626. Exchange Seats 문제 💡 Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped. Return the result table ordered by id in ascending order. 테이블 형태 풀이 SELECT id , CASE WHEN id=(SELECT COUNT(*) FROM Seat) AND id%2=1 THEN student WHEN id%2=1 THEN LEAD(student, 1) OVER (ORDER BY id) WHEN id%2=0 THEN LAG(student, 1) OVER(ORDER BY.. 2024. 2. 22.
[leetcode / MySQL] 585. Investments in 2016 문제 💡 Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who: have the same tiv_2015 value as one or more other policyholders, and are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique). Round tiv_2016 to two decimal places. 테이블 형태 풀이 SELECT ROUND(SUM(tiv_2016),2) AS tiv_2016 FROM Insu.. 2024. 2. 22.
[leetcode / MySQL] 176. Second Highest Salary 문제 Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null (return None in Pandas). 테이블 형태 풀이 SELECT MAX(salary) AS SecondHighestSalary FROM EMPLOYEE WHERE salary (SELECT MAX(salary) FROM Employee) 해설 원래 윈도우 함수 중 집계함수(DENSE_RANK 등)을 사용해서 풀이하려고 했으나, 출력하고자 하는 값이 존재하지 않을 때 NULL을 반환하는 방법을 찾지 못해 다른 사람(bhumileetcode)의 풀이를 참고하였다. OFF.. 2024. 2. 19.
[leetcode / MySQL] 185. Department Top Three Salaries 문제 💡 A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department. Write a solution to find the employees who are high earners in each of the departments. Return the result table in any order. 테이블 형태 풀이 SELECT Department, Employee, S.. 2024. 2. 1.
[leetcode / MySQL] 178. Rank Scores 문제 💡 Write a solution to find the rank of the scores. The ranking should be calculated according to the following rules: The scores should be ranked from the highest to the lowest. If there is a tie between two scores, both should have the same ranking. After a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no holes between ranks. Retur.. 2024. 2. 1.
[leetcode / MySQL] 511. Game Play Analysis1 문제 💡 Write a solution to find the first login date for each player. Return the result table in any order. 테이블 형태 풀이 SELECT player_id, first_login FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS row_n , player_id , event_date AS first_login FROM Activity ) activity WHERE row_n=1 해설 FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS row_.. 2024. 2. 1.
[leetcode / MySQL] 180. Consecutive Numbers 문제 💡 Find all numbers that appear at least three times consecutively. Return the result table in any order. 테이블 형태 풀이 SELECT DISTINCT num0 AS ConsecutiveNums FROM (SELECT id , num AS num0 , LEAD(num, 1) OVER (ORDER BY id) AS num1 , LEAD(num, 2) OVER (ORDER BY id) AS num2 FROM Logs) logs WHERE num0=num1 AND num1=num2 해설 SELECT DISTINCT num0 AS ConsecutiveNums FROM (SELECT id , num AS num0 , LEAD(.. 2024. 1. 31.

티스토리툴바