[SQL문제풀기] 언어별 개발자 구하기

문제

MYSQL

방법

내가 작성한 정답 1

WITH A AS ((SELECT D.ID, D.EMAIL
            FROM DEVELOPERS D
            JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0 
            WHERE S.NAME = 'Python'
            GROUP BY D.ID, D.EMAIL)
             INTERSECT
            (SELECT D.ID, D.EMAIL
            FROM DEVELOPERS D
            JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0 
            WHERE S.CATEGORY = 'Front End'
            GROUP BY D.ID, D.EMAIL)),
    B AS (SELECT D.ID, D.EMAIL
            FROM DEVELOPERS D
            JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0 
            WHERE S.NAME = 'C#' ),
    C AS (SELECT D.ID, D.EMAIL
         FROM DEVELOPERS D
         JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0
         WHERE (D.ID, D.EMAIL) NOT IN (SELECT * FROM A)
            AND (D.ID, D.EMAIL) NOT IN (SELECT * FROM B)
            AND S.CATEGORY = 'Front End'),
    D AS (SELECT CASE 
                    WHEN (ID, EMAIL) IN (SELECT * FROM A) THEN 'A'
                    WHEN (ID, EMAIL) IN (SELECT * FROM B) THEN 'B'
                    WHEN (ID, EMAIL) IN (SELECT * FROM C) THEN 'C'
                                        END AS GRADE, 
          ID, EMAIL
            FROM DEVELOPERS            
            )
SELECT * FROM D
WHERE GRADE IS NOT NULL
ORDER BY GRADE ASC, ID ASC

방법2

내가 작성한 오답

WITH A AS (SELECT D.ID, D.EMAIL, A.CATEGORY, A.NAME
           FROM DEVELOPERS D
            JOIN SKILLCODES A ON D.SKILL_CODE & A.CODE != 0)
, TB AS (
    SELECT 
    CASE
        WHEN F.ID = P.ID THEN 'A'
        WHEN D.ID = C.ID THEN 'B'
        WHEN F.ID NOT IN (P.ID,C.ID) THEN 'C' END AS GRADE,
        D.ID, D.EMAIL
        FROM DEVELOPERS D
        JOIN A P ON P.NAME = 'Python'
        JOIN A C ON C.NAME = 'C#'
        JOIN A F ON F.CATEGORY = 'Front End'
        )
SELECT GRADE, ID, EMAIL
FROM TB
GROUP BY GRADE,ID,EMAIL
ORDER BY GRADE ASC, ID ASC

WITH A AS (SELECT D.ID, D.EMAIL, A.CATEGORY, A.NAME
           FROM DEVELOPERS D
            JOIN SKILLCODES A ON D.SKILL_CODE & A.CODE != 0)
SELECT P.ID ,P.EMAIL, P.NAME
FROM DEVELOPERS D
JOIN A P ON P.NAME = 'Python'
JOIN A C ON C.NAME = 'C#'
JOIN A F ON F.CATEGORY = 'Front End'

내가 작성한 오답 2

WITH SKILL AS (SELECT * 
           FROM DEVELOPERS D
            JOIN SKILLCODES A ON D.SKILL_CODE & A.CODE != 0)
, TB AS (
    SELECT 
    CASE
        WHEN D.SKILL_CODE & P.CODE != 0 THEN 'A'
        WHEN D.SKILL_CODE & C.CODE != 0 THEN 'B'
        WHEN D.SKILL_CODE & F.CODE != 0 AND F.ID NOT IN (P.ID,C.ID) THEN 'C' END AS GRADE,
        D.ID, D.EMAIL
        FROM DEVELOPERS D
        JOIN SKILL P ON P.NAME = 'Python'
        JOIN SKILL C ON C.NAME = 'C#'
        JOIN SKILL F ON F.CATEGORY = 'Front End'
        )
SELECT GRADE, ID, EMAIL
FROM TB
WHERE GRADE IS NOT NULL
GROUP BY GRADE,ID,EMAIL
ORDER BY GRADE ASC, ID ASC

내가 작성한 정답 2

시간 초과의 원인이 될 수 있는 것들

1. 많은 join문

2. not in 에서 연산할 것이 많으면 시간이 오래 걸릴 수 있다.

 

WITH TB AS (
    SELECT 
        D.ID,
        D.EMAIL,
        CASE
            WHEN P.NAME IS NOT NULL AND F.NAME IS NOT NULL THEN 'A' 
            WHEN C.NAME IS NOT NULL THEN 'B' 
            WHEN F.NAME IS NOT NULL THEN 'C' 
        END AS GRADE
    FROM DEVELOPERS D
    LEFT JOIN SKILLCODES P ON P.NAME = 'Python' AND D.SKILL_CODE & P.CODE != 0
    LEFT JOIN SKILLCODES C ON C.NAME = 'C#' AND D.SKILL_CODE & C.CODE != 0
    LEFT JOIN SKILLCODES F ON F.CATEGORY = 'Front End' AND D.SKILL_CODE & F.CODE != 0
)
SELECT GRADE, ID, EMAIL
FROM TB
WHERE GRADE IS NOT NULL
GROUP BY GRADE, ID, EMAIL
ORDER BY GRADE ASC, ID ASC

• 성능향상을 위해 cte쿼리 하나를 삭제하고 join문에 조건을 추가했다.

• join문 작성 시 left join을 사용해 조건에 맞지 않는 record들도 결과값에 넣고 나중에 걸러낸다.

• case 조건문에 join해서 온 row들에서 각각의 조건들이 없다면 제외시키기 위해 NOT NULL을 조건으로 걸어줬다. 이 때 P.NAME, C.NAME, F.NAME들은 가져온 조건들에 만족하기 못하는 경우를 걸러내기 위한 것이기 때문에 NAME은 SKILLCODES에 존재하는 CATEGORY나 CODE로 변경이 가능하다.

[SQL문제풀기] FrontEnd 개발자 찾기 - silver

SQL문제풀기

https://inblog.ai/silver/sql%EB%AC%B8%EC%A0%9C%ED%92%80%EA%B8%B0-frontend-%EA%B0%9C%EB%B0%9C%EC%9E%90-%EC%B0%BE%EA%B8%B0-39041?traffic_type=internal

다른 사람들이 작성한 정답

SELECT
        CASE 
        WHEN GROUP_CONCAT(B.NAME) LIKE ("%Python%") AND GROUP_CONCAT(B.CATEGORY) LIKE("%Front%") THEN "A"
        WHEN GROUP_CONCAT(B.NAME) LIKE ("%C#%") THEN "B"
        WHEN GROUP_CONCAT(B.CATEGORY) LIKE ("%Front%") THEN "C"
        END AS  GRADE ,
        A.ID,
        A.EMAIL

FROM DEVELOPERS  AS A
JOIN SKILLCODES AS B
ON (A.SKILL_CODE & B.CODE = B.CODE)
GROUP BY ID,EMAIL 
HAVING GRADE IS NOT NULL
ORDER BY GRADE ASC , ID ASC