------------------------------------------------------------------------
-- 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;