ErsvanThis week metrics
    Updated 2022-07-26
    select Top, contract, frequency, "Average gas spent by users" from (
    select
    timeframe,
    ROW_NUMBER() OVER (partition by timeframe order by timeframe, count(tx_signer) desc, avg("Average gas spent by users") desc) AS Top,
    tx_signer as contract, count(tx_signer) as frequency, avg("Average gas spent by users") as "Average gas spent by users" from (
    select day, TX_SIGNER, avg as "Average gas spent by users", timeframe from (
    select
    ROW_NUMBER() OVER (partition by date_trunc('day',BLOCK_TIMESTAMP) order by AVG(GAS_USED/pow(10,12)) desc) AS RN,
    date_trunc('day',BLOCK_TIMESTAMP) as day,
    TX_SIGNER,
    AVG(GAS_USED/pow(10,12)) as avg,
    CASE
    WHEN day >= (CURRENT_DATE - 7) and day < CURRENT_DATE then 'Last week'
    WHEN day >= (CURRENT_DATE - 30) and day < CURRENT_DATE -7 THEN 'Last month'
    else null
    end as timeframe
    from flipside_prod_db.mdao_near.transactions
    where timeframe is not null
    group by TX_SIGNER, day
    order by day asc, avg desc
    )
    where RN = 1
    order by day asc
    )
    group by timeframe, tx_signer
    order by timeframe, frequency desc, "Average gas spent by users" desc
    ) where top <= 3 and timeframe = 'Last week'
    Run a query to Download Data