/* 練習コンテスト 問題4 */
SELECT
    AGE_NAME as "年齢階層"
    -- ,"pop_by_time_code_table".AGE_CODE
    ,SUM(CASE WHEN "pop_by_time_code_table".TIME_CODE = 120 THEN "pop by time code" ELSE 0 END) as "5時間未満"
    ,SUM(CASE WHEN "pop_by_time_code_table".TIME_CODE = 120 THEN "pop by time code" ELSE 0 END) as "5時間未満"
    ,SUM(CASE WHEN "pop_by_time_code_table".TIME_CODE = 130 THEN "pop by time code" ELSE 0 END) as "5時間以上6時間未満"
    ,SUM(CASE WHEN "pop_by_time_code_table".TIME_CODE = 140 THEN "pop by time code" ELSE 0 END) as "6時間以上7時間未満"
    ,SUM(CASE WHEN "pop_by_time_code_table".TIME_CODE = 150 THEN "pop by time code" ELSE 0 END) as "7時間以上8時間未満"
    ,SUM(CASE WHEN "pop_by_time_code_table".TIME_CODE = 160 THEN "pop by time code" ELSE 0 END) as "8時間以上9時間未満"
    ,SUM(CASE WHEN "pop_by_time_code_table".TIME_CODE = 170 THEN "pop by time code" ELSE 0 END) as "9時間以上"
    ,SUM(CASE WHEN "pop_by_time_code_table".TIME_CODE = 180 THEN "pop by time code" ELSE 0 END) as "不詳"
FROM
    (
    SELECT
        detail.AGE_CODE
        ,detail.TIME_CODE
        ,SUM(TARGET_POP)
            OVER(PARTITION BY detail.AGE_CODE, detail.TIME_CODE)
            as "pop by time code"
        ,ROW_NUMBER()
            OVER(PARTITION BY detail.AGE_CODE, detail.TIME_CODE)
            as "row num"
    FROM
        SLEEP_TIME_DTL as detail
    ) as "pop_by_time_code_table"
INNER JOIN
    AGE_GRP ON AGE_GRP.AGE_CODE = "pop_by_time_code_table".AGE_CODE
INNER JOIN
    SLEEP_TIME ON SLEEP_TIME.TIME_CODE = "pop_by_time_code_table".TIME_CODE
WHERE
    "row num" = 1
GROUP BY
    "pop_by_time_code_table".AGE_CODE
ORDER BY
    "pop_by_time_code_table".AGE_CODE
;