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 row_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.as_of_date,
        ws.is_working_day,
        ws.row_seq,
        wdc.total_working_days,
        SUM(ws.is_working_day) OVER (ORDER BY ws.row_seq) AS cum_workdays_forward
    FROM with_seq ws
    CROSS JOIN working_days_count wdc
),
final AS (
    SELECT 
        as_of_date,
        is_working_day,
        row_seq,
        total_working_days,
        cum_workdays_forward,
        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 
    row_seq AS day_seq,
    as_of_date,
    is_working_day,
    MAX(temp_workday_seq) OVER (
        ORDER BY row_seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS workday_seq
FROM final
ORDER BY row_seq;