Moeteract
Updated 2023-02-01
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 base1 as (
select TX_SENDER as user,BLOCK_TIMESTAMP ,tx_id,TX_SUCCEEDED from terra.core.fact_transactions
where not TX_SENDER in (select address from terra.core.dim_address_labels)
union all
select RECEIVER as user,BLOCK_TIMESTAMP ,tx_id ,TX_SUCCEEDED from terra.core.ez_transfers
where not RECEIVER in (select address from terra.core.dim_address_labels)
)
,base as (select
user,
count (distinct tx_id ) as txns,
100*(count (distinct case when TX_SUCCEEDED = 'True' then tx_id end) )/txns as success_rate,
count (distinct BLOCK_TIMESTAMP::date ) as active_days,
100* active_days/(select count (distinct BLOCK_TIMESTAMP::date) from
terra.core.fact_transactions ) as ratio
from
base1
group by 1
)
select
case
-- when ratio < 5 then 'under 5 %'
-- when ratio >= 5 and ratio < 15 then '5 - 15 %'
-- when ratio >= 15 and ratio < 30 then '15 - 30 %'
-- when ratio >= 30 and ratio < 50 then '30 - 50 %'
-- when ratio >= 50 and ratio < 75 then '50 - 75 %'
-- when ratio >= 75 then '75 - 100 %'
when ratio < {{ratio}} then 'Others'
else 'Active users'
end as tier ,
count(distinct user) as user_count ,
sum(txns) "txns",
Run a query to Download Data