Customer/Material pair to count the occurences in the past 3 years and set the status flag. – aws redshift

I have a table with the following values

date	customer name	material
9/1/2020	A	A1
8/1/2019	A	A2
8/1/2018	A	A2
9/2/2020	B	A1
9/2/2019	B	A2
9/2/2017	B	A2

So the output should be something like, if I could see a customer with a material, who has an entry in the past 3 years, then the status flag should be Yes, other wise the status flag should be no.

date	customer name	material	status
9/1/2020	A	A1	No
8/1/2019	A	A2	Yes
8/1/2018	A	A2	No
9/2/2020	B	A1	No
9/2/2019	B	A2	Yes
9/2/2017	B	A2	No

I was thinking about counting the given occurences of a customer/material pair, for the past 3 years, and check the count to set the status flag. But confused how I would use windowing function?

select customer_name, material, count(*) from table 
where datediff(year, date, current_date) <=3
group by customer_name, material

and then join this table, but I feel this approach is wrong as it does not account for rolling status.

Thank you!

Answer

Use window functions. Probably the simplest is lag():

select t.*,
       (case when lag(date) over (partition by customer, material order by date) >= date - interval '3 year'
             then 'Yes' else 'No'
        end) as flag
from t