princefarzamTotal Anchor Deposits, Last 2 Weeks
    Updated 2022-01-12
    WITH deposits as (
    SELECT
    DATE_TRUNC('day',block_timestamp) as block_date,
    SUM(msg_value:coins[0]:amount::float)/POW(10,6) as amount_deposited
    FROM terra.msgs
    WHERE
    tx_status='SUCCEEDED'
    AND
    msg_value:contract::string='terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'
    AND
    msg_value:execute_msg::string='{"deposit_stable":{}}'
    GROUP BY 1
    ORDER BY 1),
    withdraws as (
    SELECT
    date_trunc('day',block_timestamp) as block_date,
    SUM(msg_value:execute_msg:send:amount::float/POW(10,6)) as amount_withdrawn
    FROM terra.msgs
    WHERE
    tx_status='SUCCEEDED'
    AND
    msg_value:execute_msg:send:contract::string='terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'
    AND
    msg_value:execute_msg:send:msg:redeem_stable IS NOT NULL
    GROUP BY 1
    ORDER BY 1),
    cumulative as (
    SELECT
    deposits.block_date as block_date,
    SUM(amount_deposited) OVER (ORDER BY deposits.block_date) as cum_amount_deposited,
    SUM(amount_withdrawn) OVER (ORDER BY deposits.block_date) as cum_amount_withdrawn,
    cum_amount_deposited-cum_amount_withdrawn as cum_net_deposits
    FROM deposits
    JOIN withdraws ON deposits.block_date=withdraws.block_date)

    SELECT *
    Run a query to Download Data