SELECT

Date_trunc('day', block_timestamp) AS Date,

event_data:amount as fee,

TX_ID

FROM flow.core.fact_events

WHERE block_timestamp::date >= '2024-01-01'

AND event_contract = 'A.f919ee77447b7497.FlowFees'

and event_Type = 'FeesDeducted'

),

Flow_Price AS(

SELECT

hour::date as day,

avg(price) as avg_price

FROM flow.price.ez_prices_hourly

WHERE symbol = 'FLOW'

GROUP BY 1

),

Final AS (

SELECT

Date,

Tx_id,

fee,

fee*avg_price AS feeUSD

FROM Txn_fee a

JOIN flow_price b

ON a.date = b.day

Date,

COUNT(DISTINCT TX_ID) AS Transactions,

SUM(Fee) AS Fee_Flow,

SUM(feeUSD) AS Fee_USD,