littlenaomiAxl - bridge - retention
Updated 2022-11-25Copy Reference Fork
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 user_cohorts as (
SELECT sender as address
, min(block_timestamp::date) as cohortDate
FROM axelar.core.fact_transfers
where tx_succeeded = 'TRUE'
and transfer_type in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')
GROUP BY address
),
new_users as (
SELECT cohortDate as day, count(DISTINCT address) as new_users_count
FROM user_cohorts uc
GROUP BY day
),
all_users as (
SELECT block_timestamp::date as day
,count(DISTINCT sender) as total_players
FROM axelar.core.fact_transfers
where tx_succeeded = 'TRUE'
and transfer_type in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')
and day > CURRENT_DATE - 60
GROUP BY day
order by day
)
SELECT au.day
, nu.new_users_count
, au.total_players - nu.new_users_count AS Existing_Users
, (nu.new_users_count/au.total_players)*100 as New_User_Percentage
FROM all_users au
LEFT JOIN new_users nu
ON au.day = nu.day
Run a query to Download Data