date_trunc('hour', block_timestamp) as hour ,

count(distinct tx_id) as tx_count,avg (price_usd) as flow_price,

sum (event_data:amount)*avg (price_usd) as total_fee_usd,

total_fee_usd/tx_count as fee_per_tx,

count(distinct block_height) as block_count,

total_fee_usd/block_count as fee_per_block

from flow.core.fact_events

join flow.core.fact_prices on date_trunc('hour', block_timestamp) = date_trunc('hour', timestamp)

where source = 'coinmarketcap'

and symbol = 'FLOW'

and block_timestamp >= CURRENT_DATE - 30

and event_type = 'FeesDeducted'

group by 1