By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t as
select 1 as id, 1 as sid, '2020-01-12' as date union all
select 2 as id, 2 as sid, '2020-01-01' as date union all
select 3 as id, 1 as sid, '2019-12-31' as date union all
select 4 as id, 2 as sid, '2019-12-31' as date union all
select 5 as id, 1 as sid, '2019-12-31' as date union all
select 6 as id, 1 as sid, '2019-11-01' as date union all
select 7 as id, 3 as sid, '2019-11-01' as date union all
select 8 as id, 3 as sid, '2018-12-21' as date union all
select 9 as id, 2 as sid, '2018-12-21' as date
select t.*,
(dense_rank() over (partition by sid order by date) - 1) as previous_count
from t
order by id;
| id | sid | date | previous_count |
|---|---|---|---|
| 1 | 1 | 2020-01-12 | 2 |
| 2 | 2 | 2020-01-01 | 2 |
| 3 | 1 | 2019-12-31 | 1 |
| 4 | 2 | 2019-12-31 | 1 |
| 5 | 1 | 2019-12-31 | 1 |
| 6 | 1 | 2019-11-01 | 0 |
| 7 | 3 | 2019-11-01 | 1 |
| 8 | 3 | 2018-12-21 | 0 |
| 9 | 2 | 2018-12-21 | 0 |
select t.*, lag(cnt, 1, 0) over (partition by sid order by date)
from (select t.*,
count(*) over (partition by sid, date) as cnt
from t
) t
order by id;
| id | sid | date | cnt | lag(cnt, 1, 0) over (partition by sid order by date) |
|---|---|---|---|---|
| 1 | 1 | 2020-01-12 | 1 | 2 |
| 2 | 2 | 2020-01-01 | 1 | 1 |
| 3 | 1 | 2019-12-31 | 2 | 1 |
| 4 | 2 | 2019-12-31 | 1 | 1 |
| 5 | 1 | 2019-12-31 | 2 | 2 |
| 6 | 1 | 2019-11-01 | 1 | 0 |
| 7 | 3 | 2019-11-01 | 1 | 1 |
| 8 | 3 | 2018-12-21 | 1 | 0 |
| 9 | 2 | 2018-12-21 | 1 | 0 |