WITH Solana_Transactions AS (

SELECT BLOCK_TIMESTAMP, TX_ID, SIGNERS[0] AS ADDRESS, FEE/POW(10,9) "Gas used in Sol"

FROM solana.core.fact_transactions

WHERE BLOCK_TIMESTAMP::DATE > CURRENT_DATE -31 -- AND STATUS='SUCCESS'

),

SOL_PRICE AS (

SELECT DATE_TRUNC('HOUR', RECORDED_HOUR) AS DAYS, AVG(CLOSE) AS PRICE

FROM solana.core.fact_token_prices_hourly

WHERE SYMBOL = 'MSOL' AND DAYS > CURRENT_DATE - 31

GROUP BY DAYS

),

SOL_GAS_USED_IN_USD_IN_TRANSACTIONS AS (

SELECT BLOCK_TIMESTAMP::DATE AS DAYS,TX_ID,ADDRESS, "Gas used in Sol", "Gas used in Sol" * PRICE AS "Gas used in USD"

FROM Solana_Transactions JOIN SOL_PRICE ON (BLOCK_TIMESTAMP::DATE = DAYS)

),

SOL_GAS_USED_IN_USD_DAILY AS (

SELECT DAYS, COUNT(TX_ID) AS "Number of transactions - Solana",

SUM("Gas used in Sol") AS "Gas used in Sol - Solana",

SUM ("Gas used in USD") AS "Gas used in USD - Solana",

"Gas used in USD - Solana" / "Number of transactions - Solana" AS "Average gas used per TX in USD - Solana",

"Gas used in Sol - Solana" / "Number of transactions - Solana" AS "Average gas used per TX - Solana"

FROM SOL_GAS_USED_IN_USD_IN_TRANSACTIONS GROUP BY DAYS

SELECT SOL_GAS_USED_IN_USD_DAILY.Days,

("Gas used in USD - Solana"), "Number of transactions - Solana", "Average gas used per TX in USD - Solana"

FROM SOL_GAS_USED_IN_USD_DAILY