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

),

user_activity as (

SELECT

distinct tx_signer as user,

count(distinct tx_hash) as counts

from near.core.fact_transactions

where tx_signer in (select distinct signer_id from activity)

group by 1

),

times as (

SELECT

user,

case when counts=1 then 'A. Tourists: 1 time use'

when counts between 2 and 7 then 'B. Weekly Passengers: 2-7 uses'

when counts between 8 and 30 then 'C. Monthly Passengers: 8-30 uses'

when counts between 31 and 90 then 'D. Partial user: 30-90 uses'

when counts between 91 and 180 then 'E. Active user 90-180 uses'

when counts between 181 and 365 then 'F. Near fan: 180-365 uses'

else 'G. NEAR megafan: more than 365 uses' end as activity

from user_activity

),

numbers as (

SELECT

activity,

count(distinct y.tx_signer) as counts

from times x

join near.core.fact_transactions y on x.user=y.tx_signer

join near.core.fact_actions_events_function_call z on y.tx_hash=z.tx_hash