princefarzamtotal collateral from addresses that have borrowed and deposited into anchor
    Updated 2022-01-31
    with providebeth as (
    SELECT
    msg_value:sender::string as address,
    SUM(msg_value:execute_msg:send:amount /POW(10,6)) as provide_beth
    FROM terra.msgs
    WHERE msg_value:execute_msg:send:msg:deposit_collateral IS NOT NULL
    and msg_value:contract::string = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun' -- deposit beth
    and tx_status = 'SUCCEEDED'
    and block_timestamp <= '2021-08-31'
    group by 1),

    withdrawbeth as (
    SELECT
    msg_value:sender::string as address,
    SUM(msg_value:execute_msg:withdraw_collateral:amount /POW(10,6)) as withdraw_beth --withdraw_collateral
    FROM terra.msgs
    WHERE msg_value:execute_msg:withdraw_collateral IS NOT NULL
    and tx_status = 'SUCCEEDED'
    and msg_value:contract::string = 'terra10cxuzggyvvv44magvrh3thpdnk9cmlgk93gmx2'
    and block_timestamp <= '2021-08-31'
    group by 1),

    netbeth as (
    select
    pb.address as addresses,
    sum(pb.provide_beth - (case when wb.withdraw_beth is null then 0 else wb.withdraw_beth end)) as net_beth,
    ( SELECT
    avg(price_usd)
    from terra.oracle_prices
    where symbol = 'mETH'
    and block_timestamp >= '2021-08-31'
    and block_timestamp < '2021-09-01')*(net_beth) as beth_value

    from providebeth pb
    left outer join withdrawbeth wb
    on pb.address = wb.address
    Run a query to Download Data