ETH Carbon Footprint
The app is called ETH Carbon Footprint. Click here to check it out!
Pass an Ethereum address along with some block bounds to calculate historical gas usage and transaction fees on for that address on mainnet.
As for how I built it, the process was pretty straightforward. Using the API Studio feature on Terminal, I constructed two SQL queries as shown below. (Full code available here).
const gasOut = await db.raw( `SELECT sum(r."gasUsed") FROM public_transactions t LEFT JOIN public_receipts r ON t."hash" = r."transactionHash" WHERE t."from"=lower(?) AND t."blockNumber" < ? AND t."blockNumber" > ? AND t.network=? AND r.network=? `, [address, upperBound, lowerBound, network, network]); gasUsed = gasOut.rows.sum;
const feesOut = await db.raw(` SELECT sum(t."gasPrice" * r."gasUsed") FROM public_transactions t LEFT JOIN public_receipts r ON t."hash" = r."transactionHash" WHERE t."from"=lower(?) AND t."blockNumber" < ? AND t."blockNumber" > ? AND t.network=? AND r.network=? `, [address, upperBound, lowerBound, network, network]); feesPaid = feesOut.rows.sum;
How It Works!
Doing this same query directly on a node would take prohibitively long given that you would have to query all receipts for that address and then begin to sum the values. Using API Studio allows the total gas and transaction fees to be queried in a matter of seconds. We were able to process this information quickly using the Terminal caches, which store all of the blockchain data in a PostgreSQL allowing for fast access to both the db and Web3 nodes in API studio.
In the rest of this article I’ll step through how the query works. If you aren’t interested in reading a walkthrough and would rather test it on your own, click here and add the “GasAndFeesByAccount” API to your Terminal project.
As for the query itself, It might seem a bit scary at first glance. If we hone in on the execution logic it’s really straightforward. I’ll outline how the gas usage query works, but like I mentioned earlier, the two implementations are very similar and the same general intuition can be applied to the fees query.
The query is an async function and its output is referenced by the gasOut variable. It looks like a traditional SQL query with some minor nuance introduced by the knex.js framework. Note how the second argument to the ‘db.raw’ method is an array. The elements of the array are derived from the post params. These elements replace the “?’s” in the query body in the order in which they appear. For example, the question mark in `WHERE t.”from”=lower(?)` is replaced by the value of the first element in the array (address).
We begin by requiring all of the necessary parameters (i.e. address, lower and upper bounds, network). From there we start with the first query. For ease of explanation I will go through the SQL query from bottom to top. We pass in our target network to specify which Ethereum database to fetch data from. In the case of the deployed ETH Carbon Footprint website, the network is hard coded to “ethereum_main”. Next, we define the block bounds. The block bounds are determined in the post params and are referenced in the `upperBound` and `lowerBound` variables respectively. After that, we cast the Ethereum address from the post params to lowercase because, for the sake of simplicity, addresses are stored as lowercase in the Postgres.
The first three lines of the query define its actual implementation given the constraints we derived from the post params. First we initialize the alias “t” to refer to the public_transactions table.. Using a LEFT JOIN, these transaction hashes are matched with their inputs in the public_receipts table or “r” to derive the gas usage for a given transaction. Summing up these values gets us with the total gas consumed by your address. The fees implementation follows the same line of thinking, with an additional step where gas is multiplied by the gas price.
Copy And Test It For Yourself!
Go ahead and fork the API into your own Terminal project to try it out for yourself. If you make changes or have other gas utilities you want to build, feel free to reach out to me on the Terminal discord channel and I’ll add you as a contributor to the project. The frontend is also open-sourced on Github. Check it out here!