작심삼일

https://leetcode.com/problems/nth-highest-salary/

CASE WHEN 

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT CASE WHEN COUNT(*) < N THEN NULL
                  ELSE MIN(SUB.SALARY) END
      FROM (SELECT SALARY
            FROM EMPLOYEE
            ORDER BY SALARY DESC
            LIMIT N) SUB
  );
END

LIMIT

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
      SELECT DISTINCT SALARY
      FROM EMPLOYEE
      ORDER BY SALARY DESC
      LIMIT N, 1
  );
END

https://leetcode.com/problems/department-top-three-salaries/

Window Func

SELECT EMP_INFO.DEPT_NAME AS Department,
       EMP_INFO.EMP_NAME AS Employee, 
       SALARY AS Salary
FROM (SELECT E.NAME AS EMP_NAME, D.NAME AS DEPT_NAME, E.SALARY, 
      DENSE_RANK() OVER(PARTITION BY E.DEPARTMENTID ORDER BY SALARY DESC) AS SAL_RANK
      FROM EMPLOYEE E
      JOIN DEPARTMENT D ON E.DEPARTMENTID = D.ID) EMP_INFO
WHERE EMP_INFO.SAL_RANK <= 3
ORDER BY SALARY

https://leetcode.com/problems/consecutive-numbers/

Multiple Self JOIN

SELECT L.NUM AS ConsecutiveNums
FROM LOGS L
JOIN LOGS L2 ON L.ID + 1 = L2.ID
JOIN LOGS L3 ON L.ID + 2 = L3.ID
WHERE L.NUM = L2.NUM = L3.NUM

Using Window Function

SELECT DISTINCT N1 AS ConsecutiveNums
FROM (
SELECT NUM AS N1, LEAD(1, NUM) OVER(ORDER BY ID) AS N2, LEAD(2, NUM) OVER(ORDER BY ID) AS N3
FROM LOGS)
WHERE N1 = N2= N3

https://www.hackerrank.com/challenges/the-report/problem?h_r=internal-search&isFullScreen=true 

JOIN ON BETWEEN

join 절에 다음과 같이 between을 사용 할 수 있다.

SELECT IF(G.GRADE >= 8, S.NAME, NULL), G.GRADE, S.MARKS
FROM STUDENTS S
JOIN GRADES G ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK
ORDER BY G.GRADE DESC, S.NAME

https://www.hackerrank.com/challenges/challenges/problem?h_r=internal-search&isFullScreen=true 

Sub Query

SELECT H.HACKER_ID, MAX(H.NAME), COUNT(C.CHALLENGE_ID) AS NUM_OF_CHALLENGES
FROM HACKERS H
JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID
GROUP BY H.HACKER_ID
HAVING NUM_OF_CHALLENGES = (SELECT MAX(SUB.NUM_OF_CHALLENGES)
                            FROM (SELECT COUNT(CHALLENGE_ID) AS NUM_OF_CHALLENGES  
                                  FROM CHALLENGES
                                  GROUP BY HACKER_ID) SUB)
OR NUM_OF_CHALLENGES IN (SELECT SUB.NUM_OF_CHALLENGES
                         FROM (SELECT HACKER_ID, COUNT(*) AS NUM_OF_CHALLENGES
                               FROM CHALLENGES
                               GROUP BY HACKER_ID) SUB
                         GROUP BY SUB.NUM_OF_CHALLENGES
                         HAVING COUNT(*) = 1)
ORDER BY NUM_OF_CHALLENGES DESC, H.HACKER_ID

여러번 쓰이는 SUB QUERY는 WITH 문으로 미리 테이블을 정의하여 재사용하면

코드를 간략화 할 수 있다.

WITH CHALLENGES_COUNT AS(
SELECT COUNT(CHALLENGE_ID) AS NUM_OF_CHALLENGES  
FROM CHALLENGES
GROUP BY HACKER_ID)

SELECT H.HACKER_ID, MAX(H.NAME), COUNT(C.CHALLENGE_ID) AS NUM_OF_CHALLENGES
FROM HACKERS H
JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID
GROUP BY H.HACKER_ID
HAVING NUM_OF_CHALLENGES = (SELECT MAX(SUB.NUM_OF_CHALLENGES)
                            FROM CHALLENGES_COUNT SUB)
OR NUM_OF_CHALLENGES IN (SELECT SUB.NUM_OF_CHALLENGES
                         FROM CHALLENGES_COUNT SUB
                         GROUP BY SUB.NUM_OF_CHALLENGES
                         HAVING COUNT(*) = 1)
ORDER BY NUM_OF_CHALLENGES DESC, H.HACKER_ID

https://leetcode.com/problems/department-highest-salary/

FROM절 서브쿼리, JOIN

SELECT D.NAME AS DEPARTMENT,
        E.NAME AS EMPLOYEE,
        E.SALARY AS SALARY
FROM EMPLOYEE E
JOIN (SELECT DEPARTMENTID, MAX(SALARY) AS MX_SAL
FROM EMPLOYEE
GROUP BY DEPARTMENTID) SUB ON E.SALARY = SUB.MX_SAL 
                            AND E.DEPARTMENTID = SUB.DEPARTMENTID
JOIN DEPARTMENT D ON E.DEPARTMENTID = D.ID

Window Func

SELECT EMP_INFO.DEPT_NAME AS Department,
       EMP_INFO.EMP_NAME AS Employee,
       EMP_INFO.SALARY AS Salary
FROM (
    SELECT E.NAME AS EMP_NAME, 
           D.NAME AS DEPT_NAME, 
           E.SALARY,
           MAX(SALARY) OVER(PARTITION BY DEPARTMENTID) AS MAX_SAL
    FROM EMPLOYEE E
    JOIN DEPARTMENT D ON E.DEPARTMENTID = D.ID) EMP_INFO
WHERE EMP_INFO.SALARY = EMP_INFO.MAX_SAL