EARLIEST_DATE | NEW_ADDIES | ONE_MONTH_LATER | TWO_MONTH_LATER | THREE_MONTH_LATER | FOUR_MONTH_LATER | FIVE_MONTH_LATER | SIX_MONTH_LATER | SEVEN_MONTH_LATER | EIGHT_MONTH_LATER | NINE_MONTH_LATER | TEN_MONTH_LATER | ELEVEN_MONTH_LATER | TWELVE_MONTH_LATER | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 2023-02 | 1,042 | 47.00% | 27.00% | 20.00% | 14.00% | 10.00% | 11.00% | 8.00% | 5.00% | 2.00% | 2.00% | 2.00% | 1.00% |
2 | 2023-03 | 784 | 34.00% | 22.00% | 15.00% | 12.00% | 12.00% | 9.00% | 5.00% | 3.00% | 2.00% | 3.00% | 1.00% | |
3 | 2023-04 | 395 | 31.00% | 19.00% | 12.00% | 14.00% | 11.00% | 7.00% | 3.00% | 3.00% | 1.00% | 1.00% | ||
4 | 2023-05 | 256 | 29.00% | 20.00% | 14.00% | 13.00% | 9.00% | 5.00% | 5.00% | 2.00% | 2.00% | |||
5 | 2023-06 | 304 | 24.00% | 19.00% | 15.00% | 8.00% | 5.00% | 4.00% | 3.00% | 1.00% | ||||
6 | 2023-07 | 166 | 29.00% | 19.00% | 10.00% | 6.00% | 5.00% | 4.00% | 3.00% | |||||
7 | 2023-08 | 193 | 21.00% | 16.00% | 6.00% | 2.00% | 2.00% | 2.00% | ||||||
8 | 2023-09 | 124 | 21.00% | 7.00% | 4.00% | 2.00% | 2.00% | |||||||
9 | 2023-10 | 58 | 14.00% | 9.00% | 10.00% | 7.00% | ||||||||
10 | 2023-11 | 79 | 23.00% | 11.00% | 6.00% | |||||||||
11 | 2023-12 | 143 | 8.00% | 6.00% | ||||||||||
12 | 2024-01 | 108 | 7.00% | |||||||||||
13 | 2024-02 | 39 |
check_skedMars Borrower/Time Based Cohort copy
Updated 2024-02-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
›
⌄
-- forked from Optimism Dex Swapper/Time Based Cohort @ https://flipsidecrypto.xyz/edit/queries/351297d0-0e12-4a06-8a4e-af1246be62b7
with base_table as (
SELECT
ft.sender,
date_trunc('month', ft.block_timestamp) AS date,
min(date_trunc('month', ft.block_timestamp)) OVER (PARTITION BY ft.sender) AS earliest_date,
datediff(
'month',
min(date_trunc('month', ft.block_timestamp)) OVER (PARTITION BY ft.sender), -- earliest_date
date_trunc('month', ft.block_timestamp) -- current date in month
) AS difference
FROM osmosis.mars.ez_redbank_actions ft
WHERE
ft.block_timestamp >= current_timestamp() - interval '1 year'
AND action = 'deposit'
)
, count_new_addies as(
select
earliest_date
, count(distinct sender) as new_addies
from base_table
group by 1
)
, count_returning_addresses as(
select
earliest_date
, difference
, count(distinct sender) as existing_addresses
from base_table
where difference != 0
group by 1,2
)
Last run: over 1 year ago
13
1KB
5s