Athena_CTAS.sql

CREATE TABLE insurance_summary_data
WITH (
      external_location = 's3://s3-techday-da-202202/insurance_summary_data/',
      format = 'PARQUET',
      write_compression = 'SNAPPY')
AS WITH inco_sales AS (
SELECT strd_yymm, 
    CASE WHEN sex_cd = 'M' THEN '남성'
    WHEN sex_cd = 'F' THEN '여성'
    END sex_nm,
    CASE WHEN age_val <= 7 THEN 'step1'
    WHEN age_val > 7 AND age_val <= 18 THEN 'step2'
    WHEN age_val > 19 AND age_val <= 30 THEN 'step3'
    WHEN age_val > 31 AND age_val <= 40 THEN 'step4'
    WHEN age_val > 41 AND age_val <= 60 THEN 'step5'
    ELSE 'step6' END age_cd,
    inco_typ_nm, inco_typ_val, inco_nm, gnt_itm_nm, hld_cont_cnt
FROM "db_techday_da_202202"."data" 
)
SELECT strd_yymm, sex_nm, age_cd,
CASE WHEN age_cd = 'step1' THEN '유년'
WHEN age_cd = 'step2' THEN '소년'
WHEN age_cd = 'step3' THEN '청년'
WHEN age_cd = 'step4' THEN '장년'
WHEN age_cd = 'step5' THEN '중년'
ELSE '노년' END age_nm, inco_typ_nm, inco_nm, gnt_itm_nm, SUM(hld_cont_cnt) AS cnt
FROM inco_sales 
GROUP BY strd_yymm, sex_nm, age_cd, inco_typ_val, inco_typ_nm, inco_nm, gnt_itm_nm