Etydhv

I need to show the running win/loss streak per row in a query, so given the table below, the query should return the "expected" column. I've tried some approaches with window functions, but no success.

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-15', TRUE, 0);

player dt is_winner expected
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-15 true 0

The logic is:

1. Resets to 0 when winning after a loss, or losing after a win.


2. Increments after a win, but not if it's case 1.


3. Decrements after a loss, but not if it's case 1.

Any insights on how to tackle this are welcome. My last resort would be a function with a loop called by every row.

I've done this in stages using CTEs so that you can see how it's done as the queries progress. Each CTE adds a column in the output in order to show you progress.

It's pretty much self-documenting with the CTE names, to be honest.

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;

DB Fiddle Link (I added an extra row just to make sure it was working at a certain point)

Basically:

1. 
   lags CTE: use LAG() to get the previous result relative to the current row.


2. 
   group_changes CTE: Detect whether the previous streak, whether win or loss, has ended


3. 
   groups_numbered CTE: Give each streak a number


4. 
   expected_in_groups CTE: Number the rows in the group


5. Final select: negate the loss streaks

.

+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
| player | dt | is_winner | prev_is_winner | expected | is_new_group | streak_group | expected_unsigned | actual |
+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+
| A | 2019-01-01 | t | | 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 |
+--------+------------+-----------+----------------+----------+--------------+--------------+-------------------+--------+