feyikemiDistribution of Users based on Avg Time between Swaps copy copy
Updated 2024-08-13
999
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 Ario / Distribution of Users based on Avg Time between Swaps copy @ https://flipsidecrypto.xyz/Ario/q/UvmchPu941fG/distribution-of-users-based-on-avg-time-between-swaps-copy
-- forked from 0xDataWolf / Time based Cohort Wide @ https://flipsidecrypto.xyz/0xDataWolf/q/jEC6M61jinzx/time-based-cohort-wide
with base_table as (
-- this is data prep
select
from_address as Trader,
date_trunc('month', block_timestamp) as date,
min(date_trunc('month', block_timestamp)) over(partition by Trader) as earliest_date,
datediff(
'month',
min(date_trunc('month', block_timestamp)) over(partition by Trader) -- earliest_date
,
date_trunc('month', block_timestamp) -- current date in month
) as difference
from
thorchain.defi.fact_swaps
where
1 = 1
and block_timestamp >= current_timestamp() - interval '1 year'
--and _TX_TYPE = 'swap'
),
count_new_users as(
select
earliest_date,
count(distinct Trader) as new_users
from
base_table
group by
1
),
count_returning_users as(
select
earliest_date,
difference,
count(distinct Trader) as existing_users
QueryRunArchived: QueryRun has been archived