-----------------2-1 --------------
with
table1 as (
SELECT a.mcode,count(*) as count_num,
CASE
WHEN a.mm in ('01','02','03') THEN '1분기'
WHEN a.mm in ('04','05','06') THEN '2분기'
WHEN a.mm in ('07','08','09') THEN '3분기'
ELSE '4분기' END as "quarter"
from "text_biz_dw"."e_test" as a
join "text_biz_dw"."e_media" as b
ON a.mcode=b.mcode AND a.userid=b.userid and a.mm=b.mm and a.yyyy=b.yyyy
join "text_biz_dw"."e_content_meta" as c
on c.mcode=a.mcode and c.mm=a.mm and c.yyyy=a.yyyy
where (c.grade=3 or c.grade=4 or c.grade=5 or c.grade=6)
AND NOT a."datestamp[active]" IS NULL
group by a.mcode, a.mm),
-----------------2-2--------------
table2 as(
SELECT a.mcode, avg(d.grade) as avg_grade
from "text_biz_dw"."e_test" as a
join "text_biz_dw"."e_media" as b
ON a.mcode=b.mcode AND a.userid=b.userid and a.mm=b.mm and a.yyyy=b.yyyy
join "text_biz_dw"."e_content_meta" as c
on c.mcode=a.mcode and c.mm=a.mm and c.yyyy=a.yyyy
join "text_biz_dw"."e_member" as d
ON a.userid=d.userid
where (c.grade=3 or c.grade=4 or c.grade=5 or c.grade=6)
AND NOT a."datestamp[active]" IS NULL
group by a.mcode),
------------------2-3------------
table3 as (
SELECT a.mcode, sum(d.learning_time) as sum_time
from "text_biz_dw"."e_test" as a
join "text_biz_dw"."e_media" as b
ON a.mcode=b.mcode AND a.userid=b.userid and a.mm=b.mm and a.yyyy=b.yyyy
join "text_biz_dw"."e_content_meta" as c
on c.mcode=a.mcode and c.mm=a.mm and c.yyyy=a.yyyy
join "text_biz_dw"."e_learning_time_proc" as d
ON a.userid=d.userid and a.mm=d.mm and a.yyyy=d.yyyy
where (c.grade=3 or c.grade=4 or c.grade=5 or c.grade=6)
AND NOT a."datestamp[active]" IS NULL
group by a.mcode),
-------------------2-4------------------
table4 as (
SELECT a.mcode, avg(item_count) as avg_count , avg(correct_count) as avg_correct, avg(score) as avg_score
from "text_biz_dw"."e_test" as a
join "text_biz_dw"."e_media" as b
ON a.mcode=b.mcode AND a.userid=b.userid and a.mm=b.mm and a.yyyy=b.yyyy
join "text_biz_dw"."e_content_meta" as c
on c.mcode=a.mcode and c.mm=a.mm and c.yyyy=a.yyyy
where (c.grade=3 or c.grade=4 or c.grade=5 or c.grade=6)
AND NOT a."datestamp[active]" IS NULL
group by a.mcode)
select table1.quarter,table1.mcode,table1.count_num, table2.avg_grade, table3.sum_time, table4.avg_count, table4.avg_correct, table4.avg_score
from table1
join table2
on table1.mcode=table2.mcode
join table3
on table1.mcode=table3.mcode
join table4
on table1.mcode=table4.mcode
where table1.quarter='3분기'
