tx_hash, origin_from_address, raw_amount/1e18 as usd_vol

from optimism.core.fact_token_transfers

where block_timestamp > CURRENT_DATE - 15 and block_timestamp < CURRENT_DATE

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','0x6c3097c3aa804c2389c2ef86a97e655424ae8631' ) then 'Baseball'

when contract_address in ('0xc648febb78826a60242160b3bcc0425fd58c70a6','0x4898a35628ae19f50c32bf4ee070d547e001973f',

'0xf9ac8a718b14521cf433fa7d7f4a16afbc1bff86','0x3310bcde57f350f316e97f83e1bf3874bc9f0f2c') then 'Football'

when contract_address in ( '0xb4a6939cc5683c00e83bca6017e1cd0a99c0d0a1','0xdd433e355c4582320eb6f5c0372f0e03ca45717c',

'0xe9bfdd82905141e18929e4c850f693d88c1f461a','0x7fc15b461ae75b41f9b55c83075e10382131ee20') then 'soccer'

when contract_address in ('0xc63ab1f550165468a9de1211e4055a584ad7a841','0xb7b84b5c84c57ecd881623b6bf3d16b995c7a8e9',

'0x22817bf55f3148050eabee94e2ecc050317c81b1') then 'UFC'

else contract_address