with

top_lp as (

select

sum(case when amount_in_usd is null then 0 else amount_in_usd end) as total,

sum(case when token_in='0x82af49447d8a07e3bd95bd0d56f35241523fbab1' then amount_in

when token_out='0x82af49447d8a07e3bd95bd0d56f35241523fbab1' then amount_out else 0

) as total_eth,

contract_address as pool_address

,pool_name

from arbitrum.defi.ez_dex_swaps

where date(block_timestamp)>= current_date() - 365

and contract_address in (

select pool_address from arbitrum.defi.dim_dex_liquidity_pools where platform like '%trader%'

and date(creation_time)>='2023-01-01'

)

and (pool_name not like ('0x%') or pool_name not like ('%-0x%'))

and contract_address not in ('0xbd42a558b60ff17394497f687931d41c68f2f167','0xc1bd8c3bc926d25836c325f35cce8f9f538d82d4','0x16a5d28b20a3fddecdcaf02df4b3935734df1a1f')

group by contract_address, pool_name

order by total desc, total_eth desc

limit 275

),

lps as (

select

pool_name, pool_address, tokens:token0 as token0, tokens:token1 as token1

from

arbitrum.defi.dim_dex_liquidity_pools

1=1

and pool_address in (select pool_address from top_lp where pool_name not like ('0x%'))

),