WITH b0 AS (

SELECT pool_name

FROM thorchain.core.fact_daily_pool_stats

WHERE swap_count > 0

GROUP BY 1

), b1 AS (

SELECT day

, REPLACE(SPLIT(SPLIT(t.pool_name, '.')[0], '/')[0], 'GAIA', 'ATOM') AS chain

, SUM((rune_liquidity * rune_price_usd) + (asset_liquidity + asset_price_usd)) AS tvl

FROM thorchain.core.fact_daily_pool_stats t

JOIN b0 ON b0.pool_name = t.pool_name

WHERE day >= DATEADD('month', -18, CURRENT_DATE)

AND NOT SPLIT(t.pool_name, '-')[0] IN (

'ETH.KYL'

, 'ETH.HOT'

, 'ETH.RAZE'

)

GROUP BY 1, 2

SELECT b1.*

FROM b1