sql 과제 문제풀이

1-1

WITH 
    table1 AS (
        SELECT '정회원' as "기준" ,COUNT(*) AS cnt, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202210'
    AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
    AND memberstatus_change LIKE '-,%'
union    
SELECT '준회원' as "기준" ,COUNT(*) AS cnt, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202210'
    AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
    AND memberstatus_change LIKE '-,%'
    ),
    table2 AS (
        SELECT COUNT(*) AS s_cnt, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202210'
    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 '-,%'
    ),
    table3 AS (
        SELECT '정회원' as "기준" ,COUNT(*) AS cnt, '202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202211'
    AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
    AND memberstatus_change LIKE '-,%'
union    
SELECT '준회원' as "기준" ,COUNT(*) AS cnt, '202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202211'
    AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
    AND memberstatus_change LIKE '-,%'
    ),
    table4 AS (
        SELECT COUNT(*) AS s_cnt, '202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202211'
    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 '-,%'
    ),
    table5 AS (
        SELECT '정회원' as "기준" ,COUNT(*) AS cnt, '202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202212'
    AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
    AND memberstatus_change LIKE '-,%'
union    
SELECT '준회원' as "기준" ,COUNT(*) AS cnt, '202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202212'
    AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
    AND memberstatus_change LIKE '-,%'
    ),
    table6 AS (
        SELECT COUNT(*) AS s_cnt, '202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member"
WHERE CONCAT(yyyy, mm) = '202212'
    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 '-,%'
    )

SELECT '202210' AS "YYYYMM", table1."기준", (cast(table1.cnt as double) / cast(table2.s_cnt as double))*100 AS ratio
FROM table1
JOIN table2
ON table1.YYYYMM=table2.YYYYMM

union

SELECT '202211' AS "YYYYMM", table3."기준", (cast(table3.cnt as double) / cast(table4.s_cnt as double))*100 AS ratio
FROM table3
JOIN table4
ON table3.YYYYMM=table4.YYYYMM

union

SELECT '202212' AS "YYYYMM", table5."기준", (cast(table5.cnt as double) / cast(table6.s_cnt as double))*100 AS ratio
FROM table5
JOIN table6
ON table5.YYYYMM=table6.YYYYMM

ORDER BY YYYYMM ASC, '기준' asc

1-2

SELECT '정회원' as "기준" ,sum(caliper_learning_time) as learning_time, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'

union    
SELECT '준회원' as "기준" ,sum(caliper_learning_time) as learning_time, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'


union
SELECT '정회원' as "기준" ,sum(caliper_learning_time) as learning_time, '202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
union    
SELECT '준회원' as "기준" ,sum(caliper_learning_time) as learning_time, '202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'

union
SELECT '정회원' as "기준" ,sum(caliper_learning_time) as learning_time, '202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
union    
SELECT '준회원' as "기준" ,sum(caliper_learning_time) as learning_time, '202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_study" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'

ORDER BY YYYYMM ASC, '기준' asc

1-3

SELECT '정회원' as "기준" , 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, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'

union

SELECT '준회원' as "기준" , 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, '202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'

union


SELECT '정회원' as "기준" , 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, '202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'

union

SELECT '준회원' as "기준" , 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, '202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'


union


SELECT '정회원' as "기준" , 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, '202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'

union

SELECT '준회원' as "기준" , 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, '202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_media" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'


ORDER BY YYYYMM ASC, '기준' asc;

1-4

WITH 
    table1 AS (
        SELECT '정회원' as "기준",sum(b.item_count) as item,'202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
union
    SELECT '준회원' as "기준",sum(b.item_count) as item,'202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'


    ),
    table2 AS (
        SELECT '정회원' as "기준",sum(b.correct_count) as correct, avg(b.score) as mean_score,'202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
union
    SELECT '준회원' as "기준",sum(b.correct_count) as correct, avg(b.score) as mean_score,'202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'

    ),
    table3 AS (
        SELECT '정회원' as "기준",sum(b.item_count) as item,'202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
union
    SELECT '준회원' as "기준",sum(b.item_count) as item,'202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
    ),
    table4 AS (
        SELECT '정회원' as "기준",sum(b.correct_count) as correct, avg(b.score) as mean_score,'202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
union
    SELECT '준회원' as "기준",sum(b.correct_count) as correct, avg(b.score) as mean_score,'202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
    ),
    table5 AS (
        SELECT '정회원' as "기준",sum(b.item_count) as item,'202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
union
    SELECT '준회원' as "기준",sum(b.item_count) as item,'202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
    ),
    table6 AS (
        SELECT '정회원' as "기준",sum(b.correct_count) as correct, avg(b.score) as mean_score,'202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'
union
    SELECT '준회원' as "기준",sum(b.correct_count) as correct, avg(b.score) as mean_score,'202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_test" AS b
ON a.userid=b.userid
WHERE CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'
    )

SELECT table1."기준", mean_score, (cast(table2.correct as double) / cast(table1.item as double))*100 AS ratio, '202210' AS "YYYYMM"
FROM table1
JOIN table2
ON table1."기준"=table2."기준"


union 

SELECT table3."기준", mean_score, (cast(table4.correct as double) / cast(table3.item as double))*100 AS ratio, '202211' AS "YYYYMM"
FROM table3
JOIN table4
ON table3."기준"=table4."기준"


union
SELECT table5."기준", mean_score, (cast(table6.correct as double) / cast(table5.item as double))*100 AS ratio, '202212' AS "YYYYMM"
FROM table5
JOIN table6
ON table5."기준"=table6."기준"



ORDER BY YYYYMM ASC, '기준' asc;

1-5

SELECT  '정회원' as "기준",sum(correct_count) as sum_wrong_correct,'202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
where CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'

UNION
SELECT '준회원' as "기준" ,sum(correct_count) as sum_wrong_correct,'202210' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
where CONCAT(a.yyyy, a.mm) = '202210'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'

union

SELECT  '정회원' as "기준",sum(correct_count) as sum_wrong_correct,'202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
where CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'

UNION
SELECT '준회원' as "기준" ,sum(correct_count) as sum_wrong_correct,'202211' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
where CONCAT(a.yyyy, a.mm) = '202211'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'

union

SELECT  '정회원' as "기준",sum(correct_count) as sum_wrong_correct,'202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
where CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%11' or memberstatus LIKE '%55')
AND memberstatus_change LIKE '-,%'

UNION
SELECT '준회원' as "기준" ,sum(correct_count) as sum_wrong_correct,'202212' AS "YYYYMM"
FROM "text_biz_dw"."e_member" AS a
JOIN "text_biz_dw"."e_wrong" AS b
ON a.userid=b.userid
where CONCAT(a.yyyy, a.mm) = '202212'
AND (memberstatus LIKE '%01' or memberstatus LIKE '%03' or memberstatus LIKE '%33' or memberstatus LIKE '%99')
AND memberstatus_change LIKE '-,%'

ORDER BY YYYYMM ASC, '기준' asc;