------------------------------------------------------------------------
-- BOOSTER QUIZ 5-1-1
------------------------------------------------------------------------

    -- (결과-2)
    ord_st  
    ------  
    MFGC    
    PKUP    
    PREP    

    -- (결과-3)
    shop_id  
    -------  
    S100     
    S280     

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-2-1
------------------------------------------------------------------------

    -- (결과-2)
    shop_id  CNT  SUM_AMT    
    -------  ---  ---------  
    S047     4    20500.000  
    S064     1    4000.000   
    S068     2    18000.000  
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-2-2
------------------------------------------------------------------------

    SELECT  t1.item_size_cd ,COUNT(*) item_cnt
    FROM    startdbmy.ms_item t1
    WHERE   item_cat = 'COF'
    GROUP BY t1.item_size_cd
    ORDER BY t1.item_size_cd;

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-2-3
------------------------------------------------------------------------

    SELECT  t1.mbr_id
            ,SUM(t1.ord_amt) ord_amt_sum
            ,COUNT(*) ord_cnt
    FROM    startdbmy.tr_ord t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20220105','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20220106','%Y%m%d')
    GROUP BY t1.mbr_id
    ORDER BY ord_amt_sum DESC;

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-3-1
------------------------------------------------------------------------

    2번, GROUP BY에 없는 lach_dt를 집계함수 없이 사용중
    
------------------------------------------------------------------------
-- BOOSTER QUIZ 5-3-2
------------------------------------------------------------------------

    SELECT  MIN(t1.ord_dtm) first_ord_dtm
            ,MAX(t1.ord_dtm) last_ord_dtm
            ,t1.mbr_id
    FROM    startdbmy.tr_ord t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20220103','%Y%m%d')
    AND     t1.shop_id IN ('S012','S212')
    GROUP BY t1.mbr_id;

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-4-1
------------------------------------------------------------------------

    3: GROUP BY에 없는 item_size_cd를 SELECT 절에서 사용하고 있음
    6: GROUP BY에 없는 item_id를 HAVING 절에서 사용하고 있음

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-4-2
------------------------------------------------------------------------

    SELECT  t1.mbr_id ,SUM(t1.ord_amt) ord_amt_sum
    FROM    startdbmy.tr_ord t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20240101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20240201','%Y%m%d')
    GROUP BY t1.mbr_id
    HAVING SUM(t1.ord_amt) >= 400000
    ORDER BY ord_amt_sum DESC;
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-5-1
------------------------------------------------------------------------

    ord_ym  cnt  
    ------  ---  
    202109  3    
    202110  2    
    202111  2    
    
------------------------------------------------------------------------
-- BOOSTER QUIZ 5-5-2
------------------------------------------------------------------------

    SELECT  DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym
            ,COUNT(*) ord_cnt
            ,SUM(t1.ord_amt) ord_amt_sum
    FROM    startdbmy.tr_ord t1
    WHERE   t1.shop_id = 'S246'
    AND     t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20230101','%Y%m%d')
    GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y%m')
    ORDER BY ord_ym DESC;
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-5-3
------------------------------------------------------------------------

    SELECT  DATE_FORMAT(t1.ord_dtm) ord_hour
            ,COUNT(*) ord_cnt
    FROM    startdbmy.tr_ord t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20230101','%Y%m%d')
    GROUP BY DATE_FORMAT(t1.ord_dtm)
    ORDER BY ord_cnt DESC;
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-6-1
------------------------------------------------------------------------

    SELECT  t1.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd
            ,COUNT(*) ord_cnt
    FROM    startdbmy.tr_ord t1
    WHERE   t1.shop_id IN ('S002','S003')
    AND     t1.ord_dtm >= STR_TO_DATE('20230601','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20230604','%Y%m%d')
    GROUP BY t1.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
    ORDER BY t1.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d');
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-6-2
------------------------------------------------------------------------

    SELECT  t1.shop_id ,TIMESTAMPDIFF(MINUTE, t1.ord_dtm, t1.prep_cmp_dtm) 제조완료분수
            ,COUNT(*) ord_cnt
    FROM    startdbmy.tr_ord t1
    WHERE   t1.shop_id IN ('S213','S214')
    AND     t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20220201','%Y%m%d')
    GROUP BY t1.shop_id ,TIMESTAMPDIFF(MINUTE, t1.ord_dtm, t1.prep_cmp_dtm)
    ORDER BY t1.shop_id ,TIMESTAMPDIFF(MINUTE, t1.ord_dtm, t1.prep_cmp_dtm);

------------------------------------------------------------------------
-- BOOSTER QUIZ 5-8-1
------------------------------------------------------------------------

    SELECT  COUNT(*) ord_cnt
            ,COUNT(DISTINCT t1.shop_id) ord_shop_cnt
    FROM    startdbmy.tr_ord t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20250101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20250201','%Y%m%d');
  
  
------------------------------------------------------------------------
-- BOOSTER QUIZ 5-9-1
------------------------------------------------------------------------

    mbr_id + ord_ymd
    
------------------------------------------------------------------------
-- BOOSTER QUIZ 5-9-2
------------------------------------------------------------------------

    shop_id + ord_hour + ord_st