Get next row with clause A since last row with clause B

I have the following table:

id | customer_id | status            | updated_on
--------------------------------------------------
 1 | 1           | 'ok'              | 2021-03-01
 2 | 1           | 'update_needed'   | 2021-03-03
 3 | 1           | 'ok'              | 2021-03-04
 4 | 2           | 'ok'              | 2021-03-07
 5 | 1           | 'update_needed'   | 2021-03-11
 6 | 1           | 'request_info'    | 2021-03-17
 7 | 2           | 'request_info'    | 2021-03-17
 8 | 3           | 'ok'              | 2021-03-17
 9 | 4           | 'ok'              | 2021-04-25
10 | 2           | 'blocked'         | 2021-03-25
11 | 2           | 'request_info'    | 2021-03-29
12 | 4           | 'request_info'    | 2021-04-07

I am looking for a way to get all customers whose status is currently not ‘ok’ with an indication since when the status is not ‘ok’. The result should be:

customer_id | duration
-------------------------------------------
    1       | 29 days
    2       | 23 days
    4       | 2 days

My attempt is the following, but it gives incorrect values for customers that are currently ‘ok’.

SELECT
    customer_id,
    MIN(duration) AS duration
FROM (
    SELECT
        customer_id,
        status,
        updated_on,
        AGE(NOW(), LEAD(updated_on) OVER w) as duration
    FROM 
        customer_status
    WINDOW w AS (
        PARTITION BY customer_id
        ORDER BY updated_on
    )
    ORDER BY updated_on DESC
) x 
WHERE status = 'ok'
GROUP BY customer_id
ORDER BY duration;

The thing that I try to figure out is getting the first row for each customer with a not-‘ok’ status, since the last ‘ok’-status. How can I achieve this in Postgres?

Answer

You can assign a grouping to the rows based on the count of 'ok' statuses on or after each row.

Then, just filter where the count is 0 and aggregate:

select customer_id, current_date - min(updated_on) as days_not_ok
from (select cs.*,
             count(*) filter (where status = 'ok') over (partition by customer_id order by updated_on desc) as grp
      from customer_status cs
      where updated_on <= current_date
     ) cs
where grp = 0
group by customer_id;

Here is a db<>fiddle.