SocioCryptoUser segmentation copy
Updated 2023-10-29Copy 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
31
32
33
34
35
36
›
⌄
with ava_uni as (
SELECT
origin_from_address as ava_uni_users,
count(DISTINCT tx_hash) as n_swaps
FROM avalanche.defi.ez_dex_swaps
WHERE platform ilike 'uniswap%'
GROUP BY 1
)
,
new_users as (SELECT distinct origin_from_address -- users on bored with uniswap
FROM (
SELECT *,
row_number()over(partition by origin_from_address order BY block_timestamp) as rank
FROM avalanche.defi.ez_dex_swaps)
WHERE rank = 1 AND platform ilike 'uniswap%')
,
swap_On_Ava_in_the_past as (
SELECT
ava_uni_users as ava_user_before
FROM ava_uni
WHERE ava_uni_users not in (SELECT origin_from_address FROM new_users)
)
,
swap_on_other_chain_in_the_past as (
SELECT
DISTINCT trader as other_user_swap
FROM crosschain.defi.ez_dex_swaps a
WHERE trader in (SELECT ava_uni_users FROM ava_uni)
AND blockchain NOT IN ('avalanche')
AND a.block_timestamp < '2023-07-12'
)
,
swap_on_uni_in_the_past as (
SELECT
Run a query to Download Data