shreexMode Of Transactions And Average Transaction Per Wallet
Updated 2022-07-06
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
›
⌄
with active_addresses as (
select
distinct tx_signer as active_address,
count(distinct date_trunc('day',block_timestamp)) as day_count
from flipside_prod_db.mdao_near.transactions
where block_timestamp >= CURRENT_DATE - interval '1 months'
GROUP BY active_address
having day_count >= 2
ORDER BY day_count desc),
finding_median as (
select
active_address,
count(*) as daily_transactions_of_active_users,
count(distinct tx_signer) as daily_active_users,
daily_transactions_of_active_users/daily_active_users as avg_transaction_per_user
from flipside_prod_db.mdao_near.transactions,active_addresses where tx_signer=active_address AND block_timestamp >= CURRENT_DATE - interval '90 days'
GROUP BY active_address
ORDER BY daily_transactions_of_active_users desc)
select
mode(daily_transactions_of_active_users) as mode_of_transactions,
avg(daily_transactions_of_active_users) as avg_tx_count
from finding_median