[DataLemur] YoY 구하기

https://datalemur.com/questions/yoy-growth-rate

Wayfair SQL Interview Question | DataLemur

 

테이블

 

 

문제

• 연도별/product별 매출액, 전년매출액, yoy rate를 구하기

 

쿼리 #1 (윈도우 함수 사용하기)

• CTE 테이블에서 year, product_id별 구매액 추출
• 바깥 쿼리에서 lag() 함수를 사용해 전년매출액, yoy_rate구하기

-- 연도별/product/매출/전년매출/yoy
with cte as (
    select date_part('year', transaction_date) as year, product_id, sum(spend) curr_year_spend
    from user_transactions
    group by 1,2)


select year, product_id, curr_year_spend, lag(curr_year_spend) over(partition by product_id order by year) as prev_year_spend,
        round((curr_year_spend-lag(curr_year_spend) over(partition by product_id order by year))*100.0/lag(curr_year_spend) over(partition by product_id order by year),2) as yoy_rate
from cte
order by product_id, year

 

 

 

쿼리 #2 (join 사용하기)

• CTE 테이블은 쿼리1과 동일
• join 시, (u1.year -1 = u2.year) 를 넣어,  u2 테이블에서는 전년 매출액 가져올 수 있도록 하기

with cte as (
    select date_part('year', transaction_date) as year, product_id, sum(spend) curr_year_spend
    from user_transactions
    group by 1,2)
    
select u1.year, u1.product_id, u1.curr_year_spend, u2.curr_year_spend as prev_year_spend, 
      round((u1.curr_year_spend - u2.curr_year_spend)*100.0/u2.curr_year_spend,2) yoy_rate
from cte u1
left join cte u2 
on u1.product_id = u2.product_id  and u1.year -1 = u2.year
order by product_id, year