오늘도 배운다

문제 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)

문제 source: HackerRank You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table. If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects co..

문제 source: HackerRank Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same..