SQL/해커랭크11 [SQL] 해커랭크 Binary Tree Nodes SELECT G.NODE FROM ( SELECT CONCAT(N, ' Root') AS NODE , N FROM BST WHERE P IS NULL UNION ALL SELECT CONCAT(N, ' Inner') AS NODE , N FROM BST WHERE N IN ( SELECT P FROM BST ) AND P IS NOT NULL UNION ALL SELECT CONCAT(N, ' Leaf') AS NODE , N FROM BST WHERE N NOT IN ( SELECT DISTINCT P FROM BST WHERE P IS NOT NULL ) ) G ORDER BY G.N 해커랭크 Binary Tree Nodes SQL 2022. 12. 3. [SQL] 해커랭크 Ollivander's Inventory SELECT C.ID, B.AGE, A.MIN_COINS_NEEDED, A.POWER FROM (SELECT CODE, MIN(COINS_NEEDED) AS MIN_COINS_NEEDED, POWER FROM WANDS GROUP BY CODE, POWER) A , WANDS_PROPERTY B , WANDS C WHERE A.CODE = B.CODE AND A.CODE = C.CODE AND A.MIN_COINS_NEEDED = C.COINS_NEEDED AND A.POWER = C.POWER AND B.IS_EVIL = 0 ORDER BY A.POWER DESC, B.AGE DESC 해커랭크 Ollivander's Inventory SQL 2022. 12. 3. [SQL] 해커랭크 15 Days of Learning SQL STEP 1. SUBMISSION_DATE와 ID, NM의 출력은 간단하다. SELECT MST.SUBMISSION_DATE , (SELECT HACKER_ID FROM SUBMISSIONS WHERE SUBMISSION_DATE = MST.SUBMISSION_DATE GROUP BY HACKER_ID ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS ID , (SELECT NAME FROM HACKERS WHERE HACKER_ID = ID) AS NM FROM (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) MST GROUP BY MST.SUBMISSION_DATE STEP 2. 아래 쿼리를 이해하자 SE.. 2022. 12. 3. [SQL] 해커랭크 SQL Project Planning SET SQL_MODE = ''; SELECT A.START_DATE, B.END_DATE FROM (SELECT START_DATE FROM PROJECTS WHERE START_DATE NOT IN (SELECT END_DATE FROM PROJECTS)) A, (SELECT END_DATE FROM PROJECTS WHERE END_DATE NOT IN (SELECT START_DATE FROM PROJECTS)) B WHERE A.START_DATE < B.END_DATE GROUP BY A.START_DATE ORDER BY DATEDIFF(B.END_DATE, A.START_DATE), A.START_DATE SQL_MODE 초기화 & 조회 SQL_MODE를 확인해보자 SHOW VARIABLE.. 2022. 12. 3. [SQL] 해커랭크 Top Competitors SELECT T.HACKER_ID , T.NAME FROM (SELECT A.HACKER_ID , D.NAME , (CASE WHEN A.SCORE = C.SCORE THEN 'PASS' ELSE 'FAIL' END) AS RESULT FROM SUBMISSIONS A, CHALLENGES B, DIFFICULTY C, HACKERS D WHERE A.CHALLENGE_ID = B.CHALLENGE_ID AND A.HACKER_ID = D.HACKER_ID AND B.DIFFICULTY_LEVEL = C.DIFFICULTY_LEVEL ORDER BY A.HACKER_ID) T WHERE T.RESULT = 'PASS' GROUP BY T.HACKER_ID, T.NAME HAVING COUNT(T.RESU.. 2022. 12. 3. [SQL] 해커랭크 The Report SELECT (CASE WHEN B.GRADE >= 8 THEN A.NAME ELSE NULL END) AS NAME , B.GRADE , A.MARKS FROM STUDENTS A, GRADES B WHERE A.MARKS = B.MIN_MARK ORDER BY B.GRADE DESC, NAME, A.MARKS 모든 데이터는 등급을 기준으로 내림차순 정렬되어야 하며, GRADE가 8 이상인 것들 '이름'을 기준으로 오름차순 정렬, GRADE가 8 미만인 것들은 '점수'를 기준으로 오름차순 정렬되어야 한다. (CASE WHEN B.GRADE >= 8 THEN A.NAME ELSE NULL END) AS NAME에서 GRADE가 8 미만인 것들은 NAME이 NULL로 조회되기 때문에 이 경우, 같은 등급 .. 2022. 12. 3. 이전 1 2 다음
