Year-Over-Year Change or Previous Year Values in MySQL

Question

I have a table that looks something like this

Date	Number of products	Department	Sub-Department
01-01-2019	10	A	Ax
01-01-2019	4	A	Ay
02-01-2019	14	B	By
02-01-2019	15	A	Ay

The table contain data from 2018 to current date. For each date, there are multiple rows for each department and sub-department.

I want to create a column that will tell me the number of products from a year ago, grouped by date, department, sub-department and other columns that I have.

What is your expected result set for your example data? What have you tried so far? — David Denenberg, Jun 14 '21 at 13:52
@DavidDenenberg Expected result would be the exact same table with an extra column that has the value for previous year's number of products. I have been searching for solutions until now, but most of them have the data on a month or year level, so they use the lag window function to get the value. — noob5eva, Jun 14 '21 at 13:54
And what is your date format? I can't even tell what the data means. — Gordon Linoff, Jun 14 '21 at 14:28

score 0 · Answer 1 · answered Jun 14 '21 at 14:29

Assuming the first column is a date, you can use window functions:

select date, department, sub_department, num_products,
       max(num_products) over (partition by department, sub_department
                               order by date
                               range between interval 1 year preceding and interval 1 year preceding
                              ) as num_products_1_year_ago
from t;

Year-Over-Year Change or Previous Year Values in MySQL

1 Answers1