flipside

    Ethereum Dex Swapper/Time Based Cohort - Sushi copy
    check_skedEthereum Dex Swapper/Time Based Cohort - Sushi copy
    Updated 2024-02-20
    -- forked from Ethereum Dex Swapper/Time Based Cohort - Sushi @ https://flipsidecrypto.xyz/edit/queries/d0d2a242-cd14-412b-bc23-b01ca784a02a

    with base_table as (
    SELECT
    ft.swapper,
    date_trunc('month', ft.block_timestamp) AS date,
    min(date_trunc('month', ft.block_timestamp)) OVER (PARTITION BY ft.swapper) AS earliest_date,
    datediff(
    'month',
    min(date_trunc('month', ft.block_timestamp)) OVER (PARTITION BY ft.swapper), -- earliest_date
    date_trunc('month', ft.block_timestamp) -- current date in month
    ) AS difference
    FROM solana.defi.fact_swaps ft
    WHERE
    ft.block_timestamp >= current_timestamp() - interval '1 year'
    --AND platform = 'uniswap-v3'
    --AND platform = 'sushiswap'
    --AND platform = 'balancer'
    --AND platform = 'uniswap-v2'
    --AND platform = 'curve'
    --AND platform = 'pancakeswap-v3'
    )

    , count_new_addies as(
    select
    earliest_date
    , count(distinct swapper) as new_addies
    from base_table
    group by 1
    )

    , count_returning_addresses as(
    select
    earliest_date
    , difference
    , count(distinct swapper) as existing_addresses
    Last run: over 1 year ago
    EARLIEST_DATE
    NEW_ADDIES
    ONE_MONTH_LATER
    TWO_MONTH_LATER
    THREE_MONTH_LATER
    FOUR_MONTH_LATER
    FIVE_MONTH_LATER
    SIX_MONTH_LATER
    SEVEN_MONTH_LATER
    EIGHT_MONTH_LATER
    NINE_MONTH_LATER
    TEN_MONTH_LATER
    ELEVEN_MONTH_LATER
    TWELVE_MONTH_LATER
    1
    		2023-02 59,24860.00%47.00%44.00%35.00%34.00%30.00%26.00%29.00%41.00%49.00%53.00%45.00%
    2
    		2023-03 88,92033.00%30.00%22.00%21.00%18.00%15.00%17.00%30.00%38.00%42.00%35.00%
    3
    		2023-04 77,01733.00%21.00%21.00%18.00%15.00%17.00%27.00%36.00%37.00%30.00%
    4
    		2023-05 109,72717.00%15.00%12.00%10.00%11.00%20.00%26.00%28.00%22.00%
    5
    		2023-06 67,82914.00%10.00%8.00%8.00%13.00%15.00%17.00%13.00%
    6
    		2023-07 62,23318.00%12.00%11.00%23.00%30.00%33.00%26.00%
    7
    		2023-08 37,14819.00%17.00%24.00%30.00%35.00%27.00%
    8
    		2023-09 24,78229.00%30.00%35.00%40.00%30.00%
    9
    		2023-10 42,81630.00%31.00%33.00%25.00%
    10
    		2023-11 232,01648.00%45.00%28.00%
    11
    		2023-12 711,89552.00%29.00%
    12
    		2024-01 760,99629.00%
    13
    		2024-02 528,007
    13
    1KB
    305s