1번 null제거

with
table1 as(
SELECT '정회원' as "category" ,COUNT(*) AS cnt, mm, yyyy
FROM "text_biz_dw"."e_member"
WHERE (mm = '10' or mm ='11' or mm='12')
and yyyy='2022'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
group by mm, yyyy

union

SELECT '준회원' as "category" ,COUNT(*) AS cnt, mm, yyyy
FROM "text_biz_dw"."e_member"
WHERE (mm = '10' or mm ='11' or mm='12')
and yyyy='2022'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
group by mm, yyyy

),

table2 as (SELECT COUNT(*) AS s_cnt, mm
FROM "text_biz_dw"."e_member"
WHERE (mm = '10' or mm ='11' or mm='12')
and yyyy='2022'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55' or memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
group by mm
),
table3 as (SELECT '정회원' as "category" ,sum(caliper_learning_time) as learning_time, a.mm
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm= '11' or a.mm= '12')
and a.yyyy='2022'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
AND NOT b."datestamp[active]" IS NULL
group by a.mm

union

SELECT '준회원' as "category" ,sum(caliper_learning_time) as learning_time, a.mm
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm= '11' or a.mm= '12')
and a.yyyy='2022'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
AND NOT b."datestamp[active]" IS NULL
group by a.mm),

table4 as (SELECT '정회원' as "category" , sum(video_pause_count) as sum_pause, avg(video_pause_count) as mean__pause, sum(video_jump_count) as sum_jump, avg(video_jump_count) as mean_jump, a.mm
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm= '11' or a.mm= '12')
and a.yyyy='2022'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
AND NOT b."datestamp[active]" IS NULL
group by a.mm

union

SELECT '준회원' as "category" , sum(video_pause_count) as sum_pause, avg(video_pause_count) as mean__pause, sum(video_jump_count) as sum_jump, avg(video_jump_count) as mean_jump, a.mm
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm= '11' or a.mm= '12')
and a.yyyy='2022'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
AND NOT b."datestamp[active]" IS NULL
group by a.mm
),

table5 AS (
SELECT '정회원' as "category",sum(b.item_count) as item, a.mm
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm ='11' or a.mm='12')
and a.yyyy='2022'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
AND NOT b."datestamp[active]" IS NULL
group by a.mm

union

SELECT '준회원' as "category",sum(b.item_count) as item, a.mm

FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm ='11' or a.mm='12')
and a.yyyy='2022'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
AND NOT b."datestamp[active]" IS NULL
group by a.mm

),
table6 AS (
    SELECT '정회원' as "category",sum(b.correct_count) as correct, avg(b.score) as mean_score,a.mm

FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm ='11' or a.mm='12')
and a.yyyy='2022'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
AND NOT b."datestamp[active]" IS NULL
group by a.mm

union
SELECT '준회원' as "category",sum(b.correct_count) as correct, avg(b.score) as mean_score,a.mm
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm ='11' or a.mm='12')
and a.yyyy='2022'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
AND NOT b."datestamp[active]" IS NULL
group by a.mm
),
table7 as (
SELECT '정회원' as "category",sum(correct_count) as sum_wrong_correct, a.mm
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm= '11' or a.mm= '12')
and a.yyyy='2022'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
group by a.mm

UNION

SELECT '준회원' as "category" ,sum(correct_count) as sum_wrong_correct, a.mm
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
WHERE (a.mm = '10' or a.mm= '11' or a.mm= '12')
and a.yyyy='2022'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
group by a.mm)

SELECT table1.category, table1.yyyy, table1.mm ,(cast(table1.cnt as double) / cast(table2.s_cnt as double))100 AS change_ratio, table3.learning_time, table4.sum_pause, table4.mean__pause, table4.sum_jump, table4.mean_jump, mean_score, (cast(table6.correct as double) / cast(table5.item as double))100 AS test_ratio,table7.sum_wrong_correct
FROM table1
JOIN table2
ON table1.mm=table2.mm
JOIN table3
on table1.mm=table3.mm and table1."category"=table3."category"
join table4
on table1.mm=table4.mm and table1."category"=table4."category"
join table5
on table1.mm=table5.mm and table1."category"=table5."category"
join table6
on table1.mm=table6.mm and table1."category"=table6."category"
join table7
on table1.mm=table7.mm and table1."category"=table7."category"

ORDER BY table1.mm ASC, table1.category asc;