select BLOCK_TIMESTAMP, SIGNERS[0]::string as user, 'play' as event, split(VALUE:parsed,'_')[0] as action, split(VALUE:parsed,'_')[1]::float as amount, value as log

from eclipse.core.fact_transactions,

table(FLATTEN(input => INSTRUCTIONS)) as flattened

where ACCOUNT_KEYS[1]:pubkey='BGh13zVibtk3kge1K3u8kTbfk4Zyqmf7coqm8YoU6Wio'

and SIGNERS[1] is null

and VALUE:parsed IS NOT NULL

and VALUE:parsed:info IS NULL

and BLOCK_TIMESTAMP::date>='2024-12-20'

union all

select BLOCK_TIMESTAMP, SIGNERS[0]::string as user, 'result' as event, split(VALUE:parsed,'_')[0] as action, split(VALUE:parsed,'_')[1]::float as amount, value as log

from eclipse.core.fact_transactions,

table(FLATTEN(input => INSTRUCTIONS)) as flattened

where SIGNERS[1]='BGh13zVibtk3kge1K3u8kTbfk4Zyqmf7coqm8YoU6Wio'

and VALUE:parsed IS NOT NULL

and VALUE:parsed:info IS NULL

and BLOCK_TIMESTAMP::date>='2024-12-20')

select user,

sum(case when event='play' then 1 else 0 end) as plays,

sum(case when event='result' and action='draw' then 1 else 0 end) as draws,

sum(case when event='result' and action='win' then 1 else 0 end) as wins,

plays-draws-wins as loses, round(100*DIV0(wins,plays),2) as win_rate,

sum(case when event='play' then amount else 0 end) as paid,

sum(case when event='result' then amount else 0 end) as paid_back,

paid_back-paid as net_gain

from hand_paper_raw

group by 1

order by 2 desc