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

,ethereum.public.udf_hex_to_int( substr(data,3+17*64,64) )-9000 as SportID,

case when SportID = 1 then 'NCAA - American Footbal '

when SportID = 2 then 'NFL - American Footbal '

when SportID = 3 then 'MLB - Baseball '

when SportID = 4 then 'NBA - Basketball'

when SportID = 5 then 'NCAA - Basketball'

when SportID = 6 then 'NHL - Hockey '

when SportID = 7 then 'MMA'

when SportID = 8 then 'WNBA'

when SportID = 10 then 'MLS - Soccer'

when SportID = 11 then 'EPL - Soccer'

when SportID = 12 then 'Ligue 1 - Soccer'

when SportID = 13 then 'Bundesliga - Soccer'

when SportID = 14 then 'La Liga - Soccer'

when SportID = 15 then 'Serie A - Soccer'

when SportID = 16 then 'UEFA Champions League - Soccer' END as Sport

from

optimism.core.fact_event_logs

where

topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'

and block_timestamp > CURRENT_DATE - 15 and block_timestamp < CURRENT_DATE

),

code2 as (

select FROM_ADDRESS,TO_ADDRESS,RAW_AMOUNT

from optimism.core.fact_token_transfers

where ORIGIN_TO_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'

and CONTRACT_ADDRESS='0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'

and FROM_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'

select sum (b.RAW_AMOUNT/1e18) as amount_USD,

case when a.Sport like '%Footbal%' then 'American_Footbal'

when a.Sport like '%Baseball%' then 'Baseball'

when a.Sport like '%Basketball%' then 'Basketball'

when a.Sport like '%Hockey%' then 'Hockey'