How to subset a SQL table using previous records condition?

Question

How can I query the table that ignores rows having consecutive 0 values in the last 4 weeks / records? This for example (Note that there can be other IDs too that might have records on the previous weeks):

Initial Table:

WEEK	ID	SALES
202001	1	0
202002	1	0
202003	1	0
202004	1	0
202005	1	3
202006	1	0
202007	1	0
202008	1	5

Output Table:

WEEK	ID	SALES
202005	1	3
202006	1	0
202007	1	0
202008	1	5

I use 2 tables here to pull the data. So far I've tried doing a 2 step code:

CREATE TABLE PRODUCT_SALES AS
SELECT 
B.WEEK_NBR,
A.ID
B.SALES
FROM 
PRODUCT_TABLE A,
SALES_TABLE B
WHERE 
A.ID= B.ID(+);

Then I try to get the minimum week where Sales = 0 but now I'm stuck here since after I get the min weeks for every product ID. I'm not sure what to do next or how it can be used to subset the main table.

SELECT 
(WEEK_NBR) MIN_WEEK_SALES,
ID,
SALES
FROM 
PRODUCT_SALES 
WHERE SALES = 0;

I was wondering if there's another way I can code this?

Answer 1

use lag() window function

with cte as (select
WEEK,SALES,
lag(SALES,1)over(order by w) as sales1,
lag(SALES,2)over(order by w) as sales2,
lag(SALES,3)over(order by w) as sales3,ID from t
) select WEEK,SALES,ID from cte where 
(coalesce(SALES,0)+coalesce(sales1,0)+coalesce(sales2,0)+coalesce(sales3,0) )<>0

demo link