Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table matches (player text, dt date,  
is_winner boolean, expected integer );

insert into matches values
('A', '2019-01-01', TRUE, 0),
('A', '2019-01-03', TRUE, 1),
('A', '2019-01-04', TRUE, 2),
('A', '2019-01-09', FALSE, 0),
('A', '2019-01-10', FALSE, -1),
('A', '2019-01-11', FALSE, -2),
('A', '2019-01-15', TRUE, 0);
7 rows affected

select * from matches;


player
dt
is_winner
expected


A
2019-01-01
t
0

A
2019-01-03
t
1

A
2019-01-04
t
2

A
2019-01-09
f
0

A
2019-01-10
f
-1

A
2019-01-11
f
-2

A
2019-01-15
t
0


with lags as ( select player, dt, is_winner, 
lag(is_winner) OVER (partition by player ORDER BY dt ASC) as prev_is_winner,
expected 
from matches
),
group_changes as (
select lags.*,case when prev_is_winner<>is_winner or prev_is_winner is null then 1 else 0 end as is_new_group
from lags
),
groups_numbered as (
select *, sum(is_new_group) over(partition by player order by dt, is_winner desc) as streak_group
from group_changes
),
expected_in_groups as (
select groups_numbered.*, row_number() over (partition by player,streak_group order by dt asc, streak_group asc) -1 as expected_unsigned
from groups_numbered
)
select expected_in_groups.*, case when is_winner='t' then expected_unsigned else expected_unsigned*-1 end as actual
from expected_in_groups
order by player asc, dt asc ; 


player
dt
is_winner
prev_is_winner
expected
is_new_group
streak_group
expected_unsigned
actual


A
2019-01-01
t
null
0
1
1
0
0

A
2019-01-03
t
t
1
0
1
1
1

A
2019-01-04
t
t
2
0
1
2
2

A
2019-01-09
f
t
0
1
2
0
0

A
2019-01-10
f
f
-1
0
2
1
-1

A
2019-01-11
f
f
-2
0
2
2
-2

A
2019-01-15
t
f
0
1
3
0
0

player	dt	is_winner	expected
A	2019-01-01	t	0
A	2019-01-03	t	1
A	2019-01-04	t	2
A	2019-01-09	f	0
A	2019-01-10	f	-1
A	2019-01-11	f	-2
A	2019-01-15	t	0

player	dt	is_winner	prev_is_winner	expected	is_new_group	streak_group	expected_unsigned	actual
A	2019-01-01	t	null	0	1	1	0	0
A	2019-01-03	t	t	1	0	1	1	1
A	2019-01-04	t	t	2	0	1	2	2
A	2019-01-09	f	t	0	1	2	0	0
A	2019-01-10	f	f	-1	0	2	1	-1
A	2019-01-11	f	f	-2	0	2	2	-2
A	2019-01-15	t	f	0	1	3	0	0