Total gas fees are how blockchains generate revenue. Calculating the mean gas fee is a great benchmark to compare the cost of using multiple chains.

Addresses stored in the contract_address field are in lower case. So when you use an address that is all upper case - e.g C02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2 - you must wrap the address in the lower() function. The AND statement down below would then look like the following:

AND contract_address = lower('C02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

Also, when doing this analysis for Arbitrum you must use the fees_paid column instead of tx_gas_spent * tx_gas_price.

SELECT [signed_at:aggregation] as date
       , avg((tx.tx_gas_spent/pow(10, 18))* toFloat64(tx.tx_gas_price)* prices.price_in_usd) as average_gas_cost
       , sum((tx.tx_gas_spent/pow(10, 18))* toFloat64(tx.tx_gas_price)* prices.price_in_usd) as aggregate_gas_cost
FROM ( 
			SELECT any(tx_hash), tx_gas_spent, tx_gas_price, signed_at
	    FROM blockchains.all_chains 
  		WHERE chain_name = 'eth_mainnet'
  		AND [signed_at:daterange]
			GROUP BY tx_gas_spent, tx_gas_price, signed_at
    ) tx
LEFT JOIN reports.token_prices prices
  ON date_trunc('day', tx.signed_at) = prices.dt
    AND prices.contract_address = 'c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
 GROUP BY date

Want to learn how we got there? 👇

The following query will retrieve all the entries in the all_chains data table.

SELECT * 
FROM blockchains.all_chains
-- This will be quite a large query, so we will need to refine it a bit further!

You are going to transform this query into one that calculates the average and aggregate amount of gas spent on Avalanche. Firstly, lets break down the anatomy of the all_chains table.

The all_chains table provides all the historical block, transaction and log event data for every chain. To see an example of this, run the following query:

SELECT block_height -- block data 
			 , hex(tx_hash) -- transaction data 
			 , tx_gas_price -- transaction data 
			 , tx_gas_spent -- transaction data 
			 , hex(topic0) -- log event data 
FROM blockchains.all_chains
WHERE chain_name = 'eth_mainnet'
AND tx_hash = unhex('9cfb22eac017be365f5b0be733bfba69bf953c95ae0c8d80147f8af1c6255128')

The result 👇

As you can see from the result, all the block and transaction data (block_height, hex(tx_hash), tx_gas_price, tx_gas_spent) gets duplicated for each seperate entry in the log event (hex(topic0)) data. Therefore, you can’t simply sum() and avg() the tx_gas_spent column to find gas fees.

To get a single row of transaction information you need to SELECT each distinct tx_hash . To do so you need to use the aggregate function any() - which selects the first encountered value - to select distinct tx_hash. You can also remove the block_height and hex(topic0) because these are useless for our analysis.

SELECT any(hex(tx_hash)) -- aggregate function
			 , signed_at
			 , tx_gas_price 
			 , tx_gas_spent 
FROM blockchains.all_chains
WHERE chain_name = 'eth_mainnet'
AND tx_hash = unhex('9cfb22eac017be365f5b0be733bfba69bf953c95ae0c8d80147f8af1c6255128')
GROUP BY block_height, tx_gas_price, tx_gas_spent, signed_at

The result 👇 Perfect!

Now you can replace the tx_hash in the WHERE statement with [signed_at:daterange] (so you aren’t just looking at one transaction) and turn the query into a subquery. The subquery can then be joined onto the reports.token_prices table.

SELECT * 
FROM (
SELECT any(tx_hash) -- aggregate function
			 , signed_at
			 , tx_gas_price 
			 , tx_gas_spent 
FROM blockchains.all_chains
WHERE chain_name = 'eth_mainnet'
AND [signed_at:daterange]
GROUP BY block_height, tx_gas_price, tx_gas_spent, signed_at
) data -- alias
			LEFT JOIN reports.token_prices prices
				ON