Y-on-Y Growth Rate

Y-on-Y Growth Rate

문제

1. 연도 오름차순, 제품ID, 해당 연도의 총 지출, 전년도의 총 지출, 전년 대비 성장률

 

풀이

with temp_01 as (
  SELECT 
    extract(year from transaction_date) as year_t,
    product_id,
    sum(spend) as curr_year_spend
  FROM user_transactions
  group by year_t, product_id
)
select 
  year_t as year,
  product_id,
  curr_year_spend,
  LAG(curr_year_spend) OVER (PARTITION BY product_id ORDER BY year_t) AS prev_year_spend,
  round((curr_year_spend - LAG(curr_year_spend) OVER (PARTITION BY product_id ORDER BY year_t)) 
  / LAG(curr_year_spend) OVER (PARTITION BY product_id ORDER BY year_t) * 100, 2) as yoy_rate
from temp_01
order by product_id asc

1️⃣ CTE temp_01: 연도별, 제품ID별 총 비용 계산

2️⃣ 이전 행(전년도): lag 사용, 제품ID 파티션, year_t 오름차순 값 가져오기

3️⃣ (이번년도 - 전년도) / 전년도 -> yoy 계산