binhachonFirst Time Deposits at Mango Markets - Daily and average deposit
    Updated 2022-02-18
    with new_account_transactions as (
    select
    block_timestamp,
    tx_id,
    inner_instruction:instructions[0]:parsed:info:newAccount::string as account
    from solana.events
    where block_timestamp::date >= '2022-02-10'
    and inner_instruction:instructions[0]:parsed:info:owner::string = 'mv3ekLzLbnVPNxjSKvqBpU3ZeZXPQdEC3bp5MDEBG68'
    and inner_instruction:instructions[0]:parsed:type::string = 'createAccount'
    and instruction:programId::string = 'mv3ekLzLbnVPNxjSKvqBpU3ZeZXPQdEC3bp5MDEBG68'
    ),
    first_deposit_transactions as (
    select
    block_timestamp,
    inner_instruction:instructions[0]:parsed:info:amount::float/1e9 as amount_deposited,
    inner_instruction:instructions[0]:parsed:info:destination::string as account
    from solana.events
    where tx_id in (select tx_id from new_account_transactions)
    and inner_instruction:instructions[0]:parsed:type::string = 'transfer'
    and instruction:programId::string = 'mv3ekLzLbnVPNxjSKvqBpU3ZeZXPQdEC3bp5MDEBG68'
    ),
    first_deposit as (
    select
    block_timestamp::date as blocktime,
    sum(amount_deposited) as daily_deposited,
    count(amount_deposited) as number_of_deposit
    from first_deposit_transactions
    group by blocktime
    )
    select
    blocktime,
    daily_deposited,
    sum(daily_deposited) over (order by blocktime) as total_deposited,
    number_of_deposit,
    daily_deposited / number_of_deposit as daily_average,
    sum(daily_deposited) over () / sum(number_of_deposit) over () as average
    Run a query to Download Data