select

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

length(data)-319 as length_contract,

tokenflow_eth.hextoint(substr(data,length_contract,64)) as sport_num

from

optimism.core.fact_event_logs

where contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

and (origin_function_signature = '0x8ec2c5a6' or origin_function_signature = '0xac2c957c')

,sport_info as (

select contract_of_game,

case

when right(sport_num,2) = '01' or right(sport_num,2) = '02' then 'Football'

when right(sport_num,2) = '03' then 'Baseball'

when right(sport_num,2) = '04' or right(sport_num,2) = '05' or right(sport_num,2) = '08' then 'Basketball'

when right(sport_num,2) = '06' then 'Hockey'

when right(sport_num,2) = '07' then 'MMA'

when right(sport_num,2) = '10' or right(sport_num,2) = '11' or right(sport_num,2) = '12' or right(sport_num,2) = '13' or

right(sport_num,2) = '14' or right(sport_num,2) = '15' or right(sport_num,2) = '16' then 'Soccer'

end as sport

from raw_data

),win_amount as (

SELECT TO_VARCHAR(event_inputs:account) as trader,

event_inputs:value/power(10,18) as amount_usd,

sport FROM optimism.core.fact_event_logs join sport_info on contract_address = contract_of_game

where

origin_function_signature = '0x85149258'

and block_timestamp >CURRENT_DATE - interval '14 days'

and event_inputs:value is not null

)

,spend as (

select

tx_hash,

sport from optimism.core.fact_token_transfers,sport_info

where to_address = contract_of_game and