DAY | ACTIVE_SWAPPERS | NEW_SWAPPERS | RETURNING_SWAPPERS | RETENTION_RATE | |
|---|---|---|---|---|---|
1 | 2022-07-01 00:00:00.000 | 1576 | 1576 | 424 | 0.269035533 |
2 | 2022-08-01 00:00:00.000 | 12479 | 11782 | 4015 | 0.3217405241 |
3 | 2022-09-01 00:00:00.000 | 10503 | 7669 | 4677 | 0.4453013425 |
4 | 2022-10-01 00:00:00.000 | 5937 | 3791 | 2931 | 0.4936836786 |
5 | 2022-11-01 00:00:00.000 | 15268 | 11739 | 6564 | 0.4299187844 |
6 | 2022-12-01 00:00:00.000 | 7144 | 4383 | 3578 | 0.5008398656 |
7 | 2023-01-01 00:00:00.000 | 15889 | 12123 | 6179 | 0.3888853924 |
8 | 2023-02-01 00:00:00.000 | 5557 | 3229 | 2824 | 0.5081878712 |
9 | 2023-03-01 00:00:00.000 | 6844 | 4275 | 3590 | 0.5245470485 |
10 | 2023-04-01 00:00:00.000 | 7197 | 4445 | 3865 | 0.5370293178 |
11 | 2023-05-01 00:00:00.000 | 6879 | 4233 | 3740 | 0.543683675 |
12 | 2023-06-01 00:00:00.000 | 6233 | 3577 | 3661 | 0.5873576127 |
13 | 2023-07-01 00:00:00.000 | 8448 | 5613 | 4040 | 0.478219697 |
14 | 2023-08-01 00:00:00.000 | 6312 | 3622 | 3544 | 0.5614702155 |
15 | 2023-09-01 00:00:00.000 | 4687 | 2322 | 2902 | 0.619159377 |
16 | 2023-10-01 00:00:00.000 | 7290 | 3944 | 4441 | 0.6091906722 |
17 | 2023-11-01 00:00:00.000 | 21229 | 14953 | 11955 | 0.5631447548 |
18 | 2023-12-01 00:00:00.000 | 64049 | 51878 | 36729 | 0.5734515761 |
19 | 2024-01-01 00:00:00.000 | 49072 | 31471 | 31181 | 0.6354132703 |
20 | 2024-02-01 00:00:00.000 | 48556 | 32825 | 30539 | 0.6289438998 |
Afonso_DiazSwappers retention rate
Updated 2025-04-24
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 main as (
select
tx_id,
block_timestamp,
swapper,
swap_from_symbol as symbol_in,
swap_to_symbol as symbol_out,
abs(nvl(swap_from_amount_usd, swap_to_amount_usd)) as amount_usd,
case
when platform ilike 'jupiter%' then 'Jupiter'
when platform ilike 'raydium%' then 'Raydium'
when platform ilike 'meteora%' then 'Meteora'
when platform ilike 'saber%' then 'Saber'
when platform ilike 'orca%' then 'Orca'
else initcap(platform)
end as platform
from solana.marinade.ez_swaps
where succeeded
and 'MSOL' in (symbol_in, symbol_out)
),
first_swaps as (
select swapper, min(block_timestamp) as first_swap_date
from main
group by swapper
),
retention as (
select
date_trunc('{{ period }}', m.block_timestamp) as day,
count(distinct m.swapper) as active_swappers, -- Unique swappers per day
count(distinct case when f.first_swap_date = m.block_timestamp then m.swapper end) as new_swappers,
count(distinct case when f.first_swap_date < m.block_timestamp then m.swapper end) as returning_swappers
from main m
left join first_swaps f on m.swapper = f.swapper
group by day
)
select
Last run: 19 days ago
34
2KB
2s