SQL 문제 풀이 (21번~23번)

문제 풀이 DB : https://thinking-it.tistory.com/12

 

[21번 문제]  지점별 가장 많은 매출을 올린 직원 구하기

SELECT store_code 사원명
     , emp_name 지점명
     , total 총매출
  FROM (
       SELECT d.*
            , RANK()OVER(PARTITION BY d.store_code
        ORDER BY d.store_code
            , d.total DESC) ranking
         FROM (
              SELECT a.store_code
              , a.emp_name
              , SUM(c.amount) total
           FROM burger_emp a
              , burger_ord b
              , burger_ord_item c
          WHERE a.store_code = b.store_code
            AND a.emp_code = b.emp_code
            AND c.store_code = b.store_code
            AND c.ord_code = b.ord_code
          GROUP BY a.store_code
              , a.emp_name
        ) d)
    WHERE ranking = 1;

※결과값※

 

[22번 문제] 지점별 최대 매출 일자와 해당일 매출 구하기

SELECT d.지점명
     , d.최대매출일자
     , d.해당일매출
  FROM (
       SELECT a.store_addr 지점명
            , b.ord_date 최대매출일자
            , SUM(c.amount) 해당일매출
            , ROW_NUMBER() OVER(PARTITION BY a.store_addr
        ORDER BY SUM(c.amount) DESC) e
         FROM burger_store a
         JOIN burger_ord b ON a.store_code = b.store_code
         JOIN burger_ord_item c ON b.ord_code = c.ord_code
        GROUP BY a.store_addr, b.ord_date
        ) d
    WHERE e = 1
    ORDER BY d.최대매출일자 DESC;

※결과값※

 

[23번 문제] 지점별 2017년 매달마다 이번달 매출과 지난달 매출을 구하고 증감값과 누적매출을 구하시오

--burger_ord와 burger_ord_item을 inner join 후 store_code와 ord_date를 group by
--기간은 2017%로 2017년도 전체 선택 후 date에 substr을 활용하여 년도와 월을 select
WITH 기간계산 AS (
                SELECT a.store_code
                     , SUBSTR(a.ord_date, 1, 4) || '_' || SUBSTR(a.ord_date, 5, 2) ord_month
                     , SUM(b.amount) amount
                  FROM burger_ord a
                 INNER JOIN burger_ord_item b ON a.store_code = b.store_code
                   AND a.ord_code = b.ord_code
                 WHERE a.ord_date LIKE ('2017%')
                 GROUP BY a.store_code, a.ord_date
                 ),
--기간계산의 store_code, ord_month를 group by 후 store_code, ord_month, 매출액 계산
매출계산 AS (
           SELECT store_code
                , ord_month
                , SUM(amount) amount
             FROM 기간계산
            GROUP BY store_code, ord_month
            ),
--점포별 매출액 계산
점포매출 AS (
           SELECT store_code
                , ord_month
                , NVL(amount, 0) amount
                , NVL(LAG(amount) OVER(PARTITION BY store_code ORDER BY ord_month), 0) pre_amount
                , NVL(SUM(amount) OVER(PARTITION BY store_code ORDER BY ord_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) acc_amount
             FROM 매출계산
            )
--점포별 월 매출액 계산
 SELECT a.store_code 지점코드
      , b.store_addr 지점명
      , a.ord_month 년월
      , a.amount "이번달 매출"
      , a.pre_amount "지날달 매출"
      , CASE WHEN a.pre_amount = 0 THEN 0 ELSE ROUND((a.amount - a.pre_amount) / a.pre_amount, 3) END INC_PER
      , a.acc_amount "누적 매출"
   FROM 점포매출 a
   LEFT OUTER JOIN burger_store b ON a.store_code = b.store_code
  ORDER BY a.store_code, a.ord_month;

※결과값※