calculating percent change month over month in SQL

Question

i want to query one table to find the sum of charges for a good within the months of Jan - May of 2018 - 2019. i know how to conditionally sum to get the charges broken out by year, but i'm unsure how to also build out a subquery to calculate the month over month change in sums between the years.

here's what my query looks like now:

    SELECT
    "Month Bought" AS MONTH,
    SUM(
        CASE
            WHEN "Bought Year" = '2018'
            THEN "total charges"
            ELSE 0
        END) AS Charge_2018,
    SUM(
        CASE
            WHEN "Bought Year" = '2019'
            THEN "total charges"
            ELSE 0
        END) AS Charge_2019
FROM
    Sales_agg
WHERE
    "Month Bought" IS NOT NULL
AND "Month Bought" between '1' and '5'
GROUP BY
    "Month Bought"
ORDER BY
    "Month Bought";

here's what the output looks like now:

Month | Charge_2018 | Charge_ 2019
1            5             7
2            7             8
3            7             10
4            6             8
5            5             6

here's what i'd like the output to look like:

Month | Charge_2018 | Charge_ 2019 | Mom_Change
1            5             7            0.XX
2            7             8            0.XX
3            7             10           0.XX
4            6             8            0.XX
5            5             6            0.XX

thanks so much for any help!

LAG will work. https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15 — Flat Cat, May 28 '20 at 14:30
is `mom_change` the percentual change between `charge_2018` for month 1 and `charge_2018` for month 2, for example? or is it `charge_2019` it's based upon? Or both? — marcothesane, May 28 '20 at 15:33
@marcothesane i would like mom_change to be the delta b/w month 1 in 2018 and month 1 in 2019. does that make sense? thank you! — mpc83, May 28 '20 at 15:37
That would then be year-over-year for that month, so `yoy_change` — marcothesane, May 28 '20 at 15:40

marcothesane · Answer 1 · 2020-05-28T15:56:07.543

Then it's this:

WITH
sales_agg(yr,mth,charge) AS (
          SELECT 2018,  1, 5 
UNION ALL SELECT 2018,  2, 7 
UNION ALL SELECT 2018,  3, 7 
UNION ALL SELECT 2018,  4, 6 
UNION ALL SELECT 2018,  5, 5 
UNION ALL SELECT 2019,  1, 7
UNION ALL SELECT 2019,  2, 8
UNION ALL SELECT 2019,  3,10
UNION ALL SELECT 2019,  4, 8
UNION ALL SELECT 2019,  5, 6 
)
SELECT
  mth
, SUM(CASE yr WHEN 2018 THEN charge END) AS charge_2018
, SUM(CASE yr WHEN 2019 THEN charge END) AS charge_2019
, ( 
    SUM(CASE yr WHEN 2019 THEN charge END) 
  / SUM(CASE yr WHEN 2018 THEN charge END)
  )::NUMERIC(5,3) - 1 AS yoy_change
FROM sales_agg
GROUP BY 1
ORDER BY 1;
-- out Time: First fetch (0 rows): 0.565 ms. All rows formatted: 0.580 ms
-- out  mth | charge_2018 | charge_2019 | yoy_change 
-- out -----+-------------+-------------+------------
-- out    1 |           5 |           7 |      0.400
-- out    2 |           7 |           8 |      0.143
-- out    3 |           7 |          10 |      0.429
-- out    4 |           6 |           8 |      0.333
-- out    5 |           5 |           6 |      0.200

calculating percent change month over month in SQL

1 Answers1