Query editor
-- African Stablecoins Liquidity Distribution on Stellar Network-- 1) pick only the latest row per poolWITH latest_pools AS (SELECTliquidity_pool_id,asset_a_code, asset_a_amount,asset_b_code, asset_b_amountFROM (SELECT*,ROW_NUMBER()OVER (PARTITION BY liquidity_pool_idORDER BY updated_at DESC) AS rnFROM stellar.liquidity_poolsWHERE deleted = FALSE) tWHERE rn = 1),-- 2) unpivot A/B sides and keep only African stablecoin codesafrican_pool_tokens AS (SELECT asset_a_code AS asset_code, asset_a_amount AS amountFROM latest_poolsWHERE asset_a_code IN ('ZARZ', 'mZARZ', 'NGNC', 'NGNT', 'TZS', 'KES', 'RWF')UNION ALLSELECT asset_b_code AS asset_code, asset_b_amount AS amountFROM latest_poolsWHERE asset_b_code IN ('ZARZ', 'mZARZ', 'NGNC', 'NGNT', 'TZS', 'KES', 'RWF'))-- 3) aggregate with scaling for NGNT (Nigerian Naira variants)SELECTasset_code AS "Asset Code",CASE-- Nigerian Naira tokens typically use 6 decimal places like USDCWHEN asset_code IN ('NGNT', 'KES', 'TZS') THEN SUM(amount) / 1000000-- Other African tokens use standard 7 decimal placesELSE SUM(amount)END AS "Total Pool Liquidity",-- Add currency description for clarityCASEWHEN asset_code = 'ZARZ' THEN 'South African Rand'WHEN asset_code = 'mZARZ' THEN 'South African Rand (Variant)'WHEN asset_code = 'NGNC' THEN 'Nigerian Naira (Circle)'WHEN asset_code = 'NGNT' THEN 'Nigerian Naira (Trust Token)'WHEN asset_code = 'TZS' THEN 'Tanzanian Shilling'WHEN asset_code = 'KES' THEN 'Kenyan Shilling'WHEN asset_code = 'RWF' THEN 'Rwandan Franc'ELSE 'Unknown African Currency'END AS "Currency Description",-- Count of pools containing this assetCOUNT(*) AS "Pool Count"FROM african_pool_tokensGROUP BY asset_codeORDER BY "Total Pool Liquidity" DESC;