top10 as (

select distinct pool_name, sum(AMOUNT1_USD) as volume

from ethereum.uniswapv3.ez_swaps

group by 1 having volume is not null order by 2 desc limit 10

SELECT

POOL_NAME,

TOKEN0_SYMBOL,

TOKEN1_SYMBOL,

SUM(abs(AMOUNT1_ADJUSTED)) AS total_asset_quantity,

AVG(PRICE_0_1) AS avg_amm_price,

AVG(TOKEN1_PRICE)/AVG(TOKEN0_PRICE) AS avg_market_price,

total_asset_quantity * (avg_market_price - avg_amm_price) AS total_LVR_discrete

FROM ethereum.uniswapv3.ez_swaps

where pool_name in (select distinct pool_name from top10)

and BLOCK_TIMESTAMP BETWEEN '{{start_date}}' AND '{{end_date}}'

GROUP BY POOL_NAME, TOKEN0_SYMBOL, TOKEN1_SYMBOL

having avg_amm_price<10000

ORDER BY total_LVR_discrete