with tb1 as ( select block_timestamp,

tx_id,

blockchain,

pool_name,

ifnull(from_address,native_to_address) as trader,

case when from_asset ilike '%USDT%' then 'USDT'

when from_asset ilike '%link%' then 'link'

when from_asset ilike '%usdc%' then 'USDC'

when from_asset ilike '%/%' then split(from_asset,'/')[1]

when from_asset ilike '%.%' then split(from_asset,'.')[1]

else from_asset end as from_token,

case when to_asset ilike '%USDT%' then 'USDT'

when to_asset ilike '%usdc%' then 'USDC'

when to_asset ilike '%link%' then 'link'

when to_asset ilike '%/%' then split(to_asset,'/')[1]

when to_asset ilike '%.%' then split(to_asset,'.')[1]

else to_asset end as to_token,

case when from_amount_usd >= to_amount_usd then to_amount_usd else from_amount_usd end as amount1,

case when from_amount_usd is null then to_amount_usd else from_amount_usd end as amount2,

case when amount1 is null then amount2 else amount1 end as amount

from thorchain.defi.fact_swaps

where block_timestamp::date >= '2024-01-01'

and affiliate_address in ('td','ti','tr','te')

select trunc(block_timestamp,'week') as weekly,

concat(from_token,'->',to_token) as pair,

sum(amount) as volume,

rank() over (partition by weekly order by volume desc) as rank

from tb1

group by 1,2

qualify rank <= 5