홍동이의 성장일기

Market Analysis I - LeetCode Can you solve this real interview question? Market Analysis I - Table: Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ u leetcode.com SELECT u.user_id as buyer_id , u.join_date , IFNULL(COUNT(o.order_date), 0) AS orders_in_2019 FROM ..

Investments in 2016 - LeetCode Can you solve this real interview question? Investments in 2016 - Table: Insurance +-------------+-------+ | Column Name | Type | +-------------+-------+ | pid | int | | tiv_2015 | float | | tiv_2016 | float | | lat | float | | lon | float | +------------- leetcode.com WITH cte_2015 AS ( -- tiv_2015가 동일한 고객이 두 명 이상이어야함 SELECT tiv_2015 , count(*) as tot FROM insuran..

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