------------------------------------------------------------------------
-- BOOSTER QUIZ 11-1-1
------------------------------------------------------------------------
ord_no shop_id ord_dtm ord_amt ord_amt_sum_ov ord_cnt_ov max_dtm_ov
------ ------- ------------------- -------- -------------- ---------- -------------------
124121 S092 2021-09-03 06:32:00 4500.000 17000.000 4 2021-09-10 06:31:00
124135 S092 2021-09-03 06:36:00 4000.000 17000.000 4 2021-09-10 06:31:00
125684 S092 2021-09-09 06:32:00 4000.000 17000.000 4 2021-09-10 06:31:00
125994 S092 2021-09-10 06:31:00 4500.000 17000.000 4 2021-09-10 06:31:00
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-1-2
------------------------------------------------------------------------
shop_id shop_nm chair_qty shop_cnt_ov chair_qty_sum_ov
------- ---------------- --------- ----------- ----------------
S014 Columbus-1st 23 3 77
S022 Los Angeles-2nd 20 3 77
S026 Philadelphia-2nd 34 3 77
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-1-3
------------------------------------------------------------------------
SELECT t1.ord_no ,t1.ord_dtm ,t1.mbr_id ,t1.ord_amt
,SUM(t1.ord_amt) OVER() ord_amt_sum_ov
,COUNT(*) OVER() ord_cnt_ov
FROM startdbmy.tr_ord t1
WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20220102','%Y%m%d')
AND t1.mbr_id = 'M0888'
ORDER BY t1.ord_no;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-1-4
------------------------------------------------------------------------
SELECT t2.* ,ROUND(t2.ord_amt / t2.ord_amt_sum_ov ,2) ord_amt_rat_sum
FROM (
SELECT t1.ord_no ,t1.ord_dtm ,t1.mbr_id ,t1.ord_amt
,SUM(t1.ord_amt) OVER() ord_amt_sum_ov
,COUNT(*) OVER() ord_cnt_ov
FROM startdbmy.tr_ord t1
WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20220102','%Y%m%d')
AND t1.mbr_id = 'M0888'
ORDER BY t1.ord_no
) t2;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-1-5
------------------------------------------------------------------------
SELECT t1.shop_id
,t1.shop_nm
,t1.chair_qty
,MAX(t1.chair_qty) OVER() chair_qty_max
,ROUND(t1.chair_qty / MAX(t1.chair_qty) OVER(), 2) chair_qty_rat_max
FROM startdbmy.ms_shop t1
WHERE t1.chair_qty >= 70
ORDER BY t1.chair_qty DESC;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-2-1
------------------------------------------------------------------------
ord_no shop_id ord_dtm ord_amt ord_dtm_rank_ov
------ ------- ------------------- -------- ---------------
127776 S261 2021-09-22 06:46:00 8500.000 5
127767 S261 2021-09-22 06:39:00 4000.000 4
127764 S261 2021-09-22 06:38:00 4000.000 3
127751 S261 2021-09-22 06:31:00 4000.000 2
123551 S261 2021-09-01 06:32:00 4000.000 1
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-2-2
------------------------------------------------------------------------
SELECT t1.mbr_id ,t1.nick_nm ,t1.join_dtm ,t1.mbr_gd
,RANK() OVER(ORDER BY t1.join_dtm ASC) join_dtm_rank_ov
FROM startdbmy.ms_mbr t1
WHERE t1.mbr_gd = 'PLAT'
AND t1.join_tp = 'INV'
AND t1.join_dtm >= STR_TO_DATE('20220401','%Y%m%d')
AND t1.join_dtm < STR_TO_DATE('20220501','%Y%m%d');
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-3-1
------------------------------------------------------------------------
SELECT t4.item_cat ,t5.item_cat_nm ,t4.ord_qty_sum
,RANK() OVER(ORDER BY t4.ord_qty_sum DESC) ord_qty_sum_rank_ov
FROM (
SELECT t3.item_cat
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t2.ord_no = t1.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY t3.item_cat
) t4
INNER JOIN startdbmy.ms_item_cat t5
ON (t5.item_cat = t4.item_cat);
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-3-2
------------------------------------------------------------------------
SELECT t3.shop_oper_tp
,(SELECT MAX(x.base_cd_nm)
FROM startdbmy.cm_base_cd x
WHERE x.base_cd_dv = 'shop_oper_tp'
AND x.base_cd = t3.shop_oper_tp) shop_oper_tp_nm
,t3.ord_amt_sum
,ROUND(t3.ord_amt_sum / SUM(t3.ord_amt_sum) OVER() * 100,2) ord_amt_sum_pct
FROM (
SELECT t1.shop_oper_tp
,SUM(t2.ord_amt) ord_amt_sum
FROM startdbmy.ms_shop t1
INNER JOIN startdbmy.tr_ord t2
ON (t2.shop_id = t1.shop_id)
WHERE t2.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND t2.ord_dtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY t1.shop_oper_tp
) t3
ORDER BY ord_amt_sum_pct DESC;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-4-1
------------------------------------------------------------------------
SELECT t1.mbr_id ,t1.ord_dtm ,t1.ord_amt
,SUM(t1.ord_amt) OVER(PARTITION BY t1.mbr_id) ord_amt_ov_mbr
FROM startdbmy.tr_ord t1
WHERE t1.mbr_id IN ('M0100','M0200','M0300')
AND t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230201','%Y%m%d')
ORDER BY t1.mbr_id ,t1.ord_dtm;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-4-2
------------------------------------------------------------------------
SELECT t1.mbr_id ,t1.ord_dtm ,t1.ord_amt
,SUM(t1.ord_amt) OVER(PARTITION BY t1.mbr_id) ord_amt_ov_mbr
,RANK() OVER(PARTITION BY t1.mbr_id ORDER BY t1.ord_amt DESC) ord_amt_rank_ov_mbr
FROM startdbmy.tr_ord t1
WHERE t1.mbr_id IN ('M0100','M0200','M0300')
AND t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230201','%Y%m%d')
ORDER BY t1.mbr_id ,t1.ord_dtm;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-4-3
------------------------------------------------------------------------
SELECT t1.ord_no ,t1.shop_id ,t1.ord_dtm ,t1.ord_amt
,SUM(t1.ord_amt) OVER(PARTITION BY DATE_FORMAT(t1.ord_dtm,'%Y%m')) ord_amt_sum_ov_ym
FROM startdbmy.tr_ord t1
WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230401','%Y%m%d')
AND t1.ord_amt >= 10000
AND t1.shop_id = 'S282'
ORDER BY t1.shop_id, t1.ord_dtm;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-4-4
------------------------------------------------------------------------
SELECT t1.ord_no ,t4.item_cat ,t4.item_cat_nm ,t3.item_nm ,DATE_FORMAT(t1.ord_dtm,'%H') ord_hour, t2.ord_qty
,SUM(t2.ord_qty) OVER(PARTITION BY DATE_FORMAT(t1.ord_dtm,'%H'), t4.item_cat) ord_qty_sum_ov_hour_cat
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t2.ord_no = t1.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
INNER JOIN startdbmy.ms_item_cat t4
ON (t4.item_cat = t3.item_cat)
WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20230102','%Y%m%d')
AND t1.ord_amt >= 10000
AND t1.shop_id IN ('S100')
AND t4.item_cat IN ('BKR','COF')
ORDER BY t1.shop_id ,ord_hour;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-4-5
------------------------------------------------------------------------
SELECT t3.*
,RANK() OVER(PARTITION BY t3.ord_ymd ORDER BY t3.ord_amt_sum DESC) ord_amt_rank_ov_ymd
FROM (
SELECT t2.shop_id ,MAX(t2.shop_nm) shop_nm ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd
,SUM(t1.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.ms_shop t2
ON (t2.shop_id = t1.shop_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20240101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20240104','%Y%m%d')
AND t2.shop_oper_tp = 'FLAG'
AND t2.shop_size >= 160
GROUP BY t2.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
ORDER BY t2.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
) t3;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-4-6
------------------------------------------------------------------------
SELECT *
FROM (
SELECT t3.*
,RANK() OVER(PARTITION BY t3.ord_ymd ORDER BY t3.ord_amt_sum DESC) ord_amt_rank_ov
FROM (
SELECT t2.shop_id ,MAX(t2.shop_nm) shop_nm ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd
,SUM(t1.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.ms_shop t2
ON (t2.shop_id = t1.shop_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20240101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20240104','%Y%m%d')
AND t2.shop_oper_tp = 'FLAG'
AND t2.shop_size >= 160
GROUP BY t2.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
ORDER BY t2.shop_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
) t3
) t4
WHERE t4.ord_amt_rank_ov = 1;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-4-7
------------------------------------------------------------------------
SELECT *
FROM (
SELECT DATE_FORMAT(t1.ord_dtm,'%Y') ord_yy ,t3.item_id
,MAX(t3.item_nm) item_nm
,SUM(t2.ord_qty) ord_qty_sum
,RANK() OVER(PARTITION BY DATE_FORMAT(t1.ord_dtm,'%Y') ORDER BY SUM(t2.ord_qty) DESC) ord_qty_sum_rank_ov
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t2.ord_no = t1.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20250101','%Y%m%d')
AND t3.item_cat = 'COF'
GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y') ,t3.item_id
) t4
WHERE t4.ord_qty_sum_rank_ov = 1;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-5-1
------------------------------------------------------------------------
ord_no shop_id ord_dtm ord_amt ord_amt_bf
------ ------- ------------------- --------- ----------
124121 S092 2021-09-03 06:32:00 4500.000 NULL
124135 S092 2021-09-03 06:36:00 4000.000 4500.000
125684 S092 2021-09-09 06:32:00 4000.000 4000.000
125994 S092 2021-09-10 06:31:00 4500.000 4000.000
126773 S092 2021-09-14 06:31:00 4500.000 4500.000
126781 S092 2021-09-14 06:38:00 14000.000 4500.000
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-5-2
------------------------------------------------------------------------
SELECT t5.item_cat
,t5.item_cat_nm
,t5.ord_ym
,t5.ord_amt_sum
,LAG(t5.ord_amt_sum) OVER(PARTITION BY t5.item_cat ORDER BY t5.ord_ym ASC) ord_amt_sum_bf_cat
,t5.ord_amt_sum
- LAG(t5.ord_amt_sum) OVER(PARTITION BY t5.item_cat ORDER BY t5.ord_ym ASC) ord_amt_sum_cat_diff
FROM (
SELECT t4.item_cat
,MAX(t4.item_cat_nm) item_cat_nm
,DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym
,SUM(t2.ord_qty * t2.sale_prc) ord_amt_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t2.ord_no = t1.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
INNER JOIN startdbmy.ms_item_cat t4
ON (t4.item_cat = t3.item_cat)
WHERE t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20220401','%Y%m%d')
GROUP BY t4.item_cat ,DATE_FORMAT(t1.ord_dtm,'%Y%m')
ORDER BY t4.item_cat ,DATE_FORMAT(t1.ord_dtm,'%Y%m')
) t5;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-6-1
------------------------------------------------------------------------
SELECT t4.item_id ,t4.item_nm ,t4.ord_ymd ,t4.ord_qty_sum
,SUM(t4.ord_qty_sum) OVER(ORDER BY t4.ord_ymd ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ord_qty_sum_run_total
FROM (
SELECT t3.item_id ,MAX(t3.item_nm) item_nm
,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t2.ord_no = t1.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20221220','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20221226','%Y%m%d')
AND t2.item_id = 'AMB'
GROUP BY t3.item_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
ORDER BY t3.item_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
) t4;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-6-2
------------------------------------------------------------------------
SELECT t4.item_id ,t4.item_nm ,t4.ord_ymd ,t4.ord_qty_sum
,SUM(t4.ord_qty_sum) OVER(PARTITION BY t4.item_id ORDER BY t4.ord_ymd ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ord_qty_sum_run_total_item
FROM (
SELECT t3.item_id ,MAX(t3.item_nm) item_nm
,DATE_FORMAT(t1.ord_dtm,'%Y%m%d') ord_ymd
,SUM(t2.ord_qty) ord_qty_sum
FROM startdbmy.tr_ord t1
INNER JOIN startdbmy.tr_ord_det t2
ON (t2.ord_no = t1.ord_no)
INNER JOIN startdbmy.ms_item t3
ON (t3.item_id = t2.item_id)
WHERE t1.ord_dtm >= STR_TO_DATE('20221220','%Y%m%d')
AND t1.ord_dtm < STR_TO_DATE('20221226','%Y%m%d')
AND t2.item_id IN ('AMB','IAMB')
GROUP BY t3.item_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
ORDER BY t3.item_id ,DATE_FORMAT(t1.ord_dtm,'%Y%m%d')
) t4;
------------------------------------------------------------------------
-- BOOSTER QUIZ 11-6-3
------------------------------------------------------------------------
SELECT t1.base_ymd
,IFNULL(t2.join_cnt,0) join_cnt
,MAX(IFNULL(t2.join_cnt,0)) OVER(ORDER BY t1.base_ymd ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) join_cnt_max_ov_10d
FROM startdbmy.cm_base_dt t1
LEFT OUTER JOIN (
SELECT DATE_FORMAT(a.join_dtm,'%Y%m%d') join_ymd
,COUNT(*) join_cnt
FROM startdbmy.ms_mbr a
WHERE a.join_dtm >= STR_TO_DATE('20220701','%Y%m%d')
AND a.join_dtm < STR_TO_DATE('20220801','%Y%m%d')
GROUP BY DATE_FORMAT(a.join_dtm,'%Y%m%d')
) t2
ON (t2.join_ymd = t1.base_ymd)
WHERE t1.base_ymd LIKE '202207%'
ORDER BY t1.base_ymd;