flipside

    BTC.BTC Asset terms liquidity, L1 Asset depth, Synth Supply, Savers depth (from 2022-11-12)
    MultipartiteBTC.BTC Asset terms liquidity, L1 Asset depth, Synth Supply, Savers depth (from 2022-11-12)
    Updated 2025-03-05
    WITH
    dim_convert AS (
    SELECT dim_block_id, block_id
    FROM thorchain.core.dim_block
    ),

    saverdepths AS (
    SELECT DATE(block_timestamp) AS date,
    block_id AS saver_block_id,
    pool_name,
    SPLIT(pool_name, '-')[0] AS synth,
    POWER(10,-8) * asset_e8 AS savers_depth
    FROM (thorchain.defi.fact_block_pool_depths INNER JOIN dim_convert ON fact_block_pool_depths.dim_block_id = dim_convert.dim_block_id)
    WHERE (pool_name = 'BTC/BTC')
    --For an extra check that 'synth_amount' refers to synths.
    AND (date >= '2022-11-12')
    QUALIFY (saver_block_id = MAX(saver_block_id) OVER(PARTITION BY date, pool_name))
    --AND (date <> (SELECT DATE(MAX(block_timestamp)) FROM thorchain.defi.fact_rewards_events)) --Not complete yet, so leave it out.
    ),

    pools AS (
    SELECT DISTINCT REPLACE(pool_name, '/', '.') AS pool_name
    FROM saverdepths
    ),

    pooldepths AS (
    SELECT DATE(block_timestamp) AS date,
    block_id AS pool_block_id,
    pool_name,
    POWER(10,-8) * (2 * asset_e8) AS asset_terms_liquidity,
    POWER(10,-8) * asset_e8 AS asset_depth,
    POWER(10,-8) * synth_e8 AS synth_supply,
    rune_e8 / 1e8 AS rune_depth,
    1e8 * asset_e8 / rune_e8 AS satoshi_per_rune
    FROM (thorchain.defi.fact_block_pool_depths AS table1 INNER JOIN dim_convert ON table1.dim_block_id = dim_convert.dim_block_id)
    WHERE (pool_name in (SELECT pool_name FROM pools))
    Last run: about 1 month agoAuto-refreshes every 24 hours
    DATE
    SYNTH
    POOL_BLOCK_ID
    SAVER_BLOCK_ID
    ASSET_TERMS_LIQUIDITY
    ASSET_DEPTH
    SYNTH_SUPPLY
    SAVERS_DEPTH
    RUNE_DEPTH
    SYNTH_PER_POOL_DEPTH_PERCENTAGE
    SATOSHI_PER_RUNE
    1
    		2025-03-01 00:00:00.000
    BTC/BTC
    		2008205020072168606.6831485303.34157425540.49458601539.2095613320777372.96376789.0900937911459.961155
    2
    		2025-02-27 00:00:00.000
    BTC/BTC
    		2005369120043798611.1886966305.5943483540.4945863539.2095613320534456.87834688.4333413411488.202732
    3
    		2025-02-18 00:00:00.000
    BTC/BTC
    		1992873719918727558.9533516279.4766758540.4945997539.2095613322147564.74442296.697622111261.884451
    4
    		2025-02-10 00:00:00.000
    BTC/BTC
    		1981507219811300596.81437362298.40718681540.4945997539.2095613320680202.12406390.5632678421442.960688
    5
    		2025-02-07 00:00:00.000
    BTC/BTC
    		1977354619763446557.44336988278.72168494540.4945997539.2095613322135102.03101796.9595530061259.184098
    6
    		2025-02-06 00:00:00.000
    BTC/BTC
    		1975930119754293569.43822608284.71911304540.4945997539.2095613321654031.01825894.9171613261314.855016
    7
    		2025-01-29 00:00:00.000
    BTC/BTC
    		1964548119635395706.56440644353.28220322540.49460435539.2095613317660241.58338876.4961551172000.438112
    8
    		2025-01-24 00:00:00.000
    BTC/BTC
    		1957495219561971711.92280772355.96140386540.49460435539.2095613317590812.75869275.9203945272023.564282
    9
    		2025-01-23 00:00:00.000
    BTC/BTC
    		1956092519560280866.27061864433.13530932541.15830097539.8732579514702456.80163862.4698898162946.006339
    10
    		2025-01-22 00:00:00.000
    BTC/BTC
    		1954691219546178891.84983424445.92491712542.82687598541.487638714346016.12955360.8652774423108.353658
    11
    		2025-01-21 00:00:00.000
    BTC/BTC
    		1953307419532707935.29073358467.64536679544.40911151543.0698742314207760.74887458.20747413291.478334
    12
    		2025-01-20 00:00:00.000
    BTC/BTC
    		1951925319517842931.13631204465.56815602550.20271964548.8634823614442010.67499359.089384933223.707325
    13
    		2025-01-19 00:00:00.000
    BTC/BTC
    		1950525219504396940.61811716470.30905858550.66363078549.324393514358122.55722558.5427412823275.56097
    14
    		2025-01-18 00:00:00.000
    BTC/BTC
    		1949121519490710992.00948966496.00474483553.9651802552.6259429213693652.25213355.8427299313622.150875
    15
    		2025-01-17 00:00:00.000
    BTC/BTC
    		19477258194759151030.99657996515.49828998554.35875569553.0195184113171930.134953.7692138333913.612392
    16
    		2025-01-16 00:00:00.000
    BTC/BTC
    		1946322719460284978.28382656489.14191328556.21385667554.8226111913929604.033156.8560822093511.527766
    17
    		2025-01-15 00:00:00.000
    BTC/BTC
    		1944904219448976981.43231966490.71615983558.13235544556.7411099613925615.296356.8691640023523.838261
    18
    		2025-01-14 00:00:00.000
    BTC/BTC
    		1943519019435088957.26591368478.63295684558.37279646556.9815509814296264.22261758.3299570663347.958246
    19
    		2025-01-13 00:00:00.000
    BTC/BTC
    		1942109319420229973.35756654486.67878327561.96973311560.5784876314175990.45386257.7351789753433.12014
    20
    		2025-01-12 00:00:00.000
    BTC/BTC
    		19406951194043141041.9322326520.9661163566.00833668564.610491214494662.75826954.3229510493594.192738
    ...
    812
    118KB
    98s