Untitled
unknown
plain_text
15 days ago
1.1 kB
4
Indexable
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;
Editor is loading...
Leave a Comment