select pool_name as name, avg(asset_amount_usd / asset_amount) as asset_price, avg(rune_amount_usd / rune_amount) as rune_price

from thorchain.pool_block_balances

where asset_amount != 0 and rune_amount != 0

group by 1

select date_trunc('day', block_timestamp) as date,

CASE

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

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

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

WHEN pool_name = 'ETH.XRUNE-0X69FA0FEE221AD11012BAB0FDB45D444D3D2CE71C' THEN 'ETH.XRUNE'

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

WHEN pool_name = 'ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044' THEN 'ETH.THOR'

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

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

WHEN pool_name = 'BNB.ETH-1C9' THEN 'BNB.ETH'

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

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

WHEN pool_name = 'TERRA.LUNA' THEN 'TERRA.LUNA'

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

WHEN pool_name = 'TERRA.UST' THEN 'TERRA.UST'

WHEN pool_name = 'ETH.SUSHI-0X6B3595068778DD592E39A122F4F5A5CF09C90FE2' THEN 'ETH.SUSHI'

WHEN pool_name = 'ETH.ALCX-0XDBDB4D16EDA451D0503B854CF79D55697F90C8DF' THEN 'ETH.ALCX'

WHEN pool_name = 'ETH.YFI-0X0BC529C00C6401AEF6D220BE8C6EA1667F6AD93E' THEN 'ETH.YFI'

WHEN pool_name = 'ETH.KYL-0X67B6D479C7BB412C54E03DCA8E1BC6740CE6B99C' THEN 'ETH.KYL'

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

WHEN pool_name = 'ETH.DODO-0X43DFC4159D86F3A37A5A4B3D4580B888AD7D4DDD' THEN 'ETH.DODO'

WHEN pool_name = 'BNB.TWT-8C2' THEN 'BNB.TWT'

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

WHEN pool_name = 'ETH.FOX-0XC770EEFAD204B5180DF6A14EE197D99D808EE52D' THEN 'ETH.FOX'

WHEN pool_name = 'ETH.AAVE-0X7FC66500C84A76AD7E9C93437BFC5AC33E2DDAE9' THEN 'ETH.AAVE'

WHEN pool_name = 'ETH.HOT-0X6C6EE5E31D828DE241282B9606C8E98EA48526E2' THEN 'ETH.HOT'

WHEN pool_name = 'ETH.PERP-0XBC396689893D065F41BC2C6ECBEE5E0085233447' THEN 'ETH.PERP'

WHEN pool_name = 'BNB.AVA-645' THEN 'BNB.AVA'