USERS_CHURNED | TOTAL_USERS | CHURN_RATE_PERCENTAGE | |
|---|---|---|---|
1 | 180985 | 206842 | 87.5 |
bobby_daniel2_Liquidity Provider - Churn Rate
Updated 2025-04-03
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
›
⌄
WITH new_lps AS (
SELECT
provider_address as provider,
MIN(block_timestamp) AS first_deposit
FROM solana.defi.ez_liquidity_pool_actions
WHERE block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP())
AND action_type = 'deposit'
GROUP BY provider_address
),
exit_check AS (
SELECT
nl.provider,
nl.first_deposit,
CASE WHEN MIN(e.block_timestamp) IS NOT NULL
AND DATEDIFF(day, nl.first_deposit, MIN(e.block_timestamp)) <= 7
THEN TRUE ELSE FALSE END as exit_within_week
FROM new_lps nl
LEFT JOIN solana.defi.ez_liquidity_pool_actions e
ON nl.provider = e.provider_address
AND e.block_timestamp > nl.first_deposit
AND e.block_timestamp <= DATEADD(day, 7, nl.first_deposit)
AND e.action_type = 'withdraw'
GROUP BY nl.provider, nl.first_deposit
)
SELECT
COUNT(DISTINCT CASE WHEN exit_within_week THEN provider END) as users_churned,
COUNT(DISTINCT provider) as total_users,
ROUND(COUNT(DISTINCT CASE WHEN exit_within_week THEN provider END) * 100.0 /
NULLIF(COUNT(DISTINCT provider), 0), 2) AS churn_rate_percentage
FROM exit_check;
Last run: about 2 months ago
1
22B
7s