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.

Answer 1

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;