By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
create table t (day int, nickname text, result text);
insert into t values
(1,'john','lose'),
(3,'mark','draw'),
(2,'mark','win'),
(1,'mark','win'),
#(5,'richard','win'),
(4,'richard','win'),
(3,'richard','draw'),
(2,'richard','win'),
(1,'richard','win');
Records: 8 Duplicates: 0 Warnings: 0
select
nickname,
result,
count(1) over (partition by nickname order by day) -
count(1) over (partition by nickname, result order by day) streak_id
from t
| nickname | result | streak_id |
|---|---|---|
| john | lose | 0 |
| mark | draw | 2 |
| mark | win | 0 |
| mark | win | 0 |
| richard | draw | 2 |
| richard | win | 0 |
| richard | win | 0 |
| richard | win | 1 |
select nickname, result, count(*) as streak
from (
select
nickname,
result,
count(1) over (partition by nickname order by day) -
count(1) over (partition by nickname, result order by day) streak_id
from t
) results_with_streak_id
group by nickname, result, streak_id
| nickname | result | streak |
|---|---|---|
| john | lose | 1 |
| mark | draw | 1 |
| mark | win | 2 |
| richard | draw | 1 |
| richard | win | 2 |
| richard | win | 1 |