flipside

    Savers Depth USD
    kaleb0xSavers Depth USD
    Updated 2025-02-21
    WITH daily_saver_depth AS (SELECT to_date(block_timestamp) as day,
    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,
    CASE
    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,
    SUM(cumulative_depth_usd) OVER(PARTITION BY day ORDER BY day) as cumulative_total_depth_usd
    FROM asset_names WHERE day is not null
    Last run: 2 months ago
    DAY
    ASSET
    ASSET_CHANGE
    ASSET_CHANGE_USD
    CUMULATIVE_DEPTH
    CUMULATIVE_DEPTH_USD
    CUMULATIVE_TOTAL_DEPTH_USD
    1
    		2025-02-18 00:00:00.000BNB/BNB004844.616150363083558.9390995989554088.9242453
    2
    		2025-02-18 00:00:00.000GAIA/ATOM0039253.93667635190985.26101701789554088.9242453
    3
    		2025-02-18 00:00:00.000AVAX/AVAX0039608.36133855994284.96202130789554088.9242453
    4
    		2025-02-18 00:00:00.000LTC/LTC0025622.790750013364168.9992809589554088.9242453
    5
    		2025-02-18 00:00:00.000DOGE/DOGE0012239813.2382313213018.913908289554088.9242453
    6
    		2025-02-18 00:00:00.000BTC/BTC00539.2095613353313264.137189289554088.9242453
    7
    		2025-02-18 00:00:00.000Stablecoin009954934.598479819947860.0374184289554088.9242453
    8
    		2025-02-18 00:00:00.000ETH/ETH004879.9975375113580590.372499689554088.9242453
    9
    		2025-02-18 00:00:00.000BCH/BCH005602.821965521866357.3018110889554088.9242453
    10
    		2025-02-10 00:00:00.000AVAX/AVAX0039608.361338551049622.0851849489882984.4962765
    11
    		2025-02-10 00:00:00.000BTC/BTC00539.2095613354202608.353508689882984.4962765
    12
    		2025-02-10 00:00:00.000DOGE/DOGE0012239813.2382313193701.2135991289882984.4962765
    13
    		2025-02-10 00:00:00.000GAIA/ATOM0039253.93667635193087.78802248589882984.4962765
    14
    		2025-02-10 00:00:00.000BCH/BCH005602.821965521929789.9560063689882984.4962765
    15
    		2025-02-10 00:00:00.000ETH/ETH004879.9975375113367172.685296589882984.4962765
    16
    		2025-02-10 00:00:00.000LTC/LTC0025622.790750013106584.4808919889882984.4962765
    17
    		2025-02-10 00:00:00.000BNB/BNB004844.616150362896417.4066931289882984.4962765
    18
    		2025-02-10 00:00:00.000Stablecoin009954934.598479819944000.5270733389882984.4962765
    19
    		2025-02-07 00:00:00.000Stablecoin009954934.598479819946983.7377068289840951.2173751
    20
    		2025-02-07 00:00:00.000LTC/LTC0025622.790750012803809.4222055489840951.2173751
    ...
    7043
    804KB
    7s