(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),

daily_gas AS

(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"),

raw_algo_prices AS

(SELECT block_hour, price_usd

FROM algorand.core.ez_price_pool_balances

WHERE asset_name = 'ALGO'

AND block_hour::date >= CURRENT_DATE() - INTERVAL '30 days'),

daily_prices AS

(SELECT DATE_TRUNC('day', block_hour) AS "Days",

AVG(price_usd) AS "Average Daily Price in USD"

FROM raw_algo_prices

GROUP BY "Days")