WITH

activity AS (

SELECT

DISTINCT signer_id,

COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS count_days,

MIN(block_timestamp) AS debut

FROM near.core.fact_actions_events_function_call

WHERE (receiver_id LIKE '%marbledao.near' or receiver_id LIKE '%marbledex.near')

GROUP BY 1

),

tab2 AS (

SELECT

signer_id,

COUNT(DISTINCT tx_hash) AS transactions,

MIN(block_timestamp) AS debut

FROM

near.social.fact_decoded_actions

WHERE signer_id IN (SELECT signer_id FROM activity)

GROUP BY

1

),

tab3 AS (

SELECT

*,

ROW_NUMBER() OVER (ORDER BY transactions DESC) AS rank_by_transactions,

RANK() OVER (ORDER BY debut) AS rank_by_join_date,

SUM(transactions) OVER () AS total_transactions,

AVG(transactions) OVER () AS avg_transactions,

MIN(transactions) OVER () AS min_transactions,

MAX(transactions) OVER () AS max_transactions

FROM

tab2

)