gas metrics per wallet

Running Total of Gas Paid in USD

1
with alltransactions
2
AS (
3
SELECT
4
block_time,
5
success,
6
gas_price/10^9 AS gas_prices,
7
gas_used,
8
(gas_price*gas_used)/10^18 AS eth_paid_for_tx,
9
hash
10
FROM ethereum.transactions
11
WHERE "from" = CONCAT('\x', substring('{{1. Eth Address}}' from 3))::bytea
12
AND block_time >= '{{2. Start Date}}'
13
AND block_time < '{{3. End Date}}')
14
15
SELECT
16
date_trunc('minute', block_time),
17
SUM(eth_paid_for_tx*price) over (ORDER BY date_trunc('minute', block_time)) AS "Total Gas Fees Paid in USD"
18
FROM alltransactions
19
LEFT JOIN
20
(SELECT
21
minute,
22
price
23
FROM prices.usd
24
WHERE
25
symbol = 'WETH' AND
26
minute > '{{2. Start Date}}') AS prices
27
ON date_trunc('minute', block_time) = minute
28
ORDER BY block_time DESC
Copied!

Running Total of Gas Paid in ETH

1
with alltransactions
2
AS (
3
4
SELECT
5
block_time,
6
success,
7
gas_price/10^9 AS gas_prices,
8
gas_used,
9
(gas_price*gas_used)/10^18 AS eth_paid_for_tx,
10
hash
11
FROM ethereum.transactions
12
WHERE "from" = CONCAT('\x', substring('{{1. Eth Address}}' from 3))::bytea
13
AND block_time >= '{{2. Start Date}}'
14
AND block_time < '{{3. End Date}}')
15
16
SELECT
17
block_time,
18
SUM(eth_paid_for_tx) over (ORDER BY block_time ASC) as "ETH Paid"
19
FROM alltransactions
20
ORDER BY block_time DESC
Copied!

Average Gas Price Paid

1
with alltransactions
2
AS (
3
4
SELECT
5
block_time,
6
success,
7
gas_price/10^9 AS gas_prices,
8
gas_used,
9
(gas_price*gas_used)/10^18 AS eth_paid_for_tx,
10
hash
11
FROM ethereum.transactions
12
WHERE "from" = CONCAT('\x', substring('{{1. Eth Address}}' from 3))::bytea
13
AND block_time >= '{{2. Start Date}}'
14
AND block_time < '{{3. End Date}}')
15
16
SELECT
17
AVG(gas_prices) as "Average Gas Prices"
18
FROM alltransactions
Copied!

Average Gas Fee in USD

1
with alltransactions
2
AS (
3
4
SELECT
5
block_time,
6
success,
7
gas_price/10^9 AS gas_prices,
8
gas_used,
9
(gas_price*gas_used)/10^18 AS eth_paid_for_tx,
10
hash
11
FROM ethereum.transactions
12
WHERE "from" = CONCAT('\x', substring('{{1. Eth Address}}' from 3))::bytea
13
AND block_time >= '{{2. Start Date}}'
14
AND block_time < '{{3. End Date}}')
15
16
SELECT
17
AVG(eth_paid_for_tx*price) AS "AVG Gas Fee per TX Paid in USD"
18
FROM alltransactions
19
LEFT JOIN
20
(SELECT
21
minute,
22
price
23
FROM prices.usd
24
WHERE
25
symbol = 'WETH' AND
26
minute > '{{2. Start Date}}') AS prices
27
ON date_trunc('minute', block_time) = minute
Copied!

Highest Gas Fee Paid in USD

1
with alltransactions
2
AS (
3
4
SELECT
5
block_time,
6
success,
7
gas_price/10^9 AS gas_prices,
8
gas_used,
9
(gas_price*gas_used)/10^18 AS eth_paid_for_tx,
10
hash
11
FROM ethereum.transactions
12
WHERE "from" = CONCAT('\x', substring('{{1. Eth Address}}' from 3))::bytea
13
AND block_time >= '{{2. Start Date}}'
14
AND block_time < '{{3. End Date}}')
15
16
SELECT
17
MAX(eth_paid_for_tx*price) AS "Higest Gas Fee Paid (USD)"
18
FROM alltransactions
19
LEFT JOIN
20
(SELECT
21
minute,
22
price
23
FROM prices.usd
24
WHERE
25
symbol = 'WETH' AND
26
minute > '{{2. Start Date}}') AS prices
27
ON date_trunc('minute', block_time) = minute
Copied!

Gas Fees Paid by Month in USD

1
with alltransactions
2
AS (
3
SELECT
4
block_time,
5
success,
6
gas_price/10^9 AS gas_prices,
7
gas_used,
8
(gas_price*gas_used)/10^18 AS eth_paid_for_tx,
9
hash
10
FROM ethereum.transactions
11
WHERE "from" = CONCAT('\x', substring('{{1. Eth Address}}' from 3))::bytea
12
AND block_time >= '{{2. Start Date}}'
13
AND block_time < '{{3. End Date}}')
14
15
SELECT
16
date_trunc('month', block_time),
17
SUM(eth_paid_for_tx*price) AS "Total Gas Fees Paid in USD"
18
FROM alltransactions
19
LEFT JOIN
20
(SELECT
21
minute,
22
price
23
FROM prices.usd
24
WHERE
25
symbol = 'WETH' AND
26
minute > '{{2. Start Date}}') AS prices
27
ON date_trunc('month', block_time) = minute
28
GROUP BY 1
29
Copied!
Last modified 3mo ago