```
create table data (
type text,
betAmount real,
winAmount real,
endBalance real
);
delete from data;
insert into data values
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.0,299.75),
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.5,300.0),
('Purchase Started Pre Purchase Balance',0.25,NULL,NULL),
('Game Completed Final Balance',NULL,0.0,299.75),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.6,299.35),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.2,298.55),
('Purchase Started Pre Purchase Balance',1.0,NULL,NULL),
('Game Completed Final Balance',NULL,0.6,298.15);
-- assumes it's always pairs
with fuse2(t1, b1, w1, e1, t2, b2, w2, e2) as (
select
type,
betAmount,
winAmount,
endBalance,
lag(type) OVER win1,
lag(betAmount) OVER win1,
lag(winAmount) OVER win1,
lag(endBalance) OVER win1
from data
window win1 as (rows 1 preceding)
),
unified_rows(bet, win, end) as (
select b2, w1, e1 from fuse2
where fuse2.t1 = 'Game Completed Final Balance'
)
select bet, count(bet), sum(win) from unified_rows
group by bet;
```
Results:
```
0.25|3|0.5
1.0|3|1.4
```
Note: this kind of thing
```
count(
CASE
WHEN (event IS 'Game Completed Final Balance') THEN 1
ELSE NULL
END)
```
Is all kinds of redundant. Any time you have a CASE expression where the results are 0 or 1 you can pretty much replace it with an equivalent boolean that's more economical.
You never have to write `ELSE NULL` that's the default value if there is no `ELSE`.
The above could simply be
```
count(event IS 'Game Completed Final Balance')
```
But in any case I don't think you need to go there, see above.