---

개요

Z 차트 만들기, YOY 분석

+) LEAD, LAG 함수, 순위 함수 복습

---

📌 Z 차트

Z차트 : 매출을 분석하기 위해 사용

• 월별 매출 (파랑)
• 매출 누적 합계 (주황)
  • 상대적으로 단기적 추이
• 이동 합계 (회색)
  • 이동 합계 = 기준 월의 매출액 + 기준 월 전 11개월치 매출액 합계
    • 해당 월 기준으로 최근 1년치 누적 합계라고도 생각할 수 있다.
    • 이동 합계를 구하는 이유
      • 장기적 추이를 알 수 있고,
      • 계절성, 프로모션 등의 변동성을 완화해 추세를 볼 수 있다.

---

📌 월별 매출, 매출 누계 구하기

1. 호주, 이탈리아의 월별 매출, 매출 누계 구하기
   • 누계 : 누적합 (cumulative sum)
     목표 대비 실적 확인, 트렌드 파악, 예산 계획 등을 위해 누적합 계산이 많이 사용된다.

문제
sales_transaction 테이블에서 호주, 이탈리아의 월별 매출, 매출 누계를 구하는 쿼리 작성
(소수점 둘째 짜리까지 구하고 국가와 날짜 순으로 오름차순 정렬)

답

