elovianoouser_activity metrics copy
Updated 2025-01-17
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
›
⌄
-- forked from user_activity metrics @ https://flipsidecrypto.xyz/studio/queries/187b29d3-0006-4381-889d-42bf99f4e1d1
-- forked from user_activity @ https://flipsidecrypto.xyz/studio/queries/1170e35a-efd0-4789-9747-6672300a7211
with user_tx as (
-- select count(distinct user)
-- from (
select
DISTINCT date_trunc('week' , block_timestamp) as week
, signers[0] as user , count(*) as transactions
from eclipse.core.fact_transactions
where date_trunc('week' , block_timestamp) = cast ('{{week}}' as date)
-- where 1 like 2025-01-06 || '%'
group by 1 , 2
-- having count(*) > 2
order by 1, 3 desc
)
, activity_table as (
select
tx.week , tx.user
, tx.transactions
, v. total_transaction_volume
, pr. program_interactions
, SUM((CASE WHEN tx.transactions > 5 THEN 1 ELSE 0 END) +
(CASE WHEN v.total_transaction_volume IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN pr.program_interactions IS NOT NULL THEN 1 ELSE 0 END)) AS score
, case
when SUM((CASE WHEN tx.transactions >5 THEN 1 ELSE 0 END) +
QueryRunArchived: QueryRun has been archived