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