SELECT   Country 
       , DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m') AS dt_month 
       , SUM(Price*Quantity) as monthly_sales  
       , ROUND(
          SUM(SUM(Price*Quantity)) OVER(PARTITION BY Country ORDER BY 
                                 DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
              , 2)  AS country_cumsum                           
FROM SALES_TRANSACTION ST
WHERE Country IN ('Australia','Italy')
GROUP BY Country,dt_month 
ORDER BY Country,dt_month 

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 는 디폴트 값이지만,
  DBMS에 따라 디폴트 값이 다름으로 명시적으로 표시해주시는 게 더 좋다.

---

📌 월별 매출, 매출 누계, 이동 합계 구하기

2. 호주, 이탈리아의 월별 매출, 매출 누계, <이동 합계> 구하기
   • 이동 합계 : 기준 월의 매출액 + 기준 월 전 11개월치 매출액 합계
   • 정확히 값을 구하기 위해서는 최근 2년치 데이터가 필요

문제
sales_transaction 테이블에서 호주,이탈리아의 월별 매출, 매출 누계, 이동 합계를 구하는 쿼리 작성
(소수점 둘째짜리까지 구하고 국가와 날짜순으로 오름차순 정렬)

답

SELECT   
	Country 
, DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m') AS dt_month 
, SUM(Price*Quantity) as monthly_sales  
, ROUND(
    SUM(SUM(Price*Quantity)) OVER(PARTITION BY Country ORDER BY 
                              DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
      , 2)  AS monthly_cumsum       
, ROUND(
     SUM(SUM(Price*Quantity)) OVER(PARTITION BY Country ORDER BY 
                               DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
                               ROWS BETWEEN 11 PRECEDING AND CURRENT ROW )
      , 2)  AS moving_cumsum             
FROM SALES_TRANSACTION ST
WHERE Country IN ('Australia','Italy')
GROUP BY Country,dt_month 
ORDER BY Country,dt_month 

---

📌 조건부 월별 매출, 매출 누계, 이동 합계 구하기

3. 이탈리아의 제품 이름별 월별 매출, 매출 누계, 이동 합계 구하기

문제
sales_transaction 테이블에서 이탈리아의 제품 이름별 월별 매출, 매출 누계, 이동 합계를 구하는 쿼리 작성
(소수점 둘째짜리까지 구하고 제품 이름과 날짜순으로 오름차순 정렬)

답

SELECT
     ProductName 
   , DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m') AS dt_month 
   , SUM(Price*Quantity) as monthly_sales  
   , ROUND(
        SUM(SUM(Price*Quantity)) OVER(PARTITION BY ProductName  ORDER BY 
                                  DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
         , 2)  AS monthly_cumsum       
    , ROUND(
        SUM(SUM(Price*Quantity)) OVER(PARTITION BY ProductName  ORDER BY 
                                  DATE_FORMAT(STR_TO_DATE(Date,'%m/%d/%Y'),'%Y-%m')
                                  ROWS BETWEEN 11 PRECEDING AND CURRENT ROW )
         , 2)  AS moving_cumsum             
FROM SALES_TRANSACTION ST 
WHERE Country = 'Italy'
GROUP BY ProductName ,dt_month 
ORDER BY ProductName 

---

📌 YOY 분석

---

📌 YOY

YOY 란?

• 전년 대비 (YEAR OVER YEAR)
  (올해 매출 - 지난해 매출) / 지난해 매출 X 100

---

📌 LEAD, LAG

LEAD
이후 N 행의 값을 가져오는 함수. 기본값은 1 (N번 리드하는 것)

• 문법 :

LEAD(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 
OR
LEAD(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 

LAG
이전 N 행의 값을 가져오는 함수. 기본값은 1 (N번 지연하는 것)

• 문법 :

LAG(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 
OR
LAG(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 

---

📌 기출 문제 적용

문제
다양한 제품에 대한 Wayfair 사용자 거래에 대한 정보가 포함된 테이블이 있다고 가정
결과를 product_id 별로 그룹화하여 각 제품의 총 지출에 대한 전년 대비 성장률을 계산하는 쿼리 작성
(연도 오름차순, product ID, 올해 지출, 전년도 지출 및 전년 대비 성장률이 소수점 이하 2자리로 반올림 포함)

답

WITH sum_spend AS(
  SELECT EXTRACT(YEAR FROM transaction_date) AS year
        ,product_id 
        ,SUM(spend) AS curr_year_spend
  FROM user_transactions
  GROUP BY year,product_id 
  ORDER BY product_id,year
),pr AS (
  SELECT 
     year
    ,product_id
    ,curr_year_spend
    ,LAG(curr_year_spend) OVER(PARTITION BY product_id ORDER BY year)
    AS prev_year_spend 
  FROM sum_spend
)
SELECT 
   year
  ,product_id
  ,curr_year_spend
  ,prev_year_spend
  ,ROUND(
    (curr_year_spend - prev_year_spend)/prev_year_spend*100.0
    ,2)
   AS yoy_rate 
FROM pr 

---

📌 매출 데이터로 SQL 코딩 테스트

문제
Amazon 인터뷰 (모의 코테)

답

WITH sum_spend AS(
  SELECT category
        ,product 
        ,SUM(spend) AS total 
  FROM product_spend 
  -- PostgreSQL
  WHERE EXTRACT(YEAR FROM transaction_date)=2022
  -- MySQL
  WHERE YEAR(transaction_date)=2022
  GROUP BY category, product 
), CTE AS( 
   SELECT category 
         ,product 
         ,total 
         ,RANK() OVER(PARTITION BY category ORDER BY total DESC) AS rnk 
   FROM sum_spend
)   
SELECT category
     , product 
     ,total AS total_spend
FROM CTE 
WHERE rnk <= 2

---

+) 순위 함수 복습

💡 ROW_NUMBER( ) : 어떻게든 순위 정해줌, 중복 순위 X / EX) 1>2>3>4

• 기억 Tip : ROW(줄)을 설 때는 항상 순서가 있다!
  동시에 도착해도 일렬로 서면 숫자가 생기기 마련!

💡 RANK( ): 중복 순위 O / EX) 1>1>3>4>4>5

• 기억 Tip : RANK(순위)를 정하는데 동점자면 공동 1등을 줄 수 밖에 없다!

💡 DENSE_RANK( ): 중복 순위 O, 빠지는 숫자 없음 / EX) 1>1>2>3>3>3

• 기억 Tip : RANK(순위)를 정하는데 동점자면 공동 1등을 줄 수 밖에 없다!
  그런데 DENSE(밀집)되어있으니까 빠지는 순위가 없게 한다!