shreexMode Of Transactions And Average Transaction Per Wallet
    Updated 2022-07-06
    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