응엉이의 엉엉코딩일기

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=falseoccupation별로 column이 있는 형태 --> CASE문을 이용해서 기본 골자를 만드는 문제이지 않을까 했다SELECT CASE WHEN occupation = 'Doctor' THEN name END AS Doctor, CASE WHEN occupation = 'Professor' THEN name END AS Professor, CASE WHEN occupation = 'Singer' THEN name END AS Singer, CASE WHEN occupation = 'Actor' THEN name END AS ActorFROM occupa..

https://www.hackerrank.com/challenges/binary-search-tree-1/problem?isFullScreen=true Binary Tree Nodes | HackerRankWrite a query to find the node type of BST ordered by the value of the node.www.hackerrank.com 어려웠던 문제 ...n은 있는데 p가 null이면 rootn이긴 한데 p이진 않다면 leaf (애 역할은 하는데 부모 역할은 안하는 경우)나머지는 inner이렇게 구분해보았다 애 역할은 하는데 부모 역할은 하지 않는 경우를 찾기 위해서는 JOIN이 필요했다.여기서 JOIN 조건을 적어주는게 엄청 헷갈렸다.SELECT *FROM bst ..

SQRT()https://www.hackerrank.com/challenges/weather-observation-station-19/problem?isFullScreen=true Weather Observation Station 19 | HackerRankQuery the Euclidean Distance between two points and round to 4 decimal digits.www.hackerrank.com제곱이랑 제곱근 함수만 알면 완전 쉽게 풀 수 있음 제곱 : POWERPOWER(, ) 제곱근 : SQRTSQRT() root((a-b)^2 + (c-d)^2) 이게 유클리드 거리인데a는 MIN(lat_n)b는 MAX(lat_n)c는 MIN(long_w)d는 MAX(long_w)각 ..

https://www.hackerrank.com/challenges/weather-observation-station-20/problem?isFullScreen=true Weather Observation Station 20 | HackerRankQuery the median of Northern Latitudes in STATION and round to 4 decimal places.www.hackerrank.com쉽게 풀릴줄 알았는데 못풀었다 ...중앙값을 구현하는 문제https://chaemi720.tistory.com/350 [해커랭크] Weather Observation Station 20 - MySQLhttps://www.hackerrank.com/challenges/weather-obser..

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=falseSELECT start_date, end_dateFROM (SELECT rule, MIN(start_date) AS start_date, MAX(end_date) AS end_date, MAX(end_date) - MIN(end_date) AS days FROM (SELECT start_date, end_date, start_date - ROW_NUMBER() OVER (ORDER BY start_date) AS rule FROM projects) a GROUP BY rule) ..

https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=true Top Competitors | HackerRankQuery a list of top-scoring hackers.www.hackerrank.com1개 이상의 challange에서 최고득점을 한 해커의 hacker_id와 name을 출력하는 문제SELECT s.hacker_id, h.nameFROM submissions s INNER JOIN challenges c ON s.challenge_id = c.challenge_id -- 문제 난이도를 table에 추가하기 위해 JOIN INNER JOIN difficulty d ON c.difficulty_le..

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true Contest Leaderboard | HackerRankGenerate the contest leaderboard.www.hackerrank.com각 hacker의 total_score는 challange 점수 중 최고점들을 합한 것hacker_id, name, total_score를 출력total_score DESC, hacker_id 로 정렬total_score가 0인 경우는 제외 1. 각 hacker의 challage별 최고점을 구하는 쿼리 작성SELECT challenge_id, hacker_id, MAX(score) AS scoreFROM sub..

https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true Interviews | HackerRankfind total number of view, total number of unique views, total number of submissions and total number of accepted submissions.www.hackerrank.comtable 사이의 관계를 잘 파악하는게 엄청 중요했던 문제table이 5개씩이나 돼서 관계 파악이 어려웠다+ JOIN 할 때 JOIN 기준이 되는 key 값이 해당 table에 여러개인지 아닌지 확인해야 중복이 일어나지 않는다는걸 배움! SELECT con.contest_id, ..

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true The Report | HackerRankWrite a query to generate a report containing three columns: Name, Grade and Mark.www.hackerrank.com1. 기본 골자 만들어주기WITH sub AS ( SELECT s.name, g.grade, s.marks FROM students s INNER JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark) -- s.marks가 min_mark와 m..

https://www.hackerrank.com/challenges/challenges/problem?isFullScreen=true Challenges | HackerRankPrint the total number of challenges created by hackers.www.hackerrank.com강의를 듣기 전 내 풀이 1. 아이디별로 만든 챌린지 수를 확인할 수 있는 테이블을 만들어줌SELECT hacker_id, COUNT(challenge_id) AS challenges_createdFROM ChallengesGROUP BY hacker_id 이 테이블을 이용해서 추후 조건에 사용될 challenges_created의 MAX 값을 구해주는 서브쿼리를 또 작성해야 했기에, WITH문을 사용..