ErsvanThis week metrics
Updated 2022-07-26
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
›
⌄
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