오늘도 배운다

문제 source: LeetCode Write an SQL query to report the Capital gain/loss for each stock. The Capital gain/loss of a stock is the total gain or loss after buying and selling the stock one or many times. Return the result table in any order. 제출답안(MySQL) WITH ledger AS( SELECT stock_name, CASE WHEN operation = 'Buy' THEN price*(-1) ELSE price END AS balance FROM stocks) SELECT stock_name, SUM(balance..

문제 source: LeetCode Write an SQL query 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. 제출답안(MySQL) SELECT ROW_NUMBER() OVER (ORDER BY FLOOR((id+1)/2), MOD(id, 2)) AS id, student FROM seat 풀이(MySQL) 더보기 홀수, 짝수 id 한 쌍 씩 구분 / 쌍 내에서 짝수를 구분할 수 있는 가상의 칼럼 생성 FLOO..

문제 source: LeetCode Write an SQL query 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. 제출답안(MySQL) WITH buyer_2019 AS( SELECT buyer_id, COUNT(order_id) AS orders_in_2019 FROM orders WHERE YEAR(order_date) = 2019 GROUP BY buyer_id ) SELECT user_id AS buyer_id, join_date, CASE WHEN user_id IN (SELECT buyer_id FROM bu..

문제 source: LeetCode Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in any order. The query result format is in the following example. 제출답안(MySQL) WITH max_sal_by_dpt AS ( SELECT departmentid, MAX(salary) AS salary FROM employee GROUP BY departmentid ) SELECT d.name AS Department, e.name AS Employee, salary AS Salary FROM emplo..

문제 source: LeetCode Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null. The query result format is in the following example. 제출답안(MySQL) CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( SELECT DISTINCT(salary) FROM (SELECT id, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS ranking F..

문제 source: LeetCode 제출답안(MySQL) SELECT DISTINCT(l1.num) AS ConsecutiveNums FROM logs AS l1 JOIN logs AS l2 ON l1.id = l2.id+1 JOIN logs AS l3 ON l1.id = l3.id+2 WHERE l1.num = l2.num AND l1.num = l3.num 문제 바로가기(MySQL)

문제 source: LeetCode 제출답안(MySQL) SELECT score, DENSE_RANK() OVER(ORDER BY score DESC) AS 'rank' FROM scores 문제 바로가기(MySQL)