MLDZMNbot_eth
    Updated 2023-04-29
    with bots as (
    select
    date_trunc('minute',block_timestamp) as minute,
    FROM_ADDRESS as bt_address,
    count (tx_hash) as count_transacions
    from ethereum.core.fact_token_transfers
    group by 1, 2 having count_transacions > 15)


    select
    date_trunc('week', BLOCK_TIMESTAMP) as date,
    case
    when ETH_from_ADDRESS in (select bt_address from bots) then 'BOTS'
    else 'Regular Users' end as wlts,
    count(distinct tx_hash) as no_txn,
    count(distinct ETH_from_ADDRESS) as no_users,
    sum(amount_usd) as deposit,
    avg(amount_usd) as average_depsoit

    from ethereum.core.ez_eth_transfers
    where ETH_TO_ADDRESS= lower('0xCBD6832Ebc203e49E2B771897067fce3c58575ac')
    group by 1,2

    union all

    select
    date_trunc('week', BLOCK_TIMESTAMP) as date,
    case
    when from_ADDRESS in (select bt_address from bots) then 'BOTS'
    else 'Regular Users' end as wlts,
    count(distinct tx_hash) as no_txn,
    count(distinct from_ADDRESS) as no_users,
    sum(amount_usd) as deposit,
    avg(amount_usd) as average_depsoit

    from ethereum.core.ez_token_transfers
    Run a query to Download Data