Pre-Sages Lab (Team 2)LVR (ETH-USDT POOL)
    Updated 2025-03-09

    -- WITH liquidity AS (
    -- select
    -- *
    -- from $query('b7f48f5a-ed4b-4cfa-87bb-8067d04c3fcf')
    -- Where symbol = 'WETH'
    -- ),


    WITH liquidity AS (
    SELECT
    value:DT::date as DT
    , value:TOTAL_TVL::float AS total_tvl
    , value:SYMBOL::string AS symbol
    FROM (SELECT
    livequery.live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/b7f48f5a-ed4b-4cfa-87bb-8067d04c3fcf/data/latest') as response
    ), lateral FLATTEN (input => response:data)
    Where symbol = 'WETH'
    ),

    swapping AS (
    -- Extract relevant swap data from Uniswap V3 swaps table
    SELECT
    DATE_TRUNC('day', block_timestamp) AS block_timestamp,
    tx_hash AS swap_tx,
    price_0_1 AS eth_per_usdt,
    amount0_adjusted AS amount_eth_adjusted,
    amount1_adjusted AS amount_usdt_adjusted,
    token0_price AS eth_price,
    token1_price AS usdt_price,
    amount0_usd AS amount_eth_in_usd,
    amount1_usd AS amount_usdt_in_usd,
    token0_address AS eth,
    token1_address AS usdt,
    --0.03% Fee tier
    CASE
    Last run: 3 months ago
    DATE
    VARIANCE_LOG_RETURNS
    LVR (BP)
    LVR_IN_DECIMAL
    TOTAL_TVL
    LVR USD
    TOTAL_FEE
    NO_OF_TRANSACTION
    1
    2025-02-27 00:00:00.00028.5881867993.573523350.035735233592720437.10219993313386.469900336640.66914794
    2
    2025-02-26 00:00:00.00031.1990345783.8998793220.03899879322100861111.095193933461.6158458617175.596187990
    3
    2025-02-25 00:00:00.00028.9299194643.6162399330.0361623993398677244.97962023568405.9377809134234.0882351607
    4
    2025-02-24 00:00:00.00029.847425543.7309281930.03730928193100292798.5659263741852.2967934717473.9693171067
    5
    2025-02-23 00:00:00.00023.926314642.990789330.029907893398909637.97836672958178.899009456549.91587543
    6
    2025-02-22 00:00:00.00027.849208993.4811511240.0348115112495691204.51432813331155.441260123136.192371459
    7
    2025-02-21 00:00:00.00030.1422777743.7677847220.0376778472297689380.87712853680725.567466215068.293818912
    8
    2025-02-20 00:00:00.00032.4869638814.0608704850.0406087048597483715.09901043958687.414284376512.017068612
    9
    2025-02-19 00:00:00.00030.3945780073.7993222510.0379932225195565681.24940083630848.19187113216.200865547
    10
    2025-02-18 00:00:00.00026.8387396133.3548424520.0335484245298060032.49815173289759.598294784992.575796572
    11
    2025-02-17 00:00:00.00027.6147621183.4518452650.0345184526595246050.74108083287746.2923418891.519214549
    12
    2025-02-16 00:00:00.00030.5009770843.8126221360.0381262213694002408.25414963583956.62502341583.0565269
    13
    2025-02-15 00:00:00.00028.9186839953.6148354990.0361483549994626754.07652343420601.498230551379.678379376
    14
    2025-02-14 00:00:00.00029.6322105213.7040263150.0370402631593926045.90855863479045.457171465253.750918695
    15
    2025-02-13 00:00:00.00030.102032253.7627540310.0376275403197475638.09928223667768.502025544815.128814562
    16
    2025-02-12 00:00:00.00029.5681812563.6960226570.0369602265793644748.53030183461131.1227426112378.21954871
    17
    2025-02-11 00:00:00.00027.0303117353.3787889670.0337878896795660961.75025643232182.021201596654.925173740
    18
    2025-02-10 00:00:00.00027.027438953.3784298690.0337842986994481402.91436273191987.936508515527.259748759
    19
    2025-02-09 00:00:00.00028.8819947673.6102493460.0361024934688731950.49978543203444.662471795336.515374633
    20
    2025-02-08 00:00:00.00028.7831983.597899750.035978997587077537.02858313132962.487011523517.362768582
    ...
    366
    42KB
    4s