niloalgo
Updated 2022-12-20
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
›
⌄
--credit to: Ali3N
with flowtable1 as (
select TX_SENDER,
TX_GROUP_ID,
block_timestamp,
row_number () over (partition by TX_SENDER order by block_timestamp) as RN
from algorand.core.fact_transaction
where block_timestamp >= CURRENT_DATE - interval'6 months'),
flowtable2 as (
select TX_SENDER,
TX_GROUP_ID,
block_timestamp,
row_number () over (partition by TX_SENDER order by block_timestamp) as RN
from algorand.core.fact_transaction
where block_timestamp >= CURRENT_DATE - interval'6 months'),
flowtable3 as (
select t1.TX_SENDER,
avg (timediff (hour,t1.block_timestamp,t2.block_timestamp)) as Time_Difference
from flowtable1 t1 join flowtable2 t2 on t1.TX_SENDER = t2.TX_SENDER and t2.rn = t1.rn + 1
group by 1)
select case
when Time_Difference < 24 then 'Less than One Day'
when Time_Difference >= 24 and Time_Difference < 48 then 'Between 1 and 2 Days'
when Time_Difference >= 48 and Time_Difference < 168 then 'Between 2 and 7 Days'
when Time_Difference >= 168 and Time_Difference < 336 then 'Between 1 Week and 2 Weeks'
when Time_Difference >= 336 and Time_Difference < 720 then 'Between 2 Weeks and 1 Month'
when Time_Difference >= 720 and Time_Difference < 2160 then 'Between 1 and 3 Months'
when Time_Difference >= 2160 then 'More than 3 Months' end as Time_Difference1,
count (*)
from flowtable3
group by 1
order by 2 desc
Run a query to Download Data