liquidity_pools as (

SELECT

pool_address,

token0 as asset

FROM ethereum.core.dim_dex_liquidity_pools

UNION ALL

SELECT

pool_address,

token1 as asset

FROM ethereum.core.dim_dex_liquidity_pools

)

SELECT

asset,

sum(COALESCE(tvl,0)) as tvl

FROM (

SELECT DISTINCT

p.pool_address,

p.asset,

sum(usd_value_now) as tvl

from liquidity_pools p

JOIN ethereum.core.ez_current_balances b

ON b.has_decimal = true

AND p.pool_address = b.user_address

AND b.has_price = true

AND p.asset = b.contract_address

GROUP BY 1,2

GROUP BY 1