https://school.programmers.co.kr/learn/courses/30/lessons/298519

정답

SELECT
    COUNT(FISH_INFO.FISH_TYPE) AS FISH_COUNT,
    MAX(LENGTH) AS MAX_LENGTH,
    FISH_INFO.FISH_TYPE
FROM
    FISH_INFO JOIN (
        SELECT 
            FISH_TYPE,
            SUM(IF(LENGTH IS NULL, 10, LENGTH)) / COUNT(FISH_TYPE) AS AVG_LENGTH
        FROM
            FISH_INFO
        GROUP BY
            FISH_TYPE
        HAVING
            AVG_LENGTH >= 33
    ) AS AVG_LENGTH_INFO ON FISH_INFO.FISH_TYPE = AVG_LENGTH_INFO.FISH_TYPE
GROUP BY
    FISH_INFO.FISH_TYPE
ORDER BY
    FISH_INFO.FISH_TYPE;

포인트

1. 타입별로 평균 길이가 33 이상인 물고기만 필터링하여 조인