Afonso_Diaz2023-10-07 08:02 PM
    Updated 2023-10-10
    with

    t1 as (
    select
    hour::date as date,
    avg(price) as price_usd
    from avalanche.price.ez_hourly_token_prices
    where symbol = 'WAVAX'
    group by 1
    ),


    t2 as (
    select
    tx_hash,
    block_timestamp,
    from_address as user,
    avax_value_precise as amount_avax,
    avax_value_precise * price_usd as amount_usd
    from avalanche.core.fact_transactions a
    join t1 on a.block_timestamp::date = t1.date
    where to_address = lower('0xA481B139a1A654cA19d2074F174f17D7534e8CeC')
    )

    select
    user,
    count(distinct tx_hash) as transactions,
    sum(amount_avax) as volume_avax,
    sum(amount_usd) as volume_usd,
    avg(amount_avax) as average_volume_avax,
    count(distinct block_timestamp::date) as active_days
    from t2
    group by 1
    order by 2 desc
    limit 10
    Run a query to Download Data