Starting and Ending a row-count based on values in another column

Question

There is a need to monitor the performance of a warehouse of goods. Please refer to the table containing data for one warehouse below:

WK_NO: Week number; Problem: Problem faced on that particular week. Empty cells are NULLs.

I need to create the 3rd column:

Weeks on list: A column indicating the number of weeks that a particular warehouse is being monitored as of that particular week.

Required Logic:

Initially the column's values are to be 0. If a warehouse is encountering problems continuously for 4 weeks, it is put onto a "list" and a counter starts, indicating the number of weeks the warehouse has been problematic. And if the warehouse is problem-free for 4 continuous weeks after facing problems, the counter resets to 0 and stays 0 until there is another 4 weeks of problems.

Code to generate data shown above:

CREATE TABLE warehouse (
  WK_NO INT NOT NULL,
  Problem STRING,
  Weeks_on_list_ref INT
);

INSERT INTO warehouse
    (WK_NO, Problem, Weeks_on_list_ref) 
VALUES 
    (1, NULL, 0),
    (2, NULL, 0),
    (3, 'supply', 0),
    (4, 'supply', 0),
    (5, 'manpower', 0),
    (6, 'supply', 0),
    (7, 'manpower', 1),
    (8, 'supply', 2),
    (9, NULL, 3),
    (10, NULL, 4),
    (11, 'supply', 5),
    (12, 'supply', 6),
    (13, 'manpower', 7),
    (14, NULL, 8),
    (15, NULL, 9),
    (16, NULL, 10),
    (17, NULL, 11),
    (18, NULL, 0),
    (19, NULL, 0),
    (20, NULL, 0);

Any help is much appreciated.

Update:

Some solutions are failing when bringing in data for multiple warehouses.

Updated the code generation script with W_NO which is the warehouse ID, for your consideration.

CREATE OR REPLACE TABLE warehouse (
  W_NO INT NOT NULL,
  WK_NO INT NOT NULL,
  Problem STRING,
  Weeks_on_list_ref INT
);

INSERT INTO warehouse
    (W_NO, WK_NO, Problem, Weeks_on_list_ref) 
VALUES 
    (1, 1, NULL, 0),
    (1, 2, NULL, 0),
    (1, 3, 'supply', 0),
    (1, 4, 'supply', 0),
    (1, 5, 'manpower', 0),
    (1, 6, 'supply', 0),
    (1, 7, 'manpower', 1),
    (1, 8, 'supply', 2),
    (1, 9, NULL, 3),
    (1, 10, NULL, 4),
    (1, 11, 'supply', 5),
    (1, 12, 'supply', 6),
    (1, 13, 'manpower', 7),
    (1, 14, NULL, 8),
    (1, 15, NULL, 9),
    (1, 16, NULL, 10),
    (1, 17, NULL, 11),
    (1, 18, NULL, 0),
    (1, 19, NULL, 0),
    (1, 20, NULL, 0),
    (2, 1, NULL, 0),
    (2, 2, NULL, 0),
    (2, 3, 'supply', 0),
    (2, 4, 'supply', 0),
    (2, 5, 'manpower', 0),
    (2, 6, 'supply', 0),
    (2, 7, 'manpower', 1),
    (2, 8, 'supply', 2),
    (2, 9, NULL, 3),
    (2, 10, NULL, 4),
    (2, 11, 'supply', 5),
    (2, 12, 'supply', 6),
    (2, 13, 'manpower', 7),
    (2, 14, NULL, 8),
    (2, 15, NULL, 9),
    (2, 16, NULL, 10),
    (2, 17, NULL, 11),
    (2, 18, NULL, 0),
    (2, 19, NULL, 0),
    (2, 20, NULL, 0);

Answer 1

Consider below query for updated question:

SELECT W_NO, WK_NO, Problem, IF(MOD(div, 2) = 0, 0, RANK() OVER (PARTITION BY W_NO, div ORDER BY WK_NO)) AS Weeks_on_list
  FROM (
    SELECT *, COUNTIF(flag IS TRUE) OVER (PARTITION BY W_NO ORDER BY WK_NO) AS div FROM (
      SELECT *,
             LAG(Problem, 5) OVER w0 IS NULL AND COUNT(Problem) OVER w1 = 4 OR
             LAG(Problem, 5) OVER w0 IS NOT NULL AND COUNT(Problem) OVER w1 = 0 AS flag
        FROM warehouse
      WINDOW w0 AS (PARTITION BY W_NO ORDER BY WK_NO), w1 AS (w0 ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)
  )
)
ORDER BY W_NO, WK_NO;

Answer 2

Consider below query:

1. Using a window frame with fixed size 4, find boundaries first where warehouse turns into abnormal state and vice versa in innermost query.
2. Partition weeks using boundaries found in step 1.
3. Since normal and abnormal states take turns, so calculate RANK() only for abnormal state in outermost query.

SELECT WK_NO, Problem, IF(MOD(div, 2) = 0, 0, RANK() OVER (PARTITION BY div ORDER BY WK_NO)) AS Weeks_on_list
  FROM (
    SELECT *, COUNTIF(flag IS TRUE) OVER (ORDER BY WK_NO) AS div FROM (
      SELECT *,
             LAG(Problem, 5) OVER w0 IS NULL AND COUNT(Problem) OVER w1 = 4 OR
             LAG(Problem, 5) OVER w0 IS NOT NULL AND COUNT(Problem) OVER w1 = 0 AS flag
        FROM warehouse
      WINDOW w0 AS (ORDER BY WK_NO), w1 AS (w0  ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)
  )
)
ORDER BY WK_NO;