princefarzamProvide bETH
    Updated 2022-02-05
    WITH provide_bETH as(
    SELECT
    BLOCK_TIMESTAMP,
    EVENT_ATTRIBUTES:collaterals[0]:amount::INT/POW(10,6) AS bETH_AMOUNT
    FROM terra.msg_events
    WHERE EVENT_ATTRIBUTES:"0_action"::string = 'lock_collateral'
    AND EVENT_ATTRIBUTES:collaterals[0]:denom::string='terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun'
    and tx_status = 'SUCCEEDED'
    and BLOCK_TIMESTAMP >= '2021-11-03' --90 days ago
    GROUP BY 1,2
    ORDER by 1 ASC
    ),
    cum_bETH AS(
    SELECT
    BLOCK_TIMESTAMP,
    bETH_AMOUNT,
    SUM(bETH_AMOUNT) OVER (ORDER BY BLOCK_TIMESTAMP ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATE_bETH,
    (SELECT
    avg(price_usd)
    from terra.oracle_prices
    where symbol= 'mETH'
    and BLOCK_TIMESTAMP >= '2021-11-03') * CUMULATE_bETH AS CUMULATE_bETH_IN_UST
    FROM provide_bETH
    WHERE BLOCK_TIMESTAMP >= '2021-11-03'
    ORDER by 1 ASC
    )
    SELECT
    CB.BLOCK_TIMESTAMP,
    PB.bETH_AMOUNT,
    CB.CUMULATE_bETH,
    CB.CUMULATE_bETH_IN_UST
    FROM cum_bETH CB FULL OUTER JOIN provide_bETH PB ON CB.BLOCK_TIMESTAMP=PB.BLOCK_TIMESTAMP
    WHERE CB.BLOCK_TIMESTAMP >= '2021-11-03'
    ORDER by 1 ASC
    Run a query to Download Data