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 BLOCK_TIMESTAMP, tx_hash, 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, sum(RAW_AMOUNT/1e18) as Volume_USD, sum(Volume_USD) over (order by date) as Volume_USD_C

,count(distinct origin_from_address) as num_users ,count(distinct tx_hash) as num_bets

from b group by 1 order by 1 asc