select game_contract,Tags ,concat(home_team,away_team) as game

from (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

,regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,

ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[17], 25, 40))) AS Tags

from optimism.core.fact_event_logs where topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f' and contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

having len(tags) = 4)

)

,b as (

select tx_hash, block_timestamp ,origin_from_address ,TO_ADDRESS ,game ,tags ,RAW_AMOUNT

from optimism.core.fact_token_transfers t right join a on t.to_address = a.game_contract

where ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1' and game is not null

and block_timestamp > CURRENT_DATE - 14 and block_timestamp < CURRENT_DATE

)

select

date_trunc('day',block_timestamp) as date,

case when tags in ('9001','9002') then 'Football'

when tags in ('9003','90004','9005') then 'Baseball'

when tags in ('9006') then 'Hockey'

when tags in ('9007') then 'MMA'

when tags in ('9008') then 'WNBA'

when tags in('9010','9011','9012','9013','9014','9015','9016') then 'Soccer'

else null end as sport ,sum(RAW_AMOUNT/1e18) as "USD Volume"

,count(distinct origin_from_address) as "number of users"

,count(distinct tx_hash) as "number of bets"

from b group by 1,2

order by 2 desc