select to_address as game_market_contract from optimism.core.fact_token_transfers

where origin_to_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

and origin_function_signature in ('0x8ec2c5a6','0xac2c957c')),

market_address_list2 as (select

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

case

when tokenflow_eth.hextoint(substr(data,len(data)-319,64)) in ('9001','9002') then 'Football'

when tokenflow_eth.hextoint(substr(data,len(data)-319,64)) = '9003' then 'Baseball'

when tokenflow_eth.hextoint(substr(data,len(data)-319,64)) in ('9004','9005','9008') then 'Basketball'

when tokenflow_eth.hextoint(substr(data,len(data)-319,64)) = '9006' then 'Hockey'

when tokenflow_eth.hextoint(substr(data,len(data)-319,64)) = '9007' then 'MMA'

when tokenflow_eth.hextoint(substr(data,len(data)-319,64)) in ('9010','9011','9012','9013','9014','9015','9016') then 'Soccer'

end as sport_name

from optimism.core.fact_event_logs

where contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

and origin_function_signature in ('0x8ec2c5a6','0xac2c957c')

and concat('0x',substr(data,27,40)) in (select * from market_address_list)),

claim as (SELECT event_inputs:account::string as user, game_market_contract,sport_name,

event_inputs:value/1e18 as volume

from optimism.core.fact_event_logs, market_address_list2

where origin_function_signature = '0x85149258'

and block_timestamp::date > CURRENT_DATE - 14

and contract_address = game_market_contract

and event_name is not null and event_inputs:value is not null),

paid1 as (select tx_hash, game_market_contract, sport_name

from optimism.core.fact_token_transfers

join market_address_list2 on to_address = game_market_contract

where contract_address ilike '0x8c6f28f2F1A3C87F0f938b96d27520d9751ec8d9'

and block_timestamp::date > CURRENT_DATE - 14),

paid2 as (select

origin_from_address as user, game_market_contract, sport_name, -(raw_amount/1e18) as volume

from optimism.core.fact_token_transfers join paid1 on optimism.core.fact_token_transfers.tx_hash = paid1.tx_hash

where not raw_amount is null and to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and from_address != '0x0000000000000000000000000000000000000000'

),