kaibladeAlgorand Daily Amount Spent on Gas in USD
Updated 2022-11-14
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
›
⌄
WITH algo_price AS
(SELECT asset_name, price_usd
FROM algorand.core.ez_price_pool_balances
WHERE asset_name = 'ALGO'
AND block_hour::date = CURRENT_DATE()
ORDER BY block_hour DESC
LIMIT 1
),
algo_all_trx AS
(SELECT *, 'ALGO' AS asset
FROM algorand.core.fact_transaction
WHERE block_timestamp::date >= CURRENT_DATE() - INTERVAL '30 days'),
algo_combined_data AS
(SELECT trx.block_timestamp, trx.tx_sender, trx.fee*price.price_usd AS gas_cost
FROM algo_all_trx trx
JOIN algo_price price
ON trx.asset = price.asset_name)
SELECT DATE_TRUNC(day, block_timestamp) AS "Days",
SUM(gas_cost) AS "Daily Spent on Gas in USD"
FROM algo_combined_data
GROUP BY "Days"
Run a query to Download Data