(SELECT block_timestamp, block_id, tx_id, fee/1e9 AS fee, 'SOL' AS symbol

FROM solana.core.fact_transactions

WHERE block_timestamp::date >= CURRENT_DATE() - INTERVAL '30 days'

),

sol_price AS

(SELECT symbol, close

FROM

solana.core.fact_token_prices_hourly

WHERE symbol ='SOL'

ORDER BY recorded_hour DESC

LIMIT 1),

sol_combined_data AS

(SELECT trx.block_timestamp, trx.fee * price.close AS gas_cost

FROM sol_all_trx trx

JOIN sol_price price

ON trx.symbol = price.symbol

)

SELECT date_trunc(hour, block_timestamp) AS "Hours",

AVG(gas_cost) AS "Average Gas Price in USD"

FROM sol_combined_data

GROUP BY "Hours"