(select projects,

avg_apy,

rank() over (order by avg_apy desc) as ranking

from

(select projects,

avg(apy) as avg_apy

from

(select

--distinct symbol,

PROJECTS,

tvl_usd,

apy

from external.defillama.fact_pool_yields

where --projects = 'pendle'

--and

symbol not like '%-%'

and

symbol not like '%+%'

and

symbol like '%BTC%'

and

symbol != 'GMUSDC(WBTC)'

and symbol != 'BTCUSD'

and projects not in ('dolomite','across','wing-finance','thorchain','stream-finance','yearn-finance')

and date = current_date() - 1

)

where apy < 100 and TVL_USD > 1000000

group by 1

order by 2 desc ))

where ranking between 1 and 10