select

currency

,case

when currency = 'gamm/pool/'||'1' then 'ATOM/OSMO'

when currency = 'gamm/pool/'||'678' then 'USDC/OSMO'

when currency = 'gamm/pool/'||'704' then 'WETH/OSMO'

when currency = 'gamm/pool/'||'712' then 'WBTC/OSMO'

when currency = 'gamm/pool/'||'803' then 'ATOM/sATOM'

when currency = 'gamm/pool/'||'722' then 'EVMOS/OSMO'

when currency = 'gamm/pool/'||'674' then 'WBTC/OSMO'

when currency = 'gamm/pool/'||'497' then 'JUNO/OSMO'

end as pool_name

,case

when currency = 'gamm/pool/'||'1' then 1

when currency = 'gamm/pool/'||'678' then 678

when currency = 'gamm/pool/'||'704' then 704

when currency = 'gamm/pool/'||'712' then 712

when currency = 'gamm/pool/'||'803' then 803

when currency = 'gamm/pool/'||'722' then 722

when currency = 'gamm/pool/'||'674' then 674

when currency = 'gamm/pool/'||'497' then 497

end as pool_id

,sum (balance/pow(10,decimal)) as TVL

from osmosis.core.fact_daily_balances

where balance_type = 'locked liquidity' and currency ilike '%pool%'

and currency in (select currency from osmosis.core.fact_liquidity_provider_actions where currency ilike '%pool%')

group by 1,2,3

having pool_name is not null

order by TVL desc

,lst_providers as (

select

LIQUIDITY_PROVIDER_ADDRESS as provider

from osmosis.core.fact_liquidity_provider_actions

where currency in(select currency from top_pools)