SQL for Monthly YOY (percentage change while keeping the monthly data frequency)?-CodePudding

Suppose I have a table called monthly metrics. Sample row in table below:

EOM	BRAND	METRIC
1/31/2021	Nike	100
2/28/2021	Adidas	68
1/31/2022	Nike	110
2/28/2022	Adidas	68
...	...	...

How do I get:

EOM	BRAND	METRIC	YOY_CHANGE
1/31/2021	Nike	100	Null
1/31/2021	Adidas	68	Null
1/31/2022	Nike	110	10%
2/28/2022	Adidas	68	0
...	...	...	...

Would love something that works in Snowflake (SQL: ANSI), but welcome any general ideas.

CodePudding user response：

A CASE statement and LAG function can do this:

WITH data(EOM, BRAND, METRIC) AS (
    SELECT to_date(column1, 'mm/dd/yyyy'), column2, column3
    FROM VALUES
        ('1/31/2021','Nike',100),
        ('2/28/2021','Adidas',68),
        ('1/31/2022','Nike',110),
        ('2/28/2022','Adidas',68),
        ('2/08/2022','Tesla',0),
        ('2/08/2022','Tesla',99999)
)
SELECT 
    eom,
    brand,
    metric,
    lag(metric)over(partition by brand order by eom) as prior_metic,
    case 
        when prior_metic is null then null
        when prior_metic = metric then '0'
        when prior_metic = 0 then ' infinity'
        else round(((metric - prior_metic) / prior_metic)*100,0)::text || '%'
    end as YOY_CHANGE
FROM data
ORDER BY 1,2;

EOM	BRAND	METRIC	PRIOR_METIC	YOY_CHANGE
2021-01-31	Nike	100
2021-02-28	Adidas	68
2022-01-31	Nike	110	100	10%
2022-02-08	Tesla	0
2022-02-08	Tesla	99,999	0	infinity
2022-02-28	Adidas	68	68	0

If you want strictly ASNI then the reusing the prior_metric is not allowed so you an mash the LAG in N times, and hope the DB is smart enough..

SELECT 
    eom,
    brand,
    metric,
    case 
        when lag(metric)over(partition by brand order by eom) is null then null
        when lag(metric)over(partition by brand order by eom) = metric then '0'
        when lag(metric)over(partition by brand order by eom) = 0 then ' infinity'
        else round(((metric - lag(metric)over(partition by brand order by eom)) / lag(metric)over(partition by brand order by eom))*100,0)::text || '%'
    end as YOY_CHANGE
FROM data
ORDER BY 1,2;

Or use a CTE/Sub-select:

SELECT 
    eom,
    brand,
    metric,
    case 
        when prior_metric is null then null
        when prior_metric = metric then '0'
        when prior_metric = 0 then ' infinity'
        else round(((metric - prior_metric) / prior_metric)*100,0)::text || '%'
    end as YOY_CHANGE
FROM (
    SELECT 
        eom,
        brand,
        metric,
        lag(metric)over(partition by brand order by eom) as prior_metric
    FROM data
)
ORDER BY 1,2;