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:
- Resets to 0 when winning after a loss, or losing after a win.
- Increments after a win, but not if it's case 1.
- 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.