------------------------------------------------------------------------
-- BOOSTER QUIZ 10-1-1
------------------------------------------------------------------------
SELECT CASE WHEN GROUPING(t1.mbr_gd) = 1 THEN 'Total' ELSE t1.mbr_gd END mbr_gd
,COUNT(*) mbr_cnt
FROM startdbmy.ms_mbr t1
WHERE t1.mbr_st = 'ACTV'
GROUP BY t1.mbr_gd WITH ROLLUP;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-1-2
------------------------------------------------------------------------
SELECT CASE WHEN GROUPING(t1.mbr_gd) = 1 THEN 'Total' ELSE t1.mbr_gd END mbr_gd
,CASE WHEN GROUPING(t1.join_tp) = 1 THEN 'Total' ELSE t1.join_tp END join_tp
,COUNT(*) mbr_cnt
FROM startdbmy.ms_mbr t1
WHERE t1.mbr_st = 'ACTV'
GROUP BY t1.mbr_gd, t1.join_tp WITH ROLLUP;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-1-3
------------------------------------------------------------------------
SELECT CASE WHEN GROUPING(t1.join_tp) = 1 THEN 'Total' ELSE t1.join_tp END join_tp
,CASE WHEN GROUPING(t1.mbr_gd) = 1 THEN 'Total' ELSE t1.mbr_gd END mbr_gd
,COUNT(*) mbr_cnt
FROM startdbmy.ms_mbr t1
WHERE t1.mbr_st = 'ACTV'
GROUP BY t1.join_tp ,t1.mbr_gd WITH ROLLUP;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-1-4
------------------------------------------------------------------------
SELECT CASE WHEN t3.gr_join_tp = 1 THEN 'Total' ELSE t4.base_cd_nm END join_tp_nm
,CASE WHEN t3.gr_ord_ym = 1 THEN 'Total' ELSE t3.ord_ym END ord_ym
,CASE WHEN t3.gr_pay_tp = 1 THEN 'Total' ELSE t5.base_cd_nm END pay_tp_nm
,t3.ord_amt_sum
FROM (
SELECT t1.join_tp
,GROUPING(t1.join_tp) gr_join_tp
,DATE_FORMAT(t2.ord_dtm,'%Y%m') ord_ym
,GROUPING(DATE_FORMAT(t2.ord_dtm,'%Y%m')) gr_ord_ym
,t2.pay_tp
,GROUPING(t2.pay_tp) gr_pay_tp
,SUM(t2.ord_amt) ord_amt_sum
FROM startdbmy.ms_mbr t1
INNER JOIN startdbmy.tr_ord t2
ON (t2.mbr_id = t1.mbr_id)
WHERE t2.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t2.ord_dtm < STR_TO_DATE('20230301','%Y%m%d')
AND t1.mbr_gd = 'PLAT'
GROUP BY t1.join_tp ,DATE_FORMAT(t2.ord_dtm,'%Y%m') ,t2.pay_tp WITH ROLLUP
) t3
LEFT OUTER JOIN startdbmy.cm_base_cd t4
ON (t4.base_cd_dv = 'join_tp' AND t4.base_cd = t3.join_tp)
LEFT OUTER JOIN startdbmy.cm_base_cd t5
ON (t5.base_cd_dv = 'pay_tp' AND t5.base_cd = t3.pay_tp)
;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-1-5
------------------------------------------------------------------------
SELECT CASE WHEN t3.gr_join_tp = 1 THEN 'Total' ELSE t4.base_cd_nm END join_tp_nm
,CASE WHEN t3.gr_ord_ym = 1 THEN 'Total' ELSE t3.ord_ym END ord_ym
,CASE WHEN t3.gr_pay_tp = 1 THEN 'Total' ELSE t5.base_cd_nm END pay_tp_nm
,t3.ord_amt_sum
FROM (
SELECT t1.join_tp
,GROUPING(t1.join_tp) gr_join_tp
,DATE_FORMAT(t2.ord_dtm,'%Y%m') ord_ym
,GROUPING(DATE_FORMAT(t2.ord_dtm,'%Y%m')) gr_ord_ym
,t2.pay_tp
,GROUPING(t2.pay_tp) gr_pay_tp
,SUM(t2.ord_amt) ord_amt_sum
FROM startdbmy.ms_mbr t1
INNER JOIN startdbmy.tr_ord t2
ON (t2.mbr_id = t1.mbr_id)
WHERE t2.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t2.ord_dtm < STR_TO_DATE('20230301','%Y%m%d')
AND t1.mbr_gd = 'PLAT'
GROUP BY t1.join_tp ,DATE_FORMAT(t2.ord_dtm,'%Y%m') ,t2.pay_tp WITH ROLLUP
) t3
LEFT OUTER JOIN startdbmy.cm_base_cd t4
ON (t4.base_cd_dv = 'join_tp' AND t4.base_cd = t3.join_tp)
LEFT OUTER JOIN startdbmy.cm_base_cd t5
ON (t5.base_cd_dv = 'pay_tp' AND t5.base_cd = t3.pay_tp)
WHERE (
t3.gr_pay_tp = 0 -- 기본 데이터(GROUP BY 마지막 컬럼의 GROUPING이 0)
OR
(t3.gr_join_tp = 0 AND t3.gr_ord_ym = 1 AND t3.gr_pay_tp = 1) -- 중간합계(join_tp별 집계 데이터만)
);
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-2-1
------------------------------------------------------------------------
SELECT t1.mbr_id
,SUM(CASE WHEN DATE_FORMAT(t1.ord_dtm,'%Y%m%d') = '20230101' THEN t1.ord_amt END) ord_amt_0101
,SUM(CASE WHEN DATE_FORMAT(t1.ord_dtm,'%Y%m%d') = '20230102' THEN t1.ord_amt END) ord_amt_0102
,SUM(CASE WHEN DATE_FORMAT(t1.ord_dtm,'%Y%m%d') = '20230103' THEN t1.ord_amt END) ord_amt_0103
FROM startdbmy.tr_ord t1
WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230104','%Y%m%d')
AND t1.shop_id = 'S028'
GROUP BY t1.mbr_id
ORDER BY t1.mbr_id;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-2-2
------------------------------------------------------------------------
SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd
,IFNULL(SUM(CASE WHEN t1.mbr_id = 'M0027' THEN 1 END),0) ord_cnt_M0027
,IFNULL(SUM(CASE WHEN t1.mbr_id = 'M3627' THEN 1 END),0) ord_cnt_M3627
,IFNULL(SUM(CASE WHEN t1.mbr_id = 'M3927' THEN 1 END),0) ord_cnt_M3927
FROM startdbmy.tr_ord t1
WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230104','%Y%m%d')
AND t1.shop_id = 'S028'
GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m%d');
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-2-3
------------------------------------------------------------------------
SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym
,t1.shop_id
,t3.item_cat
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t1.ord_no = t2.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d')
AND t1.shop_id IN ('S001','S002')
GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat
ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-2-4
------------------------------------------------------------------------
SELECT t4.ord_ym ,t4.item_cat
,SUM(CASE WHEN t4.shop_id = 'S001' THEN t4.ord_qty_sum END) qty_S001
,SUM(CASE WHEN t4.shop_id = 'S002' THEN t4.ord_qty_sum END) qty_S002
FROM (
SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym
,t1.shop_id
,t3.item_cat
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t1.ord_no = t2.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d')
AND t1.shop_id IN ('S001','S002')
GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat
ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat
) t4
GROUP BY t4.ord_ym ,t4.item_cat
ORDER BY t4.ord_ym ,t4.item_cat;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-2-5
------------------------------------------------------------------------
WITH w1 AS (
SELECT t4.ord_ym ,t4.item_cat
,SUM(CASE WHEN t4.shop_id = 'S001' THEN t4.ord_qty_sum END) qty_S001
,SUM(CASE WHEN t4.shop_id = 'S002' THEN t4.ord_qty_sum END) qty_S002
FROM (
SELECT DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym
,t1.shop_id
,t3.item_cat
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t1.ord_no = t2.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230301','%Y%m%d')
AND t1.shop_id IN ('S001','S002')
GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat
ORDER BY DATE_FORMAT(t1.ord_dtm,'%Y%m') ,t1.shop_id ,t3.item_cat
) t4
GROUP BY t4.ord_ym ,t4.item_cat
ORDER BY t4.ord_ym ,t4.item_cat
)
SELECT CASE WHEN GROUPING(t5.ord_ym) = 1 THEN 'Total'
ELSE t5.ord_ym END ord_ym
,CASE WHEN GROUPING(t5.item_cat) = 1 THEN 'Total'
ELSE t5.item_cat END item_cat
,CASE WHEN GROUPING(t5.item_cat) = 1 THEN 'Total'
ELSE (SELECT MAX(x.item_cat_nm) FROM startdbmy.ms_item_cat x WHERE x.item_cat = t5.item_cat)
END item_cat_nm
,SUM(t5.qty_S001) qty_S001
,SUM(t5.qty_S002) qty_S002
FROM w1 t5
GROUP BY t5.ord_ym ,t5.item_cat WITH ROLLUP
ORDER BY t5.ord_ym, t5.item_cat;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-2-6
------------------------------------------------------------------------
WITH w1 AS (
SELECT t3.mbr_gd
,t2.shop_oper_tp
,COUNT(*) ord_cnt
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.ms_shop t2 ON (t2.shop_id = t1.shop_id)
INNER JOIN startdbmy.ms_mbr t3 ON (t3.mbr_id = t1.mbr_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20240101','%Y%m%d')
GROUP BY t3.mbr_gd
,t2.shop_oper_tp
)
SELECT CASE WHEN GROUPING(t4.mbr_gd) = 1 THEN 'Total'
ELSE (SELECT max(x.base_cd_nm)
FROM startdbmy.cm_base_cd x
WHERE x.base_cd_dv = 'mbr_gd'
AND x.base_cd = t4.mbr_gd)
END 회원등급
,SUM(CASE WHEN t4.shop_oper_tp = 'FLAG' THEN t4.ord_cnt END) Flagship
,SUM(CASE WHEN t4.shop_oper_tp = 'DRCT' THEN t4.ord_cnt END) Directly
,SUM(CASE WHEN t4.shop_oper_tp = 'DIST' THEN t4.ord_cnt END) Distributor
FROM w1 t4
GROUP BY t4.mbr_gd WITH ROLLUP
ORDER BY GROUPING(t4.mbr_gd) DESC
,CASE WHEN t4.mbr_gd = 'PLAT' THEN 1
WHEN t4.mbr_gd = 'GOLD' THEN 2
WHEN t4.mbr_gd = 'SILV' THEN 3 END;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-3-1
------------------------------------------------------------------------
SELECT t1.base_ymd ord_ymd
,t1.mbr_id
,t1.nick_nm
,IFNULL(t2.ord_amt_sum,0) ord_amt_sum
FROM (
SELECT b.base_ymd ,a.mbr_id ,a.nick_nm
FROM startdbmy.ms_mbr a
CROSS JOIN startdbmy.cm_base_dt b
WHERE a.nick_nm IN ('Air','Wind3')
AND b.base_dt >= STR_TO_DATE('20230101','%Y%m%d')
AND b.base_dt < STR_TO_DATE('20230104','%Y%m%d')
) t1
LEFT OUTER JOIN (
SELECT DATE_FORMAT(c.ord_dtm,'%Y%m%d') ord_ymd ,c.mbr_id
,SUM(c.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord c
WHERE c.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND c.ord_dtm < STR_TO_DATE('20230104','%Y%m%d')
GROUP BY DATE_FORMAT(c.ord_dtm,'%Y%m%d') ,c.mbr_id
) t2
ON (t2.mbr_id = t1.mbr_id AND t2.ord_ymd = t1.base_ymd)
ORDER BY t1.base_ymd, t1.mbr_id;
------------------------------------------------------------------------
-- BOOSTER QUIZ 10-3-2
------------------------------------------------------------------------
SELECT t1.ym
,IFNULL(t2.shop_open_cnt,0) shop_open_cnt
,IFNULL(t3.mbr_join_cnt,0) mbr_join_cnt
,IFNULL(t4.ord_cnt,0) ord_cnt
FROM (
SELECT DISTINCT DATE_FORMAT(a.base_dt,'%Y%m') ym
FROM startdbmy.cm_base_dt a
WHERE a.base_dt >= STR_TO_DATE('20210401','%Y%m%d')
AND a.base_dt < STR_TO_DATE('20210701','%Y%m%d')
) t1
LEFT OUTER JOIN (
SELECT SUBSTR(b.shop_start_ymd,1,6) ym
,COUNT(*) shop_open_cnt
FROM startdbmy.ms_shop b
WHERE b.shop_start_ymd >= '20210401'
AND b.shop_start_ymd < '20210701'
GROUP BY SUBSTR(b.shop_start_ymd,1,6)
) t2 ON (t2.ym = t1.ym)
LEFT OUTER JOIN (
SELECT DATE_FORMAT(c.join_dtm,'%Y%m') ym
,COUNT(*) mbr_join_cnt
FROM startdbmy.ms_mbr c
WHERE c.join_dtm >= STR_TO_DATE('20210401','%Y%m%d')
AND c.join_dtm < STR_TO_DATE('20210701','%Y%m%d')
GROUP BY DATE_FORMAT(c.join_dtm,'%Y%m')
) t3 ON (t3.ym = t1.ym)
LEFT OUTER JOIN (
SELECT DATE_FORMAT(d.ord_dtm,'%Y%m') ym
,COUNT(*) ord_cnt
FROM startdbmy.tr_ord d
WHERE d.ord_dtm >= STR_TO_DATE('20210401','%Y%m%d')
AND d.ord_dtm < STR_TO_DATE('20210701','%Y%m%d')
GROUP BY DATE_FORMAT(d.ord_dtm,'%Y%m')
) t4 ON (t4.ym = t1.ym)
ORDER BY t1.ym;