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

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]::string as action, split(VALUE:parsed,'_')[1]::float as amount, value as log, tx_id

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'),

hand_paper_ranked as (

select *,

row_number() over (partition by user order by BLOCK_TIMESTAMP) as row_num

from hand_paper_raw)

case when b.action='win' then '🟩 Win'

when b.action='draw' then '⬜ Draw'

else '🟥 Lose' end as result,

a.*,

case when b.action='win' then a.amount*2

when b.action='draw' then a.amount

else 0 end as received_amount

from hand_paper_ranked a

left join hand_paper_ranked b

on a.user=b.user and a.row_num=b.row_num-1 and b.event='result'

where a.event='play'

order by 2 desc