WITH daily_saver_depth AS (SELECT to_date(block_timestamp) as day,

--SPLIT(pool_name, '-')[0] AS asset,

pool_name AS asset,

avg(POWER(10,-8) * asset_e8) AS cumulative_depth

FROM thorchain.defi.fact_block_pool_depths

WHERE (pool_name LIKE '%/%')

GROUP BY day, asset),

savers_change AS (SELECT *, cumulative_depth - coalesce(lag(cumulative_depth) over (order by asset, day), 0) as asset_change

from daily_saver_depth),

prices AS (SELECT to_date(block_timestamp) as day, avg(asset_usd) as asset_usd, REPLACE(pool_name, '.', '/') as asset

from thorchain.price.fact_prices group by day, asset order by day DESC),

joined AS (SELECT a.day, a.asset, a.asset_change, asset_usd * a.asset_change as asset_change_usd,

a.cumulative_depth, asset_usd * a.cumulative_depth as cumulative_depth_usd

FROM savers_change as a left join prices as b on a.day = b.day and a.asset = b.asset),

asset_names AS (SELECT day, sum(asset_change) as asset_change,

sum(asset_change_usd) as asset_change_usd, sum(cumulative_depth) as cumulative_depth, sum(cumulative_depth_usd) as cumulative_depth_usd,

WHEN asset = 'BSC/BNB' THEN 'BNB/BNB'

WHEN asset = 'ETH/USDC-0XA0B86991C6218B36C1D19D4A2E9EB0CE3606EB48' THEN 'Stablecoin'

WHEN asset = 'AVAX/USDC-0XB97EF9EF8734C71904D8002F8B6BC66DD9C48A6E' THEN 'Stablecoin'

WHEN asset = 'BNB/BUSD-BD1' THEN 'Stablecoin'

WHEN asset = 'BSC/USDC-0X8AC76A51CC950D9822D68B83FE1AD97B32CD580D' THEN 'Stablecoin'

WHEN asset = 'ETH/DAI-0X6B175474E89094C44DA98B954EEDEAC495271D0F' THEN 'Stablecoin'

WHEN asset = 'ETH/GUSD-0X056FD409E1D7A124BD7017459DFEA2F387B6D5CD' THEN 'Stablecoin'

WHEN asset = 'ETH/LUSD-0X5F98805A4E8BE255A32880FDEC7F6728C6568BA0' THEN 'Stablecoin'

WHEN asset = 'ETH/USDP-0X8E870D67F660D95D5BE530380D0EC0BD388289E1' THEN 'Stablecoin'

WHEN asset = 'ETH/USDT-0XDAC17F958D2EE523A2206206994597C13D831EC7' THEN 'Stablecoin'

ELSE asset END as asset2

FROM joined GROUP BY day, asset2)

SELECT day, asset2 as asset, asset_change,asset_change_usd, cumulative_depth, cumulative_depth_usd,