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,639 | 36.00% | 21.00% | 17.00% | 20.00% | 15.00% | 16.00% | 12.00% | 14.00% | 12.00% | 12.00% | 12.00% | 7.00% |
2 | 2023-03 | 6,392 | 13.00% | 8.00% | 11.00% | 8.00% | 10.00% | 8.00% | 8.00% | 7.00% | 8.00% | 7.00% | 4.00% | |
3 | 2023-04 | 2,250 | 12.00% | 14.00% | 9.00% | 10.00% | 7.00% | 8.00% | 8.00% | 7.00% | 7.00% | 4.00% | ||
4 | 2023-05 | 1,343 | 18.00% | 11.00% | 12.00% | 8.00% | 8.00% | 7.00% | 7.00% | 7.00% | 4.00% | |||
5 | 2023-06 | 2,258 | 16.00% | 14.00% | 9.00% | 9.00% | 8.00% | 7.00% | 8.00% | 4.00% | ||||
6 | 2023-07 | 2,206 | 17.00% | 11.00% | 10.00% | 8.00% | 7.00% | 6.00% | 5.00% | |||||
7 | 2023-08 | 2,193 | 16.00% | 13.00% | 10.00% | 9.00% | 7.00% | 5.00% | ||||||
8 | 2023-09 | 2,249 | 14.00% | 9.00% | 7.00% | 7.00% | 4.00% | |||||||
9 | 2023-10 | 3,028 | 12.00% | 8.00% | 7.00% | 4.00% | ||||||||
10 | 2023-11 | 1,888 | 20.00% | 15.00% | 7.00% | |||||||||
11 | 2023-12 | 2,407 | 17.00% | 8.00% | ||||||||||
12 | 2024-01 | 2,868 | 10.00% | |||||||||||
13 | 2024-02 | 1,301 |
check_skedAave Borrower/Time Based Cohort
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
›
⌄
with base_table as (
SELECT
ft.depositor_address,
date_trunc('month', ft.block_timestamp) AS date,
min(date_trunc('month', ft.block_timestamp)) OVER (PARTITION BY ft.depositor_address) AS earliest_date,
datediff(
'month',
min(date_trunc('month', ft.block_timestamp)) OVER (PARTITION BY ft.depositor_address), -- earliest_date
date_trunc('month', ft.block_timestamp) -- current date in month
) AS difference
FROM ethereum.aave.ez_deposits ft
WHERE
ft.block_timestamp >= current_timestamp() - interval '1 year'
)
, count_new_addies as(
select
earliest_date
, count(distinct depositor_address) as new_addies
from base_table
group by 1
)
, count_returning_addresses as(
select
earliest_date
, difference
, count(distinct depositor_address) as existing_addresses
from base_table
where difference != 0
group by 1,2
)
, long_retention_table as(
select
count_new_addies.earliest_date
Last run: over 1 year ago
13
1KB
3s