ソースコード
with AVG_TBL as (
select AGE , GENDER_CODE
    , round( avg( case CATEGORY_CODE when 10 then AVERAGE_VALUE else NULL end ) 
        , 1 ) as H_AVG
    , round( avg( case CATEGORY_CODE when 20 then AVERAGE_VALUE else NULL end ) 
        , 1 ) as W_AVG  
from SCHOOL_HEALTH
where SURVEY_YEAR = 2019
group by AGE , GENDER_CODE , CATEGORY_CODE
) 
select AV.AGE 
    , case AV.GENDER_CODE when 20 then 'MALE' when '30' then 'FEMALE' end as GENDER
    , max(H_AVG) as H_AVG
    , round( 100.0 * count( 
        case when SH.CATEGORY_CODE=10 and AV.H_AVG <= SH.AVERAGE_VALUE 
            then 1 else NULL end )  
        /   count( case when SH.CATEGORY_CODE=10 then 1 else NULL end)
     ,1 ) || '%' as H_PER
    , max(W_AVG) as W_AVG
    , round( 100.0 * count( 
        case when SH.CATEGORY_CODE=20 and AV.W_AVG <= SH.AVERAGE_VALUE 
            then 1 else NULL end )  
        /   count( case when SH.CATEGORY_CODE=20 then 1 else NULL end)
     ,1 ) || '%' as W_PER
from AVG_TBL as AV
LEFT OUTER join SCHOOL_HEALTH as SH on AV.AGE=SH.AGE and AV.GENDER_CODE=SH.GENDER_CODE
 and SH.SURVEY_YEAR = 2019
group by AV.AGE , AV.GENDER_CODE
order by AV.AGE desc , AV.GENDER_CODE desc
提出情報
提出日時2023/08/22 11:15:40
コンテスト第8回 SQLコンテスト
問題身長と体重
受験者hmasa
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量82 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
82 MB
データパターン2
WA
81 MB