theericstoneCopy of Anchor Deposit Duration by Tiers
    Updated 2022-01-27
    WITH deposit AS (
    SELECT
    msg_value:sender as user,
    min(date(block_timestamp)) as earliest_deposit,
    max(date(block_timestamp)) as latest_deposit,
    count(distinct tx_Id) as deposit_count,
    round(median(msg_value:coins[0]:amount/pow(10,6)),1) as median_deposit,
    round(avg(msg_value:coins[0]:amount/pow(10,6)),1) as avg_deposit,
    sum(msg_value:coins[0]:amount/pow(10,6)) as total_deposit
    FROM terra.msgs
    WHERE msg_value:contract = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'
    AND msg_value:execute_msg= '{"deposit_stable":{}}'
    AND tx_status = 'SUCCEEDED'
    GROUP BY 1
    -- 38200FFD0C76F1C4B8AE087E6A85CE6A4CDB4FEE7CF97EAB4F0EFAC4C6BDF636
    ),
    redeem AS (
    SELECT
    event_attributes:"0_from" as redeemer,
    min(date(block_timestamp)) as earliest_redeem,
    max(date(block_timestamp)) as latest_redeem,
    count(distinct tx_Id) as redeem_count,
    -- sum(event_attributes:burn_amount/pow(10,6) as aust_burned,
    avg(event_attributes:redeem_amount/pow(10,6))*-1 as avg_ust_reedeemed,
    sum(event_attributes:redeem_amount/pow(10,6))*-1 as ust_redeemed
    FROM terra.msg_events
    WHERE event_attributes:"0_contract_address" = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu' -- aust
    AND event_attributes:"1_contract_address" = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s' -- anchor_market
    AND event_type = 'from_contract' AND tx_status = 'SUCCEEDED' AND event_attributes:redeem_amount IS NOT NULL
    GROUP BY 1
    ),
    FINAL AS (
    SELECT
    DATEDIFF(day, earliest_deposit, earliest_redeem) AS days_first_remption,
    DATEDIFF(day, earliest_deposit, latest_redeem) AS days_last_remption,
    round(total_deposit + ifnull(ust_redeemed,0), 1) as balance, -- if negative, withdrawn all
    Run a query to Download Data