ZookNEAR contracts with most average gas used in past week
Updated 2022-07-27
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
28
29
›
⌄
-- Credit to Sohei_mk for sharing a prior version of this query. I modified their query to rank order the result into a daily top 3.
with daily_transactions as (
select
BLOCK_TIMESTAMP::DATE as date,
tx_receiver as contract,
sum(gas_used/1e12) as total_gas_used,
count(TXN_HASH) as tx_count,
(total_gas_used/tx_count) as avg_gas_used
from flipside_prod_db.mdao_near.transactions
where BLOCK_TIMESTAMP > CURRENT_DATE - 31
and BLOCK_TIMESTAMP < CURRENT_DATE
group by 1,2
)
Select *
from (
select
DATE,
contract,
avg_gas_used,
rank() over (partition by DATE order by avg_gas_used desc) as rank
from daily_transactions
where tx_count > 100 -- only include contracts that are active
Group by DATE,2,3
) as tmp
where rank < 4
Run a query to Download Data