'flow' as chain,

sum(flow_price * GAS_LIMIT /power(10,8)) as fee_volume,

avg(flow_price * GAS_LIMIT /power(10,8)) as avg_fee,

--sum(flow_price * AMOUNT) as trasfer_volume,

--avg(flow_price * AMOUNT) as avg_trasfer

avg(flow_price) as flow_price

FROM flow.core.fact_transactions

--LEFT outer JOIN flow.core.ez_token_transfers

--ON flow.core.ez_token_transfers.tx_id = flow.core.ez_token_transfers.tx_id

left outer JOIN (

SELECT

date_trunc('day', RECORDED_HOUR) as day1,

avg(CLOSE) as flow_price

FROM crosschain.core.fact_hourly_prices

WHERE id LIKE 'wrapped-flow'

GROUP BY 1

) ON date_trunc('day', flow.core.fact_transactions.block_timestamp) = day1

where flow.core.fact_transactions.block_timestamp > CURRENT_DATE - 30

GROUP BY 1