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!

Read more here:

Content Attribution

This content was originally published by Paras Pandey at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: