------------------------------------------------------------------------
-- 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;
SQL
복사