WITH D2019 AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , CASE
            WHEN CATEGORY_CODE = 10 THEN 'HEIGHT'
            WHEN CATEGORY_CODE = 20 THEN 'WEIGHT'
            END
            AS CATEGORY
        , CASE
            WHEN GENDER_CODE = 20 THEN 'MALE'
            WHEN GENDER_CODE = 30 THEN 'FEMALE'
            END
            AS GENDER
        , AGE
        , AVERAGE_VALUE
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = 2019
)
, CALC_CATEGORY_AVG AS (
    SELECT
        CATEGORY
        , GENDER
        , AGE
        , ROUND(AVG(AVERAGE_VALUE), 1) AS CAT_AVG
    FROM
        D2019
    GROUP BY
        CATEGORY
        , GENDER
        , AGE
)
, CHECK_UPPER_THAN_AVG AS (
    SELECT
        D2019.PF_CODE
        , D2019.CATEGORY
        , D2019.GENDER
        , D2019.AGE
        , D2019.AVERAGE_VALUE
        , CCA.CAT_AVG
        , CASE WHEN D2019.AVERAGE_VALUE >= CCA.CAT_AVG THEN 1 ELSE 0 END AS 'UPPER_THAN_AVG'
    FROM
        D2019
    LEFT JOIN
        CALC_CATEGORY_AVG AS CCA
    ON
        D2019.CATEGORY = CCA.CATEGORY
        AND
        D2019.GENDER = CCA.GENDER
        AND
        D2019.AGE = CCA.AGE
)
SELECT
    sub.AGE
    , sub.GENDER
    , MAX(CASE WHEN sub.CATEGORY = 'HEIGHT' THEN sub.CAT_AVG END) AS H_AVG
    , MAX(CASE WHEN sub.CATEGORY = 'HEIGHT' THEN sub.PER END) || '%' AS H_PER
    , MAX(CASE WHEN sub.CATEGORY = 'WEIGHT' THEN sub.CAT_AVG END) AS W_AVG
    , MAX(CASE WHEN sub.CATEGORY = 'WEIGHT' THEN sub.PER END) || '%' AS W_PER
FROM (
    SELECT
        CATEGORY
        , GENDER
        , AGE
        , CAT_AVG
        , ROUND(SUM(UPPER_THAN_AVG) * 100.0 / COUNT(UPPER_THAN_AVG), 1) AS PER
    FROM
        CHECK_UPPER_THAN_AVG
    GROUP BY
        CATEGORY
        , GENDER
        , AGE
    ) AS sub
GROUP BY
    sub.AGE
    , sub.GENDER
ORDER BY
    AGE DESC
    ,   CASE GENDER
          WHEN 'FEMALE' THEN 1
          WHEN 'MALE' THEN 2
          END
;