MLDZMNbt2
Updated 2023-07-09
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 time as (
select
case
when '{{Period}}' = 'Last 24 Hours' then 1
when '{{Period}}' = 'Last 7 Days' then 7
when '{{Period}}' = 'Last 30 Days' then 30
when '{{Period}}' = 'All Time' then 500
else 30
end as time_gp
),
tb1 as(
select
date_trunc('hour',block_timestamp) as hour,
signers[0] as bt_address,
count (distinct tx_id) as count_transacions
from solana.core.fact_events
where PROGRAM_ID='E1XRkj9fPF2NQUdoq41AHPqwMDHykYfn5PzBXAyDs7Be'
and BLOCK_TIMESTAMP>=current_date- (select time_gp from time)
group by 1, 2 having count_transacions > 30)
select
date_trunc('{{Time_basis}}',BLOCK_TIMESTAMP) as date,
case
when signers[0] in (select bt_address from tb1 ) then 'BOTS'
else 'Regular Users' end as wlts,
count(case when succeeded = 'TRUE' then 1 else null end) as success,
count (distinct tx_id) as count_transactions,
success/count_transactions*100 as success_rate
from solana.core.fact_events
where PROGRAM_ID='E1XRkj9fPF2NQUdoq41AHPqwMDHykYfn5PzBXAyDs7Be'
and BLOCK_TIMESTAMP>=current_date- (select time_gp from time)
group by 1,2
-- select
-- case
Run a query to Download Data