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