Untitled

CREATE OR REPLACE VIEW your_schema.your_view_name AS
WITH base_calendar_window AS (
    SELECT *
    FROM your_base_calendar_view
    ORDER BY as_of_date DESC
    LIMIT 60
),
with_seq AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY as_of_date DESC) AS day_seq
    FROM base_calendar_window
),
working_days_count AS (
    SELECT SUM(is_working_day) AS total_working_days FROM with_seq
),
with_cum AS (
    SELECT 
        ws.*,
        SUM(is_working_day) OVER (ORDER BY day_seq) AS cum_workdays_forward,
        wdc.total_working_days
    FROM with_seq ws
    CROSS JOIN working_days_count wdc
),
final AS (
    SELECT 
        *,
        CASE 
            WHEN is_working_day = 1 THEN 
                total_working_days - cum_workdays_forward + 1
            ELSE 
                NULL
        END AS temp_workday_seq
    FROM with_cum
)
SELECT 
    day_seq,
    as_of_date,
    is_working_day,
    MAX(temp_workday_seq) 
        OVER (ORDER BY day_seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS workday_seq
FROM final
ORDER BY day_seq;