Haisenbergretention rate
Updated 2025-03-29
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 daily_prices AS (
SELECT
date_trunc(day, hour)::date AS price_date,
token_address,
avg(price) AS token_price
FROM crosschain.price.ez_prices_hourly
WHERE date_trunc(day, hour)::date BETWEEN '{{start_date}}' AND '{{end_date}}'
GROUP BY 1, 2
),
-- Step 1: Get monthly active users
monthly_active_users AS (
SELECT DISTINCT
date_trunc('month', block_timestamp) AS month,
sender AS address
FROM axelar.defi.ez_bridge_squid
WHERE block_timestamp::date BETWEEN '{{start_date}}' AND '{{end_date}}'
),
-- Step 2: Calculate retained users
retained_users AS (
SELECT
current_month.month,
COUNT(DISTINCT current_month.address) AS total_users,
COUNT(DISTINCT previous_month.address) AS retained_users
FROM
monthly_active_users current_month
LEFT JOIN
monthly_active_users previous_month
ON current_month.address = previous_month.address
AND current_month.month = previous_month.month + INTERVAL '1 month'
GROUP BY
current_month.month
)
-- Step 3: Calculate retention rate
QueryRunArchived: QueryRun has been archived