elovianoouser_activity
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
›
⌄
-- select *
-- from $query('9152dbe0-ce7d-4a40-b2d3-69551da944d1')
-- limit 10
-- $query('739c5256-60bd-43eb-8e5e-b8c8cb627373')
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
QueryRunArchived: QueryRun has been archived