select

concat('0x',substr(data,27,40)) as market_address,

case

when len(data) = 1410 then right(tokenflow_eth.hextoint(substr(data,1091,64)),2)

when len(data) = 1474 then right(tokenflow_eth.hextoint(substr(data,1155,64)),2)

else right(tokenflow_eth.hextoint(substr(data,1219,64)),2)

end as sport_id,

case

when sport_id in (1,2) then 'Football'

when sport_id = 3 then 'Baseball'

when sport_id in (4,5,8) then 'Basketball'

when sport_id = 6 then 'Hockey'

when sport_id = 7 then 'MMA'

else 'Soccer' end as sport

from optimism.core.fact_event_logs

where contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

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

and tx_status = 'SUCCESS'

),

buying_positions as (

select

txs.from_address as user_address,

sport,

sum(raw_amount / pow(10,decimals) * price * (-1)) as buy_usd

from optimism.core.fact_transactions txs

left join optimism.core.fact_token_transfers transfers

on txs.tx_hash = transfers.tx_hash

and txs.from_address = transfers.origin_from_address

and transfers.origin_from_address = transfers.from_address

left join optimism.core.fact_hourly_token_prices prices

on transfers.contract_address = prices.token_address

and date_trunc('hour',transfers.block_timestamp) = prices.hour

inner join First_table om

on concat('0x',substr(txs.input_data,35,40)) = om.market_address

where substr(input_data,0,10) in ('0x8875eb84','0x9f916c9f','0x6cc5a6ff')