SQLite Forum

Help calculating data when answers in previous row
Login

Help calculating data when answers in previous row

(1) By ThanksRyan on 2021-07-29 06:00:46 [source]

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 [link] [source]

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] [source]

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] [source]

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] [source]

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.