문제코드WITH RECURSIVE ANCESTOR AS ( SELECT ID, PARENT_ID, 1 AS GENERATION FROM ECOLI_DATA WHERE PARENT_ID IS NULL UNION SELECT C.ID, C.PARENT_ID, (A.GENERATION + 1) AS GENERATION FROM ECOLI_DATA C, ANCESTOR A WHERE C.PARENT_ID = A.ID)SELECT COUNT(*) AS COUNT, GENERATIONFROM ANCESTORWHERE ID NOT IN (SELECT PARENT_ID FROM ANCESTOR WHERE PARENT_ID IS NOT NULL)GROUP BY GENERATIONOR..

문제코드SELECT YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH, COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS, ROUND((COUNT(DISTINCT O.USER_ID)/ (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021)),1) AS PURCHASED_RATIOFROM ONLINE_SALE OWHERE O.USER_ID IN (SELECT USER_ID FROM USER_INFO WHERE YEAR(JOINED) = 2021)GROUP BY YEAR, MONTHORDER BY YEAR, MONTH; 출처: 프로그래머스 코딩 테스..

문제코드WITH CODE_SUM AS ( SELECT (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End') AS FRONT_END, (SELECT SUM(CODE) FROM SKILLCODES WHERE NAME = 'Python') AS PYTHON, (SELECT SUM(CODE) FROM SKILLCODES WHERE NAME = 'C#') AS CSHARP),CTE AS ( SELECT CASE WHEN SKILL_CODE & c.FRONT_END > 0 AND SKILL_CODE & c.PYTHON > 0 THEN 'A' WHEN SKI..

문제코드WITH CAR AS (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID HAVING MAX(END_DATE) 출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/courses/30/lessons/157339

문제코드SELECT ID, EMAIL, FIRST_NAME, LAST_NAMEFROM DEVELOPERSWHERE SKILL_CODE & ( SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End' ) > 0ORDER BY ID ASC; 출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/courses/30/lessons/276035

문제코드WITH DURATION AS(SELECT HISTORY_ID, CAR_ID, (DATEDIFF(END_DATE, START_DATE)+1)AS DIFFDAY, (CASE WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 90 THEN '90일 이상' WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 THEN '30일 이상' WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 7 THEN '7일 이상' ELSE '할인 없음' END) AS DURATION_TYP..

문제코드SELECT IDFROM ECOLI_DATAWHERE PARENT_ID IN(SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL))ORDER BY ID 출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/courses/30/lessons/301650

문제코드SELECT HOUR, COUNT(ANIMAL_ID) AS COUNTFROM ( SELECT (ROW_NUMBER() OVER () - 1) AS HOUR FROM ANIMAL_OUTS LIMIT 24) AS 24HOURS_NUM_TABLE LEFT JOIN ANIMAL_OUTS ON HOUR = HOUR(DATETIME)GROUP BY HOURORDER BY HOUR 출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/courses/30/lessons/59413