binhachonFirst Time Deposits at Mango Markets - Daily and average deposit
Updated 2022-02-18
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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