select date_trunc('day', block_timestamp) as date, tx:receipt[4]:outcome:executor_id as protocol, (avg(TRANSACTION_FEE) / pow(10,12)) as average_tx_fee,

ROW_NUMBER() OVER (PARTITION BY date ORDER BY average_tx_fee DESC) as n

from near.core.fact_transactions

where block_timestamp >= current_date - interval '1 month'

and protocol != ''

and (

protocol = 'v2.ref-finance.near' OR

protocol = 'v2.ref-farming.near' OR

protocol = 'v1.jumbo_exchange.near' OR

protocol = 'app.nearcrowd.near' OR

protocol = 'backend.v1.pembrock.near' OR

protocol = 'operator.meta-pool.near' OR

protocol = 'v1.orderbook.near' OR

protocol = 'v1.pembrock.near'

)

group by protocol, date

) where n = 1 order by date asc