BlockTrackerlabel project in last_ n_month
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
›
⌄
-- label of activity projects in last 12 month
SELECT
CASE when n_txns <= 1000 AND n_day <= 30 then 'low active'
when n_txns <=10000 AND n_day <= 90 then 'medium'
when n_txns <= 100000 AND n_day <= 180 then 'active'
else 'high active' end as activity_level,
count(project_name) as n_project
FROM (
SELECT
b.project_name,
count(DISTINCT tx_hash) as n_txns,
count(DISTINCT tx_signer) as unique_users,
count(DISTINCT block_timestamp::date) as n_day,
sum(transaction_fee/1e24) as tx_fee --near
FROM near.core.fact_transactions a
LEFT JOIN near.core.dim_address_labels b
ON a.tx_receiver = b.address
WHERE
block_timestamp >= DATEADD('month', -1*{{last_n_month}}, CURRENT_DATE)
AND block_timestamp < current_date
AND label_type IS NOT NULL
AND project_name IS NOT NULL
GROUP BY 1
ORDER BY 3 DESC
)
GROUP BY 1
Run a query to Download Data