SELECT

Date_trunc('day', HOUR) AS Day,

AVG(Price) AS AVG_Price

FROM flow.price.ez_prices_hourly

WHERE

-- HOUR :: Date >= '2024-01-01'

Symbol = 'FLOW'

GROUP By 1

),

Txn_fee AS (

SELECT

Date_trunc('Day', Block_timestamp) AS Date,

TX_ID,

EVENT_DATA:Amount AS Fee

FROM flow.core.fact_events

WHERE Block_timestamp :: Date >= '2024-01-01'

AND EVENT_CONTRACT = 'A.f919ee77447b7497.FlowFees'

AND EVENT_TYPE = 'FeesDeducted'

),

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 Txns,