# 1. 총 계약건수
SELECT sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data";
# 2. 보험회사 구분별 계약건수
SELECT inco_typ_nm, inco_typ_val, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" group by inco_typ_nm, inco_typ_val ORDER BY 3 desc;
#3. 보험회사 구분별 계약건수가 많은 보험회사 Top10
WITH inco_typ1 AS (
SELECT inco_nm, inco_typ_nm, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" WHERE inco_typ_val = 1
GROUP BY inco_nm, inco_typ_nm order by 3 desc limit 10
),
inco_typ2 AS (
SELECT inco_nm, inco_typ_nm, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" WHERE inco_typ_val = 2
GROUP BY inco_nm, inco_typ_nm order by 3 desc limit 10
),
inco_typ3 AS (
SELECT inco_nm, inco_typ_nm, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" WHERE inco_typ_val = 3
GROUP BY inco_nm, inco_typ_nm order by 3 desc limit 10
)
SELECT * FROM inco_typ1
UNION ALL
SELECT * FROM inco_typ2
UNION ALL
SELECT * FROM inco_typ3;
# 4. 보장항목 코드별 계약건수
SELECT gnt_itm_nm, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" group by gnt_itm_nm ORDER BY 2 desc;
# 5. 성별 계약건수
SELECT sex_cd, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" group by sex_cd;
# 6. 성별 보험회사 구분별 계약건수
SELECT sex_cd, inco_typ_nm, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" group by sex_cd, inco_typ_nm;
# 7. 연령대별 계약건수
# 유년 (0~7), 소년(8~18), 청년(19~30), 장년(31~40), 중년(41~60), 노년(61~)
WITH age_sales_cnt AS (
SELECT 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, hld_cont_cnt
FROM "db_techday_da_202202"."data"
)
SELECT age_cd, sum(hld_cont_cnt) AS cnt FROM age_sales_cnt GROUP BY age_cd order by 1;
# 8. 연령대별 계약건수가 많은 보장항목 Top5
WITH age_sales_cnt AS (
SELECT 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, gnt_itm_nm, hld_cont_cnt
FROM "db_techday_da_202202"."data"
),
age_step1_top10_cnt AS (
SELECT age_cd, gnt_itm_nm, sum(hld_cont_cnt) AS cnt
FROM age_sales_cnt
WHERE age_cd = 'step1'
GROUP BY age_cd, gnt_itm_nm order by 3 desc limit 10
),
age_step2_top10_cnt AS (
SELECT age_cd, gnt_itm_nm, sum(hld_cont_cnt) AS cnt
FROM age_sales_cnt
WHERE age_cd = 'step2'
GROUP BY age_cd, gnt_itm_nm order by 3 desc limit 10
),
age_step3_top10_cnt AS (
SELECT age_cd, gnt_itm_nm, sum(hld_cont_cnt) AS cnt
FROM age_sales_cnt
WHERE age_cd = 'step3'
GROUP BY age_cd, gnt_itm_nm order by 3 desc limit 10
),
age_step4_top10_cnt AS (
SELECT age_cd, gnt_itm_nm, sum(hld_cont_cnt) AS cnt
FROM age_sales_cnt
WHERE age_cd = 'step4'
GROUP BY age_cd, gnt_itm_nm order by 3 desc limit 10
),
age_step5_top10_cnt AS (
SELECT age_cd, gnt_itm_nm, sum(hld_cont_cnt) AS cnt
FROM age_sales_cnt
WHERE age_cd = 'step5'
GROUP BY age_cd, gnt_itm_nm order by 3 desc limit 10
),
age_step6_top10_cnt AS (
SELECT age_cd, gnt_itm_nm, sum(hld_cont_cnt) AS cnt
FROM age_sales_cnt
WHERE age_cd = 'step6'
GROUP BY age_cd, gnt_itm_nm order by 3 desc limit 10
)
SELECT * FROM age_step1_top10_cnt
UNION ALL
SELECT * FROM age_step2_top10_cnt
UNION ALL
SELECT * FROM age_step3_top10_cnt
UNION ALL
SELECT * FROM age_step4_top10_cnt
UNION ALL
SELECT * FROM age_step5_top10_cnt
UNION ALL
SELECT * FROM age_step6_top10_cnt;
#9.보험회사 구분별(생명보험 = 1) 보험회사 계약건수 6개월간 추이
WITH inco_typ1_cnt AS (
SELECT strd_yymm, inco_nm, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" WHERE inco_typ_val = 1
GROUP BY strd_yymm, inco_nm
)
SELECT SUBSTR(CAST(strd_yymm AS VARCHAR), 5, 2) mm, inco_nm, cnt
FROM inco_typ1_cnt order by 1,3 desc;
#10.보험회사 구분별(손해보험 = 2) 보험회사 계약건수 6개월간 추이
WITH inco_typ1_cnt AS (
SELECT strd_yymm , inco_nm, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data" WHERE inco_typ_val = 2
GROUP BY strd_yymm, inco_nm
)
SELECT SUBSTR(CAST(strd_yymm AS VARCHAR), 5, 2) mm, inco_nm, cnt
FROM inco_typ1_cnt order by 1,3 desc;
#11.성별 계약건수 6개월간 추이
WITH inco_sex_cnt AS (
SELECT strd_yymm, sex_cd, sum(hld_cont_cnt) as cnt
FROM "db_techday_da_202202"."data"
GROUP BY strd_yymm, sex_cd
)
SELECT SUBSTR(CAST(strd_yymm AS VARCHAR), 5, 2) mm, sex_cd, cnt
FROM inco_sex_cnt order by 1,3 desc;
#12.연령별 계약건수 6개월간 추이
WITH age_sales_cnt AS (
SELECT strd_yymm, 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, hld_cont_cnt
FROM "db_techday_da_202202"."data"
),
age_step_sales_cnt AS (
SELECT strd_yymm, age_cd, SUM(hld_cont_cnt) AS cnt
FROM age_sales_cnt GROUP BY strd_yymm, age_cd
)
SELECT SUBSTR(CAST(strd_yymm AS VARCHAR), 5, 2) mm, age_cd, cnt
FROM age_step_sales_cnt
ORDER BY 1, 3;
#13.장표 (월별, 성별, 연령별, 보험회사구분별, 보험회사별, 보장항목 계약건수)
# 유년 (0~7), 소년(8~18), 청년(19~30), 장년(31~40), 중년(41~60), 노년(61~)
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
ORDER BY strd_yymm, sex_nm, age_cd, inco_typ_val, inco_nm, gnt_itm_nm;
