SELECT

"ASSET",

COUNT(DISTINCT LIQUIDITY_POOL_ID) AS "LPs"

FROM (

SELECT

ASSET_A_TYPE AS "ASSET",

LIQUIDITY_POOL_ID

FROM stellar.defi.fact_liquidity_pools

UNION ALL

SELECT

ASSET_B_CODE AS "ASSET",

LIQUIDITY_POOL_ID

FROM stellar.defi.fact_liquidity_pools

)

WHERE "ASSET" IS NOT NULL

GROUP BY 1

SELECT

'native' AS "Symbol",

"LPs" AS total

FROM cte WHERE "ASSET" = 'native'

UNION ALL

SELECT

'Other' AS "Symbol",

39499 - "LPs" AS total

FROM cte WHERE "ASSET" = 'native';