WITH RECURSIVE GEN
AS (
    SELECT ID, PARENT_ID, 1 AS DEPTH
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT CHILD.ID, CHILD.PARENT_ID, GEN.DEPTH + 1
    FROM ECOLI_DATA CHILD
    JOIN GEN
    ON CHILD.PARENT_ID = GEN.ID
),

CHILD
AS (
    SELECT A.ID, COUNT(B.ID) NUM FROM ECOLI_DATA A
    LEFT JOIN ECOLI_DATA B
    ON A.ID=B.PARENT_ID
    GROUP BY A.ID
)

SELECT COUNT(*) COUNT, A.DEPTH GENERATION FROM GEN A
JOIN (SELECT * FROM CHILD WHERE NUM=0) B USING (ID)
GROUP BY A.DEPTH
ORDER BY 2