select

concat('0x', substring(data,27,40)) as contract

,substring(data, 3+13*64, 64)

,regexp_replace(hex_decode_string(substring(data, 3+13*64, 64)), '[^a-z A-Z]') as Home

,regexp_replace(hex_decode_string(substring(data, 3+15*64, 64)), '[^a-z A-Z]') as Away

,1

-- ,EVENT_INPUTS:value/pow(10,decimals)

from optimism.core.fact_event_logs

-- join optimism.core.dim_contracts d on d.ADDRESS = CONTRACT_ADDRESS

where topics[0]::string = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'

and contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

and block_timestamp::date>=CURRENT_DATE-14

and TX_STATUS='SUCCESS'

and EVENT_REMOVED=false

,lst_all as (

select

tx_hash

,RAW_AMOUNT/pow(10,decimals) as amount_usd

,origin_from_address as user

,contract as game_contract

,home

,away

from optimism.core.fact_token_transfers left join contracts on to_address = contract

join optimism.core.dim_contracts d on d.ADDRESS = CONTRACT_ADDRESS

-- where tx_hash = '0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865'

where to_address in (select contract from contracts)

and block_timestamp::date>=CURRENT_DATE-14

,lst_union as (

select home as team,user,tx_hash,amount_usd from lst_all

union all

select away,user,tx_hash,amount_usd from lst_all