WITH SUB_ZENKOKU AS (
    SELECT
        AGE,
        GENDER_CODE,
        CATEGORY_CODE,
        ROUND(AVERAGE_VALUE,1) AS ZENKOKU_AVG
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR=2019
    GROUP BY
        AGE,GENDER_CODE,CATEGORY_CODE
    ),
    
SUB_HEIGHTS AS (
    SELECT
        SH.AGE,
        SH.GENDER_CODE,
        SH.AVERAGE_VALUE AS H_AVG,
        ZENKOKU_AVG,
        COUNT(*) 
            OVER(PARTITION BY SH.AGE,SH.GENDER_CODE) AS ROWS_CNT,
        COUNT(*) 
            FILTER(WHERE SH.AVERAGE_VALUE>=ZENKOKU_AVG) 
            OVER(PARTITION BY SH.AGE,SH.GENDER_CODE) AS FILTERD_CNT
    FROM
        SCHOOL_HEALTH AS SH
        INNER JOIN SUB_ZENKOKU AS Z
            ON SH.AGE=Z.AGE AND SH.GENDER_CODE=Z.GENDER_CODE
            AND SH.CATEGORY_CODE=Z.CATEGORY_CODE
    WHERE
        SURVEY_YEAR=2019
        AND SH.CATEGORY_CODE='10'
    ),
    
SUB_WEIGHTS AS (
    SELECT
        SH.AGE,
        SH.GENDER_CODE,
        SH.AVERAGE_VALUE AS W_AVG,
        ZENKOKU_AVG,
        COUNT(*) 
            OVER(PARTITION BY SH.AGE,SH.GENDER_CODE) AS ROWS_CNT,
        COUNT(*) 
            FILTER(WHERE SH.AVERAGE_VALUE>=ZENKOKU_AVG) 
            OVER(PARTITION BY SH.AGE,SH.GENDER_CODE) AS FILTERD_CNT
    FROM
        SCHOOL_HEALTH AS SH
        INNER JOIN SUB_ZENKOKU AS Z
            ON SH.AGE=Z.AGE AND SH.GENDER_CODE=Z.GENDER_CODE
            AND SH.CATEGORY_CODE=Z.CATEGORY_CODE
    WHERE
        SURVEY_YEAR=2019
        AND SH.CATEGORY_CODE='20'
    )
SELECT
    H.AGE AS 'AGE',
    CASE WHEN H.GENDER_CODE='20' THEN 'MALE' ELSE 'FEMALE' END AS 'GENDER',
    ROUND(AVG(H_AVG),1) AS 'H_AVG',
    ROUND(
        (CAST(H.FILTERD_CNT AS REAL)/CAST(H.ROWS_CNT AS REAL))*100
        ,1) || '%' AS H_PER,
    ROUND(AVG(W_AVG),1) AS 'W_AVG',
    ROUND(
        (CAST(W.FILTERD_CNT AS REAL)/CAST(W.ROWS_CNT AS REAL))*100
        ,1) || '%' AS W_PER
FROM
    SUB_HEIGHTS AS H
    INNER JOIN SUB_WEIGHTS AS W
        ON H.AGE=W.AGE AND H.GENDER_CODE=W.GENDER_CODE
GROUP BY
    H.AGE,H.GENDER_CODE
ORDER BY
    H.AGE DESC, H.GENDER_CODE DESC
;