------------------------------------------------------------------------
-- BOOSTER QUIZ 8-1-1
------------------------------------------------------------------------
SELECT t1.mbr_id ,t1.nick_nm
,t1.join_tp
,(SELECT x.base_cd_nm FROM startdbmy.cm_base_cd x WHERE x.base_cd_dv = 'join_tp' AND x.base_cd = t1.join_tp) join_tp_nm
,t1.mbr_gd
,(SELECT x.base_cd_nm FROM startdbmy.cm_base_cd x WHERE x.base_cd_dv = 'mbr_gd' AND x.base_cd = t1.mbr_gd) mbr_gd_nm
,t1.mbr_st
,(SELECT x.base_cd_nm FROM startdbmy.cm_base_cd x WHERE x.base_cd_dv = 'mbr_st' AND x.base_cd = t1.mbr_st) mbr_st_nm
FROM startdbmy.ms_mbr t1
WHERE t1.join_dtm >= STR_TO_DATE('20220419','%Y%m%d')
AND t1.join_dtm < STR_TO_DATE('20220420','%Y%m%d')
ORDER BY t1.mbr_id;
------------------------------------------------------------------------
-- BOOSTER QUIZ 8-1-2
------------------------------------------------------------------------
SELECT t1.ord_no ,t1.ord_dtm ,t2.ord_det_no ,t2.item_id ,t2.item_nm
,(SELECT x.base_cd_nm FROM startdbmy.cm_base_cd x WHERE x.base_cd_dv = 'item_size_cd' AND x.base_cd = t2.item_size_cd) item_size_cd_nm`
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t1.ord_no = t2.ord_no)
WHERE t1.ord_no = 10;
------------------------------------------------------------------------
-- BOOSTER QUIZ 8-1-3
------------------------------------------------------------------------
SELECT t1.ord_no ,t1.ord_dtm
,t1.shop_id ,t2.shop_nm ,t2.shop_size
,t1.mbr_id ,t3.nick_nm
FROM startdbmy.tr_ord t1
LEFT OUTER JOIN startdbmy.ms_shop t2 ON (t2.shop_id = t1.shop_id)
LEFT OUTER JOIN startdbmy.ms_mbr t3 ON (t3.mbr_id = t1.mbr_id)
WHERE t1.ord_no = 100
ORDER BY t1.ord_no;
------------------------------------------------------------------------
-- BOOSTER QUIZ 8-1-4
------------------------------------------------------------------------
SELECT t1.mbr_id ,MAX(t1.nick_nm) nick_nm ,MAX(t1.join_dtm) join_dtm
,IFNULL(SUM(t2.ord_amt),0) ord_amt_sum
,COUNT(t2.ord_no) ord_cnt
FROM startdbmy.ms_mbr t1
LEFT OUTER JOIN startdbmy.tr_ord t2
ON (t2.mbr_id = t1.mbr_id
AND t2.ord_dtm >= STR_TO_DATE('20210801','%Y%m%d')
AND t2.ord_dtm < STR_TO_DATE('20210901','%Y%m%d'))
WHERE t1.join_dtm >= STR_TO_DATE('20210702','%Y%m%d')
AND t1.join_dtm < STR_TO_DATE('20210703','%Y%m%d')
AND t1.mbr_gd = 'PLAT'
AND t1.mbr_st = 'ACTV'
GROUP BY t1.mbr_id
ORDER BY t1.mbr_id;
------------------------------------------------------------------------
-- BOOSTER QUIZ 8-2-1
------------------------------------------------------------------------
SELECT COUNT(*) mbr_cnt
FROM startdbmy.ms_mbr t1
WHERE EXISTS(
SELECT *
FROM startdbmy.tr_ord x
WHERE x.ord_dtm >= STR_TO_DATE('20221224','%Y%m%d')
AND x.ord_dtm < STR_TO_DATE('20221225','%Y%m%d')
AND x.mbr_id = t1.mbr_id);
------------------------------------------------------------------------
-- BOOSTER QUIZ 8-2-2
------------------------------------------------------------------------
SELECT COUNT(*) mbr_cnt
FROM startdbmy.ms_mbr t1
WHERE t1.join_dtm < '20200101'
AND t1.mbr_st = 'ACTV'
AND NOT EXISTS(
SELECT *
FROM startdbmy.tr_ord x
WHERE x.mbr_id = t1.mbr_id
AND x.ord_dtm >= STR_TO_DATE('20210101','%Y%m%d')
AND x.ord_dtm < STR_TO_DATE('20210111','%Y%m%d')
);
------------------------------------------------------------------------
-- BOOSTER QUIZ 8-2-3
------------------------------------------------------------------------
SELECT t1.mbr_gd
,(SELECT x.base_cd_nm FROM startdbmy.cm_base_cd x WHERE x.base_cd_dv = 'mbr_gd' AND x.base_cd = t1.mbr_gd) mbr_gd_nm
,COUNT(*) mbr_cnt
FROM startdbmy.ms_mbr t1
WHERE EXISTS(
SELECT *
FROM startdbmy.tr_ord a
INNER JOIN startdbmy.tr_ord_det b
ON (a.ord_no = b.ord_no)
WHERE a.mbr_id = t1.mbr_id
AND a.ord_dtm >= STR_TO_DATE('20221224','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20221225','%Y%m%d')
AND b.item_id = 'CMFR')
GROUP BY t1.mbr_gd
ORDER BY mbr_cnt DESC;
SQL
복사