RayyykAvalanche Swaps vs Transfers 3
    Updated 2022-12-08
    with table_1 as (select min(block_timestamp) as first_tx,
    'Swap' as action,
    origin_from_address
    from avalanche.core.fact_event_logs
    where contract_address = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E')
    and event_inputs:value/1e6 < 999999999
    and block_timestamp >= '2022-07-01'
    and tx_status = 'SUCCESS'
    and tx_hash in (select distinct tx_hash from avalanche.core.fact_event_logs where event_name = 'Swap')
    group by 3
    union
    select min(block_timestamp) as first_tx,
    'Transfer' as action,
    origin_from_address
    from avalanche.core.fact_event_logs
    where contract_address = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E')
    and event_inputs:value/1e6 < 999999999
    and block_timestamp >= '2022-07-01'
    and tx_status = 'SUCCESS'
    and not tx_hash in (select distinct tx_hash from avalanche.core.fact_event_logs where event_name = 'Swap')
    group by 3),

    final1 as (select date_trunc('hour', first_tx) as day,
    action,
    count(distinct(origin_from_address)) as new_wallets,
    sum(new_wallets) over (partition by action order by day) as cumu_new_wallets
    from table_1
    group by 1,2)

    select *
    from final1
    order by 1 desc
    Run a query to Download Data