date_trunc('hour', block_timestamp) as day,

'solana' as chain,

sum(sol_price * fee/power(10,9)) as fee_volume,

avg(sol_price * fee/power(10,9)) as avg_fee,

sum(sol_price * CASE WHEN PRE_BALANCES[0] - POST_BALANCES[0] > 0 THEN PRE_BALANCES[0] - POST_BALANCES[0] ELSE POST_BALANCES[0] - PRE_BALANCES[0] END/power(10,9)) as trasfer_volume,

avg(sol_price * CASE WHEN PRE_BALANCES[0] - POST_BALANCES[0] > 0 THEN PRE_BALANCES[0] - POST_BALANCES[0] ELSE POST_BALANCES[0] - PRE_BALANCES[0] END/power(10,9)) as avg_trasfer,

(sum(sol_price * fee/power(10,9)) /sum(sol_price * CASE WHEN PRE_BALANCES[0] - POST_BALANCES[0] > 0 THEN PRE_BALANCES[0] - POST_BALANCES[0] ELSE POST_BALANCES[0] - PRE_BALANCES[0] END/power(10,9))) * 1000000 as gas_per_million_transfered,

avg(sol_price) as sol_price

FROM

solana.core.fact_transactions

left outer JOIN (

SELECT

date_trunc('hour', RECORDED_HOUR) as day1,

avg(CLOSE) as sol_price

FROM solana.core.fact_token_prices_hourly

WHERE symbol LIKE 'SOL'

GROUP BY 1

)

ON date_trunc('day', block_timestamp) = day1

WHERE block_timestamp > CURRENT_DATE - 30

GROUP BY 1