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,

tokenflow_eth.hextoint(substr(data,3+17*64,64)) as ligue_id,

WHEN LIGUE_ID in (9010,9011,9012,9013,9014,9015,9016) THEN 'soccer'

WHEN LIGUE_ID=9003 THEN 'baseball'

WHEN LIGUE_ID=9006 THEN 'hockey'

WHEN LIGUE_ID in (9004,9005,9008) THEN 'basketball'

WHEN LIGUE_ID in (9001,9002) THEN 'football'

WHEN LIGUE_ID=9007 THEN 'mma'

else 'null'

end as sport_name

from

optimism.core.fact_event_logs

topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'

and contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'),

main_join as(

SELECT

*

FROM(

games INNER JOIN optimism.core.fact_token_transfers tk on games.game_contract= tk.TO_ADDRESS

)

WHERE from_address='0x170a5714112daeff20e798b6e92e25b86ea603c1' and tk.BLOCK_TIMESTAMP>=CURRENT_DATE-14

),

base as(

SELECT

GAME_CONTRACT,

concat(home_team, ' ', away_team) as game_name,

sport_name,

tk.from_address as wallet,

date_trunc('hour',tk.block_timestamp) as hour,

main_join.tx_hash,