binhachonFlash Bounty - Critical Anchor KPIs - Deposit stats - Upgrade
    Updated 2021-12-12
    with temp as (
    SELECT
    block_timestamp,
    msg_value:sender::string as address,
    msg_value:execute_msg:borrow_stable:borrow_amount /POW(10,6) as borrowed_amount,
    row_number() over (partition by address order by block_timestamp) as rownumber
    FROM terra.msgs
    WHERE msg_value:execute_msg:borrow_stable IS NOT NULL
    and tx_status = 'SUCCEEDED'
    ),
    borrows as(
    select block_timestamp, temp.address as address, borrowed_amount, rownumber, sum(balance_usd) as balance from temp left join terra.daily_balances on (date_trunc('day', block_timestamp) = date and temp.address = terra.daily_balances.address)
    group by block_timestamp, temp.address, borrowed_amount, rownumber
    ),
    repays as (
    SELECT
    block_timestamp,
    msg_value:sender::string as address,
    msg_value:coins[0]:amount/ POW(10,6) as amount_repaid,
    row_number() over (partition by address order by block_timestamp) as rownumber
    FROM terra.msgs
    WHERE msg_value:execute_msg:repay_stable IS NOT NULL
    and tx_status = 'SUCCEEDED'),
    temp_1 as (
    SELECT
    block_timestamp,
    msg_value:sender::string as address,
    msg_value:coins[0]:amount / pow(10,6) as deposit_amount,
    row_number() over (partition by address order by block_timestamp) as rownumber
    FROM terra.msgs
    WHERE msg_value:execute_msg:deposit_stable IS NOT NULL
    and tx_status = 'SUCCEEDED'
    ),
    terra_balance as(
    select date, address, sum(balance_usd) as balance_usd from terra.daily_balances
    group by date, address
    Run a query to Download Data