홍동이의 성장일기

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; 💡 문제풀이 ➡️ 문제 조건에 두 개의 개별 쿼리를 작성하여 원하는 결과를 출력해도 된다는 조건이 있어서 이름이..

Occupations | HackerRank Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. www.hackerrank.com 💡 문제풀이 1. 직업별로(PARTITION BY occupation) 이름을 알파벳순으로(ORDER BY name) 순위를 매겨줍니다. SELECT occupation , name , ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) as rn FROM occupations 2. 피벗을 위해 CASE WHEN 구문을 사용합니다. SELECT rn , CAS..

New Companies | HackerRank Find total number of employees. www.hackerrank.com SELECT c.company_code , c.founder , (SELECT COUNT(DISTINCT lead_manager_code) FROM lead_manager WHERE company_code = c.company_code) , (SELECT COUNT(DISTINCT senior_manager_code) FROM senior_manager WHERE company_code = c.company_code) , (SELECT COUNT(DISTINCT manager_code) FROM manager WHERE company_code = c.company_c..

Contest Leaderboard | HackerRank Generate the contest leaderboard. www.hackerrank.com SELECT h.hacker_id , h.name , SUM(score_max) total_score FROM( SELECT hacker_id , challenge_id , MAX(score) score_max FROM submissions GROUP BY 1,2 ) s INNER JOIN hackers h ON s.hacker_id = h.hacker_id GROUP BY 1,2 HAVING total_score 0 ORDER BY 3 DESC, 1 💡 문제풀이 1. hacker_id와 challenge_id별로 가장 높은 score을 구한다. SEL..

Weather Observation Station 17 | HackerRank Query the Western Longitude for the smallest value of the Northern Latitudes greater than 38.7780 in STATION and round to 4 decimal places. www.hackerrank.com ➡️ 내가 푼 풀이 select round(long_w, 4) from station where lat_n = (select min(lat_n) from station where lat_n > 38.7780) ➡️ 간단한 풀이 select round(long_w, 4) from station where lat_n > 38.7780 order by ..

Restaurant Growth - LeetCode Can you solve this real interview question? Restaurant Growth - Table: Customer +---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | | visited_on | date | | amount | int | +---------------+ leetcode.com WITH cte_sum AS( SELECT visited_on , SUM(amount) as sum_amount FROM customer GROUP BY visited_on ) ..

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 점수의 순위를 찾기 위한 해결책을 작성합니다. 순위는 다음 규칙에 따라 계산되어야 합니다: 두 점수 사이에 동점이 있으면 두 점수 모두 같은 순위를 가져야 합니다. ➡️ RANK / DENS..

Department Top Three Salaries - LeetCode Can you solve this real interview question? Department Top Three Salaries - Table: Employee +--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +-------------- leetcode.com SELECT Department , Employee , Salary FROM( SELECT d.name as Department , e.name as Emp..

Game Play Analysis I - LeetCode Can you solve this real interview question? Game Play Analysis I - Table: Activity +--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +---------- leetcode.com [풀이 1] SELECT player_id , MIN(event_date) AS first_login FROM activity GROUP BY player_id [풀이 2]..