My Dev Blog

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

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

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. 아래 쿼리를 이해하자

SELECT COUNT(DISTINCT HACKER_ID)  
  FROM SUBMISSIONS S2  
 WHERE S2.SUBMISSION_DATE = '2016-03-03'
   AND (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE) 
          FROM SUBMISSIONS S3 
         WHERE S3.HACKER_ID = S2.HACKER_ID 
           AND S3.SUBMISSION_DATE < '2016-03-03') = DATEDIFF('2016-03-03', '2016-03-01')

STEP 3. 두 개의 쿼리문을 합치면

SELECT MST.SUBMISSION_DATE
     , (SELECT COUNT(DISTINCT HACKER_ID)  
          FROM SUBMISSIONS S2  
         WHERE S2.SUBMISSION_DATE = MST.SUBMISSION_DATE
           AND (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE) 
                  FROM SUBMISSIONS S3 
                 WHERE S3.HACKER_ID = S2.HACKER_ID 
                   AND S3.SUBMISSION_DATE < MST.SUBMISSION_DATE) = DATEDIFF(MST.SUBMISSION_DATE, '2016-03-01')) AS CNT
     , (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

해커랭크 15 Days of Learning SQL

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 VARIABLES LIKE 'SQL_MODE'

SQL_MODE = ONLY_FULL_GROUP_BY 확인

(GROUP BY절에 포함되지 않은 컬럼을 집합함수로 감싸야만 사용할 수 있게 해줌)

해커랭크 SQL Project Planning SQL

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.RESULT) > 1
 ORDER BY COUNT(T.RESULT) DESC, T.HACKER_ID

RESULT 값의 집계함수를 조건으로 사용할 것이므로

WHERE절이 아닌 HAVING절에 쓴다.

HAVING절은 GROUP BY와 ORDER BY 사이에 위치한다.

해커랭크 Top Competitors SQL

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.MAX_MARK
   AND 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로 조회되기 때문에
이 경우, 같은 등급 내에서 ORDER BY NAME에 영향을 받지 않게 된다.
결과적으로, GRADE가 8 이상인 것들은 같은 등급 내에서 '이름'을 기준으로 오름차순 정렬이,
GRADE가 8 미만인 것들은 같은 등급 내에서  '점수'를 기준으로 오름차순 정렬이 된다.

해커랭크 The Report SQL

SELECT MAX(CASE WHEN A.OCCUPATION = 'Doctor' THEN NAME END) AS 'Doctor'
     , MAX(CASE WHEN A.OCCUPATION = 'Professor' THEN NAME END) AS 'Professor'
     , MAX(CASE WHEN A.OCCUPATION = 'Singer' THEN NAME END) AS 'Singer'
     , MAX(CASE WHEN A.OCCUPATION = 'Actor' THEN NAME END) AS 'Actor'
  FROM (
         SELECT NAME
              , OCCUPATION
              , ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) AS RN
           FROM OCCUPATIONS
       ) A
GROUP BY A.RN

해커랭크 Occupations SQL

SELECT CONCAT(NAME, '(', SUBSTR(OCCUPATION, 1, 1), ')')
  FROM OCCUPATIONS
 ORDER BY NAME;

SELECT CONCAT('There are a total of ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION), 's.') 
  FROM OCCUPATIONS
 GROUP BY OCCUPATION
 ORDER BY COUNT(OCCUPATION), OCCUPATION

해커랭크 The PADS SQL