amelia-leeUntitled Query
    Updated 2022-08-11
    with tb1 as (select
    BLOCK_TIMESTAMP,
    FROM_ADDRESS as wallets
    from ethereum.core.fact_transactions
    where substring(INPUT_DATA, 202, 1)='1'
    and ORIGIN_FUNCTION_SIGNATURE in ('0xc6878519') -- Solana to Ethereum
    and TO_ADDRESS='0x3ee18b2214aff97000d974cf647e7c347e8fa585'
    and STATUS='SUCCESS'
    and BLOCK_TIMESTAMP>=CURRENT_DATE-90
    ),

    tb2 as (
    select
    x.BLOCK_TIMESTAMP,
    x.FROM_ADDRESS,
    x.TO_ADDRESS,
    x.tx_hash,
    ROW_NUMBER() OVER (partition by FROM_ADDRESS order by x.BLOCK_TIMESTAMP) as t_n
    from ethereum.core.fact_token_transfers x
    join tb1 y on x.from_address = y.wallets and x.block_timestamp>y.block_timestamp
    order by 1
    )

    select
    LABEL_TYPE,
    count(distinct FROM_ADDRESS) as users,
    count(tx_hash) as count_txn
    -- sum(users) over (partition by LABEL_TYPE order by day) as cum_users,
    -- sum(count_txn) over (partition by LABEL_TYPE order by day) as cum_txn
    from tb2 q join ethereum.core.dim_labels w on q.TO_ADDRESS= w.address
    where LABEL_TYPE not in ('chadmin','operator')
    and t_n=1
    group by 1
    order by 3 desc

    Run a query to Download Data