with chrg as

(SELECT

a.BLOCK_TIMESTAMP as time,

TX_FROM as main_acc,

TX_TO as playing_acc,

AMOUNT/pow(10,DECIMAL) as vol,

case

when AMOUNT/pow(10,DECIMAL) = 0.00023 then '10k clicks'

when AMOUNT/pow(10,DECIMAL) = 0.00115 then '50k clicks'

when AMOUNT/pow(10,DECIMAL) = 0.0023 then '100k clicks'

end as "Charged amount",

a.tx_id as tx

from eclipse.core.fact_transfers a

join eclipse.core.fact_transactions b

on a.tx_id = b.tx_id

where

MINT like '%111111111111111111111111111111111111%'

and a.SUCCEEDED = 'TRUE'

and LOG_MESSAGES[0] like '%turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN%'

and LOG_MESSAGES[1] = 'Program log: Instruction: GrantClicker'),

x as

(SELECT

TX_TO,

sum(AMOUNT/pow(10,DECIMAL)) as amount_dep

from eclipse.core.fact_transfers a

join chrg b on a.TX_FROM = main_acc

where

MINT like '%111111111111111111111111111111111111%'

and a.SUCCEEDED = 'TRUE'

and TX_TO in (SELECT DISTINCT playing_acc from chrg )

group by 1

),