问题中所示的仅选择2行的逻辑没有解释，但似乎是当活动条纹为0时，而且下一个活动条纹不是零（或NULL）。因此，考虑到这个逻辑，我可以计算日期范围，一旦确定了这些日期范围，我就可以定位这些日期边界内的行来计算聚合：

WITH cte1
AS (
    SELECT LOGIN
        , Week_date
        , Active_Streak
        , Inactive_Streak
        , Amount
        , LEAD(Active_Streak) OVER (
            PARTITION BY LOGIN ORDER BY Week_date
            ) AS Next_Active_Streak
        , MIN(Week_date) OVER () AS min_date
    FROM your_table_name
    )
    , cte2
AS (
    SELECT LOGIN
        , COALESCE(LAG(Week_date) OVER (
                PARTITION BY LOGIN ORDER BY Week_date
                ), min_date) AS From_Date
        , Week_date
        , inactive_Streak
    FROM cte1
    WHERE Active_Streak = 0
        AND Inactive_Streak > 0
        AND (
            Next_Active_Streak <> 0
            OR Next_Active_Streak IS NULL
            )
    )
SELECT cte2.LOGIN
    , cte2.week_date
    , MAX(t.Active_Streak) Active_Streak
    , MAX(cte2.inactive_Streak) inactive_Streak
    , AVG(t.amount) AS avg_amount
FROM cte2
LEFT JOIN your_table_name AS t ON cte2.LOGIN = t.LOGIN
    AND t.week_date > cte2.from_date AND t.week_date < cte2.week_date
    AND Amount IS NOT NULL
GROUP BY cte2.LOGIN
    , cte2.week_date

fiddle

在PostgreSQL上，我会这样做：

create table testdata (
Login varchar(50),
Week_date date,
Active_Streak integer,
Inactive_Streak integer,
Amount integer);

insert into testdata values ('abc', '2022/01/19', 1, 0, 4);
insert into testdata values ('abc', '2022/01/25', 2, 0, 6);
insert into testdata values ('abc', '2022/02/01', 3, 0, 9);
insert into testdata values ('abc', '2022/02/08', 4, 0, 2);
insert into testdata values ('abc', '2022/02/15', 0, 1, NULL);
insert into testdata values ('abc', '2022/02/22', 1, 0, 6);
insert into testdata values ('abc', '2022/03/01', 2, 0, 11);
insert into testdata values ('abc', '2022/03/08', 0, 1, NULL);
insert into testdata values ('abc', '2022/03/15', 0, 2, NULL);
insert into testdata values ('abc', '2022/01/22', 1, 0, 4);

select * from testdata order by week_date;

 login | week_date  | active_streak | inactive_streak | amount 
-------+------------+---------------+-----------------+--------
 abc   | 2022-01-19 |             1 |               0 |      4
 abc   | 2022-01-22 |             1 |               0 |      4
 abc   | 2022-01-25 |             2 |               0 |      6
 abc   | 2022-02-01 |             3 |               0 |      9
 abc   | 2022-02-08 |             4 |               0 |      2
 abc   | 2022-02-15 |             0 |               1 |   NULL
 abc   | 2022-02-22 |             1 |               0 |      6
 abc   | 2022-03-01 |             2 |               0 |     11
 abc   | 2022-03-08 |             0 |               1 |   NULL
 abc   | 2022-03-15 |             0 |               2 |   NULL

现在，查询：

with T as (
    select t1.week_date t1date, t1.amount, t2.week_date t2date
    from testdata t1
    left outer join testdata t2
    on t1.week_date < t2.week_date
    where t2.inactive_streak > 0)
select t2date, avg(amount)
from T
where t1date >= (select coalesce(max(t2date), '1901/01/01')
                  from T as x
                  where x.t2date < T.t2date)
group by t2date;

它返回：

t2date   |        avg         
------------+--------------------
 2022-02-15 | 5.0000000000000000
 2022-03-08 | 8.5000000000000000
 2022-03-15 |               NULL