MLDZMNsoc4 copy
    Updated 2023-11-22
    -- forked from soc4 @ https://flipsidecrypto.xyz/edit/queries/a5237900-eac6-425b-afac-8b9de3404313

    select
    'Main post' as action_type,
    count(distinct tx_hash) as no_txn,
    count(distinct signer_id) as no_users,
    no_txn/no_users as action_per_user,
    no_users/count(distinct date_trunc(day, block_timestamp)) as average_user_per_day
    from near.social.fact_decoded_actions
    where BLOCK_TIMESTAMP >= CURRENT_DATE - {{Time_period_days}} and NODE='post'
    and node_data ilike '%main%'
    group by 1

    union all

    select
    'Comment' as action_type,
    count(distinct tx_hash) as no_txn,
    count(distinct signer_id) as no_users,
    no_txn/no_users as action_per_user,
    no_users/count(distinct date_trunc(day, block_timestamp)) as average_user_per_day
    from near.social.fact_decoded_actions
    where BLOCK_TIMESTAMP >= CURRENT_DATE - {{Time_period_days}} and NODE='post'
    and node_data ilike '%comment%'
    group by 1

    union all

    select
    'Like' as action_type,
    count(distinct tx_hash) as no_txn,
    count(distinct signer_id) as no_users,
    no_txn/no_users as action_per_user,
    no_users/count(distinct date_trunc(day, block_timestamp)) as average_user_per_day
    from near.social.fact_decoded_actions
    where BLOCK_TIMESTAMP >= CURRENT_DATE - {{Time_period_days}} and NODE='index'
    Run a query to Download Data