adriaparcerisasnear horizon 4
Updated 2023-05-29
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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
),
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
Run a query to Download Data