Search

9. INLINE VIEW와 JOIN- BOOSTER QUIZ 참고 답안

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