목록Tool/SQL 코딩테스트 풀이 (44)
홍동이의 성장일기
Nth Highest Salary - LeetCode Can you solve this real interview question? Nth Highest Salary - Table: Employee +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id is the primary key (column with unique values) for leetcode.com CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN SET N = N-1; RETURN ( SELECT DISTINCT..
Second Highest Salary - LeetCode Can you solve this real interview question? Second Highest Salary - Table: Employee +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ id is the primary key (column with unique values) leetcode.com SELECT MAX(salary) as SecondHighestSalary FROM Employee WHERE salary NOT IN (SELECT MAX(salary) ..
Exchange Seats - LeetCode Can you solve this real interview question? Exchange Seats - Table: Seat +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | student | varchar | +-------------+---------+ id is the primary key (unique value) column for leetcode.com 연속하는 두 학생마다 자리 ID를 바꿀 수 있는 해를 작성합니다. 학생 수가 홀수이면 마지막 학생의 ID를 교환하지 않습니다. id 순으로 정렬된 결과 테이블을 오름차순으로 반환합니다..
Rank Scores - LeetCode Can you solve this real interview question? Rank Scores - Table: Scores +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | score | decimal | +-------------+---------+ id is the primary key (column with unique values) leetcode.com SELECT score , DENSE_RANK() OVER(ORDER BY score DESC) as "rank" FROM scores ORDER BY score DESC 💡 문제 풀이 My..
Weather Observation Station 20 | HackerRank Query the median of Northern Latitudes in STATION and round to 4 decimal places. www.hackerrank.com 풀이 1 SELECT ROUND(AVG(lat_n), 4) FROM ( SELECT lat_n , PERCENT_RANK() OVER (ORDER BY lat_n) as p_rn FROM station ) as t WHERE p_rn = 0.5 풀이 2 SELECT ROUND(AVG(lat_n), 4) FROM ( SELECT ROW_NUMBER() OVER (ORDER BY lat_n) as row_num , COUNT(*) OVER () as n ..
Weather Observation Station 11 | HackerRank Query a list of CITY names not starting or ending with vowels. www.hackerrank.com 풀이 1 SELECT DISTINCT city FROM station WHERE city REGEXP '^[^aeiou]' OR city REGEXP '[^aeiou]$' 풀이 2 SELECT DISTINCT city FROM station WHERE city NOT REGEXP '^[aeiou]' OR city NOT REGEXP '[aeiou]$' 💡 문제풀이 모음으로 시작하지 않는 city city REGEXP '^[^aeiou]' city NOT REGEXP '^[aeiou]..
Ollivander's Inventory | HackerRank Help pick out Ron's new wand. www.hackerrank.com ※ 윈도우 함수를 사용할 때는 DB를 MS SQL Server로 변경해주어야 합니다. SELECT id , age , coins_needed , power FROM ( SELECT w.code , w.id , wp.age , w.coins_needed , w.power , ROW_NUMBER() OVER(PARTITION BY wp.age, w.power ORDER BY w.coins_needed) as num FROM wands AS w LEFT JOIN wands_property AS wp ON w.code = wp.code WHERE is_evil ..
SQL Project Planning | HackerRank Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. www.hackerrank.com SELECT start_date, end_date FROM ( SELECT ROW_NUMBER() OVER (ORDER BY start_date) as num , start_date FROM projects WHERE start_date NOT IN (SELECT end_date FROM projects) ) s INNER JOIN ( SELECT ROW_NUMB..
Binary Tree Nodes | HackerRank Write a query to find the node type of BST ordered by the value of the node. www.hackerrank.com 풀이 1 SELECT N , CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT P FROM BST GROUP BY P HAVING count(P) = 2) THEN 'Inner' ELSE 'Leaf' END FROM BST ORDER BY N 풀이 2 SELECT N , CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT DISTINCT P FROM BST) THEN 'Inner' ELSE 'Leaf' E..
Weather Observation Station 5 | HackerRank Write a query to print the shortest and longest length city name along with the length of the city names. www.hackerrank.com -- 글자수가 가장 긴 city SELECT city , length(city) FROM station ORDER BY 2 DESC, 1 LIMIT 1; -- 글자수가 가장 짧은 city SELECT city , length(city) FROM station ORDER BY 2, 1 LIMIT 1; 💡 문제풀이 ➡️ 문제 조건에 두 개의 개별 쿼리를 작성하여 원하는 결과를 출력해도 된다는 조건이 있어서 이름이..