SQL Challenge 세션 #6 매출분석

240607 Today I Learn

Z-차트로 매출 추이 확인하기

💡 Z차트란?
매출분석을 위해 사용하는 차트로, 월별매출, 매출누계, 그리고 이동합계를 한번에 표시하는 그래프이다.

• 월별매출 (파랑)
• 매출누계 (주황) : 상대적으로 단기적 추이
• 이동합계 (회색) : 해당 월 기준 1년치 누적합계
  • 장기적 추이를 알 수 있음
  • 계절성, 프로모션 등 변동성을 완화해 추세를 볼 수 있음

Z-차트 그리기 (실습)

Sales_Transaction 테이블에서 호주, 이탈리아의 월별 매출,  매출 누계 구하기 

## 1. 호주, 이탈리아의 월별 매출,  매출 누계 구하기 
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;

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
        ,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;

📈 Z-차트 그리기

보통 SQL을 사용해서 차트를 그리는 경우에는, SQL로 데이터를 추출하고 이를 엑셀이타 다른 시각화 툴로 그래프화한다고 한다. 가장 접근하기 쉬운 엑셀을 활용해 추출된 데이터 중 이동 합계가 비교적 명확히 드러나는 '이탈리아'의 케이스를 차트로 그려봤는데, 위와 같은 모양이 나왔다.

정확한 z 모양의 차트를 구하려면 최근 2년치 데이터가 필요하다. 그러나 위 그래프는 보다시피 Z-차트라고 보기에는 아쉬운 모양이다. 그 이유는 데이터의 양이 부족했기 때문이다. 이탈리아의 경우 2018년 12월부터 2019년 12월까지 총 13개월의 데이터가 담겨있는데, 그러다보니 이동합계 측면에서 차이가 크지 않은것이다. 

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 country_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 ,dt_month;

YOY 분석하기

💡 YOY(Year-Over-Year Growth Formula)
전년대비 증감률으로 아래와 같이 계산할 수 있다.
→ (올해 매출 - 지난해 매출) / 지난해 매출 X 100
→ (Current Year Earnings — Last Year’s Earnings) / Last Year’s Earnings x 100

 

SQL Challenge 세션 #1 윈도우 함수, WITH절

→ LEAD, LAG 활용하기

Wayfair SQL Interview Question | DataLemur

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

코테 체험하기

 

Amazon SQL Interview Question | DataLemur

WITH product_spend2 AS (
    SELECT category,
           product,
          SUM(spend) AS total_spend
    FROM product_spend
    WHERE EXTRACT(YEAR FROM transaction_date)=2022
    GROUP BY category, product
    ),
    product_rank AS (
    SELECT category, product, total_spend,
           RANK() OVER(PARTITION BY category ORDER BY total_spend DESC) rank
    FROM product_spend2
    )
SELECT category, product, total_spend
FROM product_rank
WHERE rank <= 2;