select concat('0x', substr(data, 27, 40)) as game_contract_address, regexp_substr_all(substr(data, 3, len(data)), '.{64}') as sub_str, ethereum.public.udf_hex_to_int(concat('0x', substr(sub_str[17], 25, 40))) as tags, hex_decode_string(substr(data, 3 + 15 * 64, 64)) as away_team, hex_decode_string(substr(data, 3 + 13 * 64,64)) as home_team

from optimism.core.fact_event_logs

where contract_address = lower('0x2b91c14ce9aa828ed124d12541452a017d8a2148')

and topics[0] = lower('0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f')

having len(tags) = 4

),

list2 as (

select tx_hash, origin_from_address, to_address, game_title, tags, raw_amount from

optimism.core.fact_token_transfers a right join (

select game_contract_address, tags, concat(home_team, away_team) as game_title from list1

) b on a.to_address = b.game_contract_address

where origin_to_address = lower('0x170a5714112daeff20e798b6e92e25b86ea603c1')

and block_timestamp > current_date - 15

and block_timestamp < current_date

and game_title is not null

)

select case

when tags = 9001 then 'NCAA - American Footbal'

when tags = 9002 then 'NFL - American Footbal'

when tags = 9003 then 'MLB - Baseball'

when tags = 9004 then 'NBA - Basketball'

when tags = 9005 then 'NCAA - Basketball'

when tags = 9006 then 'NHL - Hockey'

when tags = 9007 then 'MMS'

when tags = 9008 then 'WNBA'

when tags = 9010 then 'MLS - Soccer'

when tags = 9011 then 'EPL - Soccer'

when tags = 9012 then 'Ligue 1 - Soccer'

when tags = 9013 then 'Bundesliga - Soccer'

when tags = 9014 then 'LaLiga - Soccer'

when tags = 9015 then 'Serie A - Soccer'

when tags = 9016 then 'UEFA Champions League - Soccer'

else null end as type, count(distinct(origin_from_address)) as users_num, count(distinct(tx_hash)) as tx_num, sum(raw_amount / 1e18) as total_amount

from list2 group by type