멸종위기의 대장균 찾기

문제

코드

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, GENERATION
FROM ANCESTOR
WHERE ID NOT IN (SELECT PARENT_ID FROM ANCESTOR WHERE PARENT_ID IS NOT NULL)
GROUP BY GENERATION
ORDER BY GENERATION

 

출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/courses/30/lessons/301651