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,

tokenflow_eth.hextoint(substr(data,3+17*64,64)) as league_id,

case

when league_id in (9001, 9002) then 'American football'

when league_id = 9003 then 'Baseball'

when league_id in (9004, 9005, 9008) then 'Basketball'

when league_id = 9006 then 'Hockey'

when league_id = 9007 then 'AMM'

else 'Soccer' end as sports

from

optimism.core.fact_event_logs

where topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'

and contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

),

reward as(

select

origin_from_address,

concat(home_team, away_team) as game,

sports,

sum(event_inputs:value::float/1e18) as reward

from optimism.core.fact_event_logs join game_info on contract_address = game_contract

where

origin_function_signature = '0x85149258' and

event_name = 'OptionsExercised' and

tx_status = 'SUCCESS' and

block_timestamp >= current_date - 14

group by 1, 2, 3

),

all_bettors as(

select

from_address,

concat(home_team, away_team) as game,