bnb as (SELECT ORIGIN_FROM_ADDRESS as LP1,TO_ADDRESS as pools,sum(amount_USD) as ADD_USD

from ethereum.core.ez_token_transfers where TO_ADDRESS in (select POOL_ADDRESS from aaa) and ORIGIN_TO_ADDRESS='0xd9e1ce17f2641f24ae83637ab66a2cca9c378b9f'

and origin_function_signature in ('0xe8e33700', '0xded9382a', '0xf305d719')

and BLOCK_TIMESTAMP>=CURRENT_DATE-365 group by 1,2),

eth as (SELECT ORIGIN_FROM_ADDRESS as LP2,

from_ADDRESS as pools1,sum(amount_USD) as remove_usd,

max(BLOCK_TIMESTAMP)::date as remove_time

from ethereum.core.ez_token_transfers q left outer join bnb d on q.from_ADDRESS=d.pools and q.ORIGIN_FROM_ADDRESS=d.lp1

where ORIGIN_TO_ADDRESS='0xd9e1ce17f2641f24ae83637ab66a2cca9c378b9f'

and origin_function_signature in ('0xbaa2abde', '0x2195995c', '0x02751cec')

and BLOCK_TIMESTAMP>=CURRENT_DATE-365

group by 1,2),

mtc as (select

q.LP2 as LP,

POOL_NAME,

(remove_usd - ADD_USD) as USD_proit

from eth q left outer join bnb d on q.LP2=d.LP1

left join aaa m on q.pools1=m.POOL_ADDRESS

where USD_proit is not null

select

POOL_NAME,

sum(USD_proit) as profit

from mtc

group by 1 having POOL_NAME is not null

order by 2 desc

limit 20