SELECT

trunc(hour, 'hour') AS hour,

avg(price) AS price

FROM

flow.price.ez_prices_hourly

WHERE

symbol = 'FLOW'

GROUP BY

1

),

monthly_fees AS (

SELECT

trunc(block_timestamp, 'month') AS month,

avg(event_data:amount) AS avg_tx_fee_flow,

avg(event_data:amount * price) AS avg_tx_fee_usd

FROM

flow.core.fact_events x

JOIN

flow_price y ON trunc(x.block_timestamp, 'hour') = y.hour

WHERE

event_contract = 'A.f919ee77447b7497.FlowFees'

AND event_Type = 'FeesDeducted'

GROUP BY

1

ORDER BY

1 ASC

AVG(avg_tx_fee_flow) AS avg_monthly_tx_fee_flow,

AVG(avg_tx_fee_usd) AS avg_monthly_tx_fee_usd

monthly_fees;