SELECT concat('0x',substr(data,27,40)) as game_contract

from

optimism.core.fact_event_logs

where

topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'

and BLOCK_TIMESTAMP::DATE>=CURRENT_DATE-14

),

code2 as (

select FROM_ADDRESS,TO_ADDRESS,RAW_AMOUNT, origin_from_address

from optimism.core.fact_token_transfers

where ORIGIN_TO_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'

and CONTRACT_ADDRESS='0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'

and FROM_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'

),

code3 as (

select sum (b.RAW_AMOUNT/1e18) as amount, count(distinct origin_from_address) as no_of_users, a.game_contract as contract

from code1 a JOIN code2 b on a.game_contract=b.TO_ADDRESS

group by 3

order by 1 DESC

),

code4 as (

SELECT

concat('0x',substr(data,27,40)) as game_contract

, HEX_DECODE_STRING( substr(data,3+13*64,64)) as home_team

, HEX_DECODE_STRING( substr(data,3+15*64,64) ) as away_team

from

optimism.core.fact_event_logs

where

topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'

and BLOCK_TIMESTAMP::DATE>=CURRENT_DATE-14

select c.amount,c.no_of_users, concat(d.home_team,d.away_team) as game,d.game_contract

from code3 c join code4 d on c.contract=d.game_contract

order by 2 DESC

limit 15