How to calculate transaction fees or gas fees in USD for a specific ETH address on Dune Analytics?

Question

I am trying to write a query to calculate total transaction fees paid in USD. For that I am writing the below query but somehow join messes things up.

SELECT
 date_trunc('minute',block_time),
   hash,
   (gas_used*gas_price/1e18) as total_price_in_eth
 FROM
   ethereum.transactions
   join 
   (select minute,price from prices.usd where symbol = 'ETH') as prices on date_trunc('minute',block_time) = minute
 WHERE
   "from" = xxxxxxxx

I am able to calculate total fees in gwei but I want to calculate transaction fees paid in USD. Can someone point me in right direction what I am doing wrong here?

Answer 1

The biggest issue with your code is that it is not following style conventions. While style conventions aren't mandatory, they help us write code that are easy to debug. You haven't added any columns for showing the gas fee in USD. Here's how you might solve this:

    SELECT
      date_trunc('minute', block_time),
      hash,
      (gas_used * gas_price / 1e18) AS gas_price_in_eth,
      -- Add a new col for gas fee in usd
      ((gas_used * gas_price / 1e18) * pricestable."price") AS gas_price_in_usd
    FROM
      ethereum.transactions
      JOIN (
        SELECT
          minute,
          price
        FROM
          prices.usd
        WHERE
          symbol = 'ETH'
      ) AS pricestable ON date_trunc('minute', block_time) = minute
    WHERE
      "from" = {{Enter ETH Address}}

Link to my query.