elsina2024-08-20: Daily new users per platform
    Updated 2024-10-19
    with users as (
    SELECT
    origin_from_address,
    CASE
    WHEN platform IN ('kyberswap-v1', 'kyberswap-v2') THEN 'Kyberswap'
    WHEN platform IN ('pharaoh-v1', 'pharaoh-v2') THEN 'Pharaoh'
    WHEN platform IN ('trader-joe-v1', 'trader-joe-v2') THEN 'Trader Joe'
    WHEN platform IN ('uniswap-v2', 'uniswap-v3') THEN 'Uniswap'
    ELSE platform
    END as platforms,
    min(block_timestamp) as min_date
    from
    avalanche.defi.ez_dex_swaps

    group by
    origin_from_address, platforms
    )

    select
    date_trunc('day', min_date) as date,
    platforms,
    count(distinct origin_from_address) as new_user_count
    from
    users
    where date >= current_date - interval '30 days'
    group by date, platforms
    order by date, platforms asc;


    QueryRunArchived: QueryRun has been archived