SocioCryptoUser segmentation copy
    Updated 2023-10-29

    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