Help calculating data when answers in previous row
(1) By ThanksRyan on 2021-07-29 06:00:46 [link]
Hi, **Question:** I would like to calculate the winAmount and POP for each of the betAmount values, but some of the data is only available in the 'pre purchase bal' and rest is available in 'completed final bal'. **Sample data:** ``` event,betAmount,winAmount,endBalance Purchase Started Pre Purchase Balance,0.25,, Game Completed Final Balance,,0.0,299.75 Purchase Started Pre Purchase Balance,0.25,, Game Completed Final Balance,,0.5,300.0 Purchase Started Pre Purchase Balance,0.25,, Game Completed Final Balance,,0.0,299.75 Purchase Started Pre Purchase Balance,1.0,, Game Completed Final Balance,,0.6,299.35 Purchase Started Pre Purchase Balance,1.0,, Game Completed Final Balance,,0.2,298.55 Purchase Started Pre Purchase Balance,1.0,, Game Completed Final Balance,,0.6,298.15 ``` **Query:** ``` SELECT purchasesbyba, wager, betamount, purchasesbyba, winamount, (winamount+ pw) / wager * 100.0 AS pop FROM ( SELECT event, count( CASE WHEN( event IS 'Game Completed Final Balance') THEN 1 ELSE NULL END) AS 'purchases', sum(mga.betamount) AS wager, sum(mga.winamount) AS winamount, sum(mga.progressivewin) AS pw, count( CASE WHEN( event IS 'Game Completed Final Balance' AND winamount != 0.0 ) THEN 1 ELSE NULL END) AS wins, betamount, count(betamount) purchasesbyba FROM mgaccounting AS mga GROUP BY betamount ); ``` **Results so far:** ``` purchasesbyba,wager,betamount,purchasesbyba,winamount,pop 0,,,0,6637.20000000004, 2236,559.0,0.25,2236,, 1141,570.5,0.5,1141,, 1316,1316.0,1.0,1316,, 1278,1917.0,1.5,1278,, 1123,2807.5,2.5,1123,, ``` Thank you for any pointers!
(2) By ThanksRyan on 2021-07-29 13:27:53 in reply to 1
A simpler question that may lead me on to the next path: How can I sum the winAmount and count(betAmount) for a given betAmount? Do I need to use window/lag functions for this?
(3) By Rico Mariani (rmariani) on 2021-07-30 13:59:07 in reply to 1 [link]
``` 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.
(4) By ThanksRyan on 2021-08-04 19:55:46 in reply to 3 [link]
Rico, This is beautiful. Thank you for your help and hand-holding through this exercise.
(5) By anonymous on 2021-08-04 22:43:36 in reply to 3 [link]
> `window win1 as (rows 1 preceding)` I think this is incorrect. The frame specification is not relevant for the `lag` function, which always uses the previous row, so "`rows 1 preceding`" is redundant. However, I think that you do need to specify the order. > `count(event IS 'Game Completed Final Balance')` This is not right either, I think. In this case, the `IS` expression results 0 or 1 (never null), so `count` will count all of the rows. What should be needed here is `sum` or `total`, not `count`.