desired_rank

WITH datasource (id, date, status) AS (
  SELECT
    1,
    '2024-01-15',
    'a'
  UNION ALL
  SELECT
    1,
    '2024-01-16',
    'g'
  UNION ALL
  SELECT
    1,
    '2024-01-17',
    'e'
  UNION ALL
  SELECT
    1,
    '2024-01-18',
    'done'
  UNION ALL
  SELECT
    1,
    '2024-01-19',
    'f'
  UNION ALL
  SELECT
    1,
    '2024-01-20',
    'r'
),
rownumbers AS (
  SELECT
    id,
    date,
    status,
    Row_number() OVER (
      partition BY id
      ORDER BY
        date
    ) AS rn
  FROM
    datasource
),
withnewpartitionkey AS (
  SELECT
    r1.id,
    r1.date,
    r1.status,
    r1.rn AS rn,
    r2.rn AS rn_done,
    CASE WHEN (
      r1.rn - COALESCE(r2.rn, 0)
    ) > 0 THEN 1 WHEN (
      r1.rn - COALESCE(r2.rn, 0)
    ) < 0 THEN -1 ELSE 0 END AS np_key
  FROM
    rownumbers r1
    LEFT JOIN (
      SELECT
        id,
        rn
      FROM
        rownumbers
      WHERE
        status = "done"
    ) r2 ON r1.id = r2.id
)
SELECT
  id,
  date,
  status,
  Rank() OVER (
    partition BY id,
    np_key
    ORDER BY
      id,
      date
  ) AS desired_rank
FROM
  withnewpartitionkey