LTirrellnear_citizens_users-sol
    Updated 2022-09-29
    with flattened_transactions as (
    select
    t.block_timestamp :: date as datetime,
    t.tx_id,
    s.value as from_address
    from
    solana.core.fact_transactions t,
    lateral flatten(input => t.signers) s
    where
    succeeded = 'TRUE'
    and datetime >= current_date - 30
    ),
    active_users_30d as (
    select
    distinct t.from_address as users
    from
    flattened_transactions t
    where
    t.datetime >= current_date - 30
    ),
    active_user_activity as (
    select
    t.block_timestamp :: date as datetime,
    count(t.tx_id) as active_users_tx,
    count(distinct t.signers) as active_users,
    active_users_tx / active_users as tx_per_active_users
    from
    solana.core.fact_transactions t
    where
    datetime >= current_date - 90
    and t.signers in (
    select
    users
    from
    active_users_30d
    )