WITH classified_pools AS (

SELECT

day,

CASE

WHEN pool_name = 'BTC.BTC' THEN 'BTC'

WHEN pool_name = 'ETH.ETH' THEN 'ETH'

WHEN pool_name = 'BNB.BNB' THEN 'BNB'

WHEN pool_name = 'LTC.LTC' THEN 'LTC'

WHEN pool_name = 'AVAX.AVAX' THEN 'AVAX'

WHEN pool_name = 'GAIA.ATOM' THEN 'ATOM'

WHEN pool_name = 'DOGE.DOGE' THEN 'DOGE'

WHEN pool_name = 'BCH.BCH' THEN 'BCH'

WHEN pool_name = 'BSC.BNB' THEN 'BNB-BSC'

WHEN pool_name = 'ETH.USDC-0XA0B86991C6218B36C1D19D4A2E9EB0CE3606EB48' THEN 'ETH-USDC'

WHEN pool_name = 'ETH.USDT-0XDAC17F958D2EE523A2206206994597C13D831EC7' THEN 'ETH-USDT'

WHEN pool_name = 'BNB.BUSD-BD1' THEN 'BUSD'

WHEN pool_name = 'AVAX.USDC-0XB97EF9EF8734C71904D8002F8B6BC66DD9C48A6E' THEN 'AVAX-USDC'

WHEN pool_name = 'BSC.USDC-0X8AC76A51CC950D9822D68B83FE1AD97B32CD580D' THEN 'BSC-USDC'

WHEN pool_name = 'BNB.BTCB-1DE' THEN 'BTCB'

WHEN pool_name = 'ETH.WBTC-0X2260FAC5E5542A773AA44FBCFEDF7C193BC2C599' THEN 'WBTC'

-- Add more conditions for other pool classifications as needed

ELSE pool_name -- Default to the original pool_name if not classified

END AS classified_pool,

asset_liquidity

FROM thorchain.defi.fact_daily_pool_stats

-- Add any additional WHERE conditions as needed

day,

MAX(CASE WHEN classified_pool = 'BTC' THEN asset_liquidity END) AS BTC,

MAX(CASE WHEN classified_pool = 'ETH' THEN asset_liquidity END) AS ETH,

MAX(CASE WHEN classified_pool = 'BNB' THEN asset_liquidity END) AS BNB,

MAX(CASE WHEN classified_pool = 'LTC' THEN asset_liquidity END) AS LTC,