SELECT

CASE

WHEN ASSET_A_TYPE = 'native' THEN 'XLM'

WHEN ASSET_A_CODE IS NOT NULL THEN ASSET_A_CODE

END AS Asset_a,

CASE

WHEN ASSET_B_TYPE = 'native' THEN 'XLM'

WHEN ASSET_B_CODE IS NOT NULL THEN ASSET_B_CODE

END AS Asset_b

FROM stellar.defi.fact_liquidity_pools

),

all_Assets AS (

SELECT Asset_a AS Asset FROM Asset_pools WHERE Asset_a IS NOT NULL

UNION

SELECT Asset_b AS Asset FROM Asset_pools WHERE Asset_b IS NOT NULL

SELECT COUNT(DISTINCT Asset) AS total_Assets

FROM all_Assets;