SELECT

LIQUIDITY_POOL_ID,

CASE

WHEN ASSET_A_TYPE = 'native' THEN 'XLM'

WHEN ASSET_A_CODE IS NOT NULL THEN ASSET_A_CODE

END AS token_a,

CASE

WHEN ASSET_B_TYPE = 'native' THEN 'XLM'

WHEN ASSET_B_CODE IS NOT NULL THEN ASSET_B_CODE

END AS token_b

FROM stellar.defi.fact_liquidity_pools

),

all_tokens AS (

SELECT

LIQUIDITY_POOL_ID,

token_a AS token

FROM token_pools

WHERE token_a IS NOT NULL

UNION ALL

SELECT

LIQUIDITY_POOL_ID,

token_b AS token

FROM token_pools

WHERE token_b IS NOT NULL

SELECT

token,

COUNT(DISTINCT LIQUIDITY_POOL_ID) AS total_pools

FROM all_tokens

GROUP BY token

ORDER BY total_pools DESC

LIMIT 10;