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

    SELECT  CASE WHEN t1.hot_cold_cd = 'HOT' THEN '뜨거운'
                 WHEN t1.hot_cold_cd = 'COLD' THEN '차가운' END hot_cold_nm
            ,t1.hot_cold_cd
            ,t1.item_id
            ,t1.item_nm
    FROM    startdbmy.ms_item t1
    WHERE   t1.item_cat IN ('BEV','BKR')
    AND     t1.item_size_cd = 'REG'
    ORDER BY hot_cold_nm ,t1.item_id;
    
------------------------------------------------------------------------
-- BOOSTER QUIZ 6-1-2
------------------------------------------------------------------------

    SELECT  t1.ord_no ,t1.ord_dtm ,t1.mbr_id
            ,CASE WHEN DATE_FORMAT(t1.ord_dtm,'%H%m%s') <= '070000' THEN '아침주문'
                  ELSE '아침이후주문' END 주문시점구분
    FROM    startdbmy.tr_ord t1
    WHERE   t1.mbr_id = 'M0058'
    AND     t1.ord_dtm >= STR_TO_DATE('20241201','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20241202','%Y%m%d')
    ORDER BY t1.ord_no ASC;
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 6-2-1
------------------------------------------------------------------------

    SELECT  t1.ord_no ,t1.ord_dtm ,t1.shop_id ,t1.pkup_dtm
            ,CASE WHEN TIMESTAMPDIFF(MINUTE,t1.ord_dtm,t1.pkup_dtm) > 5 THEN '5분초과'
                  ELSE '5분이하' END 픽업완료시간구분
    FROM    startdbmy.tr_ord t1
    WHERE   t1.shop_id = 'S264'
    AND     t1.ord_dtm >= STR_TO_DATE('20211201','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20211202','%Y%m%d')
    ORDER BY t1.ord_no;

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

    SELECT  CASE WHEN TIMESTAMPDIFF(MINUTE,t1.ord_dtm,t1.pkup_dtm) > 5 THEN '5분초과'
                 ELSE '5분이하' END 픽업완료시간구분
            ,COUNT(*) 주문건수
    FROM    startdbmy.tr_ord t1
    WHERE   t1.shop_id = 'S264'
    AND     t1.ord_dtm >= STR_TO_DATE('20211201','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20211202','%Y%m%d')
    GROUP BY CASE WHEN TIMESTAMPDIFF(MINUTE,t1.ord_dtm,t1.pkup_dtm) > 5 THEN '5분초과'
                  ELSE '5분이하' END
    ORDER BY 주문건수 DESC;
    
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 6-2-3
------------------------------------------------------------------------

    SELECT  t1.shop_id
            ,CASE WHEN DATE_FORMAT(t1.ord_dtm,'%Y%m') <= '202206' THEN '상반기'
                  ELSE '하반기' END 반기구분
            ,COUNT(*) ord_cnt
    FROM    startdbmy.tr_ord t1
    WHERE   t1.shop_id IN ('S001','S002')
    AND     t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20230101','%Y%m%d')
    GROUP BY t1.shop_id
            ,CASE WHEN DATE_FORMAT(t1.ord_dtm,'%Y%m') <= '202206' THEN '상반기'
                  ELSE '하반기' END
    ORDER BY t1.shop_id, 반기구분;
    
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 6-2-4
------------------------------------------------------------------------

    SELECT  t1.ord_no ,t1.ord_dtm ,t1.shop_id ,t1.ord_amt
    FROM    startdbmy.tr_ord t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20211224','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20211225','%Y%m%d')
    ORDER BY CASE WHEN t1.shop_id = 'S010' THEN 1 ELSE 2 END
            ,t1.ord_no;