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='nearhorizon.near'

GROUP BY 1

),

active_weeks AS (

SELECT

signer_id,

COUNT(DISTINCT DATE_TRUNC('week', block_timestamp)) AS count_weeks

FROM near.social.fact_decoded_actions

WHERE signer_id IN (SELECT signer_id FROM activity)

GROUP BY 1

),

user_groups AS (

SELECT

signer_id,

COUNT(DISTINCT tx_hash) AS count_transactions

FROM near.social.fact_decoded_actions

WHERE signer_id IN (SELECT signer_id FROM activity)

GROUP BY 1

),

user_group_ranges AS (

SELECT

signer_id,

CASE

WHEN count_transactions = 1 THEN '1 tx'

WHEN count_transactions between 1 and 5 THEN '2-4 tx'

WHEN count_transactions between 5 and 10 THEN '5-9 tx'

WHEN count_transactions between 10 and 20 THEN '10-19 tx'

WHEN count_transactions between 20 and 50 THEN '20-49 tx'

WHEN count_transactions between 50 and 100 THEN '50-99 tx'

WHEN count_transactions between 10 and 500 THEN '100-499 tx'