如何编写统计连续工作日（Streak）的SQL查询？

我有一张名为MyDates的表，包含Id（INT）和MyDate（DATE）两列，表中可能存在同一日期的多条记录。

我需要统计从今日起倒推的连续工作日数量，工作日定义为周一至周五。尝试用CTE编写查询时遇到了困难。

假设今日为2023年4月27日，MyDates表包含以下7行数据：

1, 2023-04-17
2, 2023-04-21
3, 2023-04-24
4, 2023-04-24
5, 2023-04-25
6, 2023-04-26
7, 2023-04-27

我期望查询返回Streak = 5，原因是统计范围包含2023-04-27（周四）至2023-04-24（周一）的4个日期，加上2023-04-21（周五）；查询需忽略2023-04-22（周六）、2023-04-23（周日），同时忽略2023-04-24存在多条记录的情况。

以下是我尝试编写的SQL代码：

SELECT MIN(date) AS start_date, MAX(date) AS end_date, COUNT(*) AS streak_length
FROM (
  SELECT date, value, ROW_NUMBER() OVER (ORDER BY date) AS rn, 
         ROW_NUMBER() OVER (PARTITION BY value >= threshold, is_weekend ORDER BY date) AS grp
  FROM (
    SELECT date, value,
           CASE WHEN DAYOFWEEK(date) IN (1, 7) THEN 1 ELSE 0 END AS is_weekend
    FROM table_name
  ) sub
) sub
WHERE value >= threshold
GROUP BY value, grp - rn
ORDER BY start_date;

内容的提问来源于stack exchange，提问作者Rune Leknes