kaibladeArbitrum User Retention table copy
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 Arbitrum User Retention table @ https://flipsidecrypto.xyz/edit/queries/840ebf5c-3586-43a0-b038-4b5826e78c81
WITH all_delegations AS
(SELECT *
FROM arbitrum.core.ez_decoded_event_logs
WHERE contract_address = '0x912ce59144191c1204e64559fe8253a0e49e6548'
AND event_name = 'DelegateChanged'
AND tx_status = 'SUCCESS'),
all_airdrop_claims AS
(SELECT *,
(CASE
WHEN tx_hash IN (SELECT tx_hash FROM all_delegations) THEN 'Claimed And Delegated'
ELSE 'Claimed Only'
END) AS claim_type
FROM arbitrum.core.ez_decoded_event_logs
WHERE contract_address = '0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9'
AND event_name = 'HasClaimed'
AND tx_status = 'SUCCESS'),
users AS
(SELECT DISTINCT (date_trunc('month',block_timestamp)) AS active_period
,from_address AS address
FROM arbitrum.core.fact_transactions
WHERE BLOCK_TIMESTAMP::date >= CURRENT_DATE() -INTERVAL '7 months'
AND from_address IN (SELECT ORIGIN_FROM_ADDRESS FROM all_airdrop_claims)
),
initial_cohorts AS (
SELECT address,
MIN(active_period) AS initial_period
FROM users
GROUP BY address
),
cohort_diff AS (
Run a query to Download Data