kasadeghDaily top 10 contracts with the highest spending gas (over past month)
    Updated 2022-07-27

    with daily_aggregated_gas_info as (
    select *,
    sum(total_gas) over (partition by contract order by day) as cum_total_gas
    from
    (
    SELECT block_timestamp::date as day
    ,TX_RECEIVER as contract
    ,sum(GAS_USED)/power(10,12) as total_gas
    from near.core.fact_transactions
    where BLOCK_TIMESTAMP::date>=DATEADD(day,-30,CURRENT_DATE)
    GROUP by 1 , 2
    )

    )

    SELECT * from
    (
    SELECT *
    ,row_number() over (partition by day order by cum_total_gas desc ) as rank
    from
    (
    select * from daily_aggregated_gas_info
    )
    )
    WHERE rank <= 10

    Run a query to Download Data