with

users as (select

tx_hash, origin_from_address, raw_amount/1e18 as usd_vol

from

optimism.core.fact_token_transfers

block_timestamp >= current_date - 14

and raw_amount > 0

and contract_address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'

and to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and from_address != '0x0000000000000000000000000000000000000000'),

sport as (select date(block_timestamp) as day,

contract_address,

sum(usd_vol) as usd_volume,

row_number () over (partition by day order by usd_volume desc) as rank

from

optimism.core.fact_token_transfers a

join users b on a.tx_hash = b.tx_hash

where raw_amount > 0

and

contract_address != '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' --sUSD

and contract_address != '0x7f5c764cbc14f9669b88837ca1490cca17c31607' --usdc

and contract_address != '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' --DAI

and from_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1' --overtime markets

group by 1,2)

select day,

when contract_address in( '0x9da29c92014a9c795e340230e844f400e4d08cfd', '0xac26bd5d705ffa94a6f64889d85d0137949aeaae',

'0xd76b9176a7594c06f063ba3e9e636c0c8542f0c2','0xe6bb090e1ff96d02a32e51b4df829cf31392e802',

'0xd72f7c78f5a8468dcb34dca68bdc97bea263f7d7','0x078922a627c2307d49d5350d158cdc37cccabf5b',

'0x14b889830d5193e344e8308a6311d2c3b5f83478','0xf480dc5482cfc22dc4bfc18e0bf0ef8eec818393' ) then 'Baseball'

when contract_address in ('0xc648febb78826a60242160b3bcc0425fd58c70a6','0x4898a35628ae19f50c32bf4ee070d547e001973f') then 'Football'