------------------------------------------------------------------------
-- BOOSTER QUIZ 9-2-1
------------------------------------------------------------------------
SELECT t1.mbr_id ,t1.nick_nm ,t2.ord_amt_sum
FROM startdbmy.ms_mbr t1
INNER JOIN (
SELECT a.mbr_id
,SUM(a.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord a
WHERE a.ord_dtm >= STR_TO_DATE('20220301','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20220302','%Y%m%d')
GROUP BY a.mbr_id
) t2
ON (t1.mbr_id = t2.mbr_id)
WHERE t1.join_tp = 'SNS'
AND t1.mbr_gd = 'PLAT'
GROUP BY t1.mbr_id
ORDER BY t2.ord_amt_sum DESC;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-2-2
------------------------------------------------------------------------
SELECT t1.mbr_gd ,MAX(t1.nick_nm) nick_nm
,SUM(t2.ord_cnt) ord_cnt ,SUM(t2.ord_amt_sum) ord_amt_sum
FROM startdbmy.ms_mbr t1
INNER JOIN (
SELECT a.mbr_id ,COUNT(*) ord_cnt ,SUM(a.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord a
WHERE a.ord_dtm >= STR_TO_DATE('20210101','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20220101','%Y%m%d')
GROUP BY a.mbr_id
) t2
ON (t1.mbr_id = t2.mbr_id)
WHERE t1.join_dtm = STR_TO_DATE('20190323','%Y%m%d')
GROUP BY t1.mbr_gd
ORDER BY ord_amt_sum DESC;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-3-1
------------------------------------------------------------------------
SELECT t1.mbr_id ,t3.ord_cnt
FROM startdbmy.ms_mbr t1
INNER JOIN (
SELECT a.mbr_id ,COUNT(*) ord_cnt
FROM startdbmy.tr_ord a
INNER JOIN startdbmy.ms_shop b
ON (b.shop_id = a.shop_id)
WHERE a.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20200401','%Y%m%d')
AND b.shop_size < 50
GROUP BY a.mbr_id
) t3 ON (t3.mbr_id = t1.mbr_id)
WHERE t1.join_dtm = STR_TO_DATE('20190329','%Y%m%d')
ORDER BY t3.ord_cnt DESC;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-4-1
------------------------------------------------------------------------
SELECT t1.mbr_id ,t1.nick_nm ,IFNULL(t2.ord_amt_sum,0) ord_amt_sum ,IFNULL(t3.entry_cnt,0) entry_cnt
FROM startdbmy.ms_mbr t1
LEFT OUTER JOIN (
SELECT a.mbr_id ,SUM(a.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord a
WHERE a.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY a.mbr_id
) t2 ON (t2.mbr_id = t1.mbr_id)
LEFT OUTER JOIN (
SELECT a.mbr_id ,COUNT(*) entry_cnt
FROM startdbmy.tr_event_entry a
WHERE a.entry_dtm >= STR_TO_DATE('20200101','%Y%m%d')
AND a.entry_dtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY a.mbr_id
) t3 ON (t3.mbr_id = t1.mbr_id)
WHERE t1.join_dtm = STR_TO_DATE('20190327','%Y%m%d')
AND t1.mbr_gd = 'PLAT'
ORDER BY t1.mbr_id asc;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-6-1
------------------------------------------------------------------------
SELECT t1.ym ,t1.mbr_cnt ,t2.shop_cnt
FROM (
SELECT DATE_FORMAT(a.join_dtm,'%Y%m') ym
,COUNT(*) mbr_cnt
FROM startdbmy.ms_mbr a
WHERE a.join_dtm >= STR_TO_DATE('20220501','%Y%m%d')
AND a.join_dtm < STR_TO_DATE('20220701','%Y%m%d')
GROUP BY DATE_FORMAT(a.join_dtm,'%Y%m')
) t1
INNER JOIN(
SELECT SUBSTR(b.shop_start_ymd,1,6) ym
,COUNT(*) shop_cnt
FROM startdbmy.ms_shop b
WHERE 1=1
AND b.shop_start_ymd >= '20220501'
AND b.shop_start_ymd < '20220701'
GROUP BY SUBSTR(b.shop_start_ymd,1,6)
) t2
ON (t1.ym = t2.ym)
ORDER BY t1.ym;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-6-2
------------------------------------------------------------------------
SELECT t1.ym ,t1.mbr_cnt ,t2.shop_cnt ,t3.ord_cnt
FROM (
SELECT DATE_FORMAT(a.join_dtm,'%Y%m') ym
,COUNT(*) mbr_cnt
FROM startdbmy.ms_mbr a
WHERE a.join_dtm >= STR_TO_DATE('20220501','%Y%m%d')
AND a.join_dtm < STR_TO_DATE('20220701','%Y%m%d')
GROUP BY DATE_FORMAT(a.join_dtm,'%Y%m')
) t1
INNER JOIN(
SELECT SUBSTR(b.shop_start_ymd,1,6) ym
,COUNT(*) shop_cnt
FROM startdbmy.ms_shop b
WHERE 1=1
AND b.shop_start_ymd >= '20220501'
AND b.shop_start_ymd < '20220701'
GROUP BY SUBSTR(b.shop_start_ymd,1,6)
) t2
ON (t2.ym = t1.ym)
INNER JOIN (
SELECT DATE_FORMAT(c.ord_dtm,'%Y%m') ym, COUNT(*) ord_cnt
FROM startdbmy.tr_ord c
WHERE c.ord_dtm >= STR_TO_DATE('20220501','%Y%m%d')
AND c.ord_dtm < STR_TO_DATE('20220701','%Y%m%d')
GROUP BY DATE_FORMAT(c.ord_dtm,'%Y%m')
) t3
ON (t3.ym = t1.ym)
ORDER BY t1.ym;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-7-1
------------------------------------------------------------------------
SELECT a.shop_id ,SUM(a.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord a
WHERE a.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY a.shop_id
ORDER BY ord_amt_sum DESC
LIMIT 5;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-7-2
------------------------------------------------------------------------
SELECT DATE_FORMAT(t2.ord_dtm,'%Y%m') ord_ym
,SUM(t2.ord_amt) ord_amt_sum
FROM (
SELECT a.shop_id ,SUM(a.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord a
WHERE a.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY a.shop_id
ORDER BY ord_amt_sum DESC
LIMIT 5
) t1
INNER JOIN startdbmy.tr_ord t2
ON (t2.shop_id = t1.shop_id)
WHERE t2.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t2.ord_dtm < STR_TO_DATE('20230401','%Y%m%d')
GROUP BY DATE_FORMAT(t2.ord_dtm,'%Y%m')
ORDER BY ord_ym;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-7-3
------------------------------------------------------------------------
SELECT t1.shop_id
,MAX(t3.shop_nm) shop_nm
,MAX(t1.ord_amt_sum) ord_amt_2022
,SUM(t2.ord_amt) ord_amt_2023
FROM (
SELECT a.shop_id ,SUM(a.ord_amt) ord_amt_sum
FROM startdbmy.tr_ord a
WHERE a.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY a.shop_id
ORDER BY ord_amt_sum DESC
LIMIT 5
) t1
INNER JOIN startdbmy.tr_ord t2
ON (t2.shop_id = t1.shop_id)
INNER JOIN startdbmy.ms_shop t3
ON (t3.shop_id = t1.shop_id)
WHERE t2.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d')
AND t2.ord_dtm < STR_TO_DATE('20240101','%Y%m%d')
GROUP BY t1.shop_id
ORDER BY ord_amt_2022 DESC;
------------------------------------------------------------------------
-- BOOSTER QUIZ 9-7-4
------------------------------------------------------------------------
SELECT t1.mbr_id ,t3.nick_nm ,t1.ord_amt_202201 ,t2.ord_amt_202202
FROM (
SELECT a.mbr_id ,SUM(a.ord_amt) ord_amt_202201
FROM startdbmy.tr_ord a
WHERE a.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
AND a.ord_dtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY a.mbr_id
ORDER BY ord_amt_202201 DESC
LIMIT 3
) t1
INNER JOIN
(
SELECT b.mbr_id ,SUM(b.ord_amt) ord_amt_202202
FROM startdbmy.tr_ord b
WHERE b.ord_dtm >= STR_TO_DATE('20220201','%Y%m%d')
AND b.ord_dtm < STR_TO_DATE('20220301','%Y%m%d')
GROUP BY b.mbr_id
ORDER BY ord_amt_202202 DESC
LIMIT 3
) t2
ON (t2.mbr_id = t1.mbr_id)
INNER JOIN startdbmy.ms_mbr t3
ON (t3.mbr_id = t1.mbr_id)
;