홍동이의 성장일기

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

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 ..

안녕하세요 👋 오늘은 해커랭크 SQL Certificate 취득 후기에 대해 이야기해보겠습니다. 취득하게 된 계기 데이터리안 SQL 실전반의 마지막 선택 미션으로 해커랭크 SQL (Intermediate) Certificate 취득이 있었습니다. 한달 동안 학습한 SQL 지식을 검증해보고 싶어서 테스트에 응시하게 되었습니다. 취득 방법 해커랭크 페이지 접속 → 메뉴 → Certify 화면을 밑으로 끝까지 내리면 SQL 자격을 취득하는 칸이 나옵니다. 저는 Intermediate 단계를 취득했기 때문에 화면에 나오지 않습니다. 원하는 난이도를 선택한 후 시험에 응시합니다. 문제 유형 문제 수: 2문제 제한시간: 35분 한문제에 4~5개의 테이블이 주어졌습니다. ⭐ 한 번 시험에 응시하면 30일 후에 다시 ..

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 ) ..