SELECT

DATE_TRUNC(DAY, ft.BLOCK_TIMESTAMP) AS date,

SUM(ft.RESOURCE_FEE/10000000.0) AS total_resource_fees_xlm,

SUM(ft.INCLUSION_FEE_CHARGED/10000000.0) AS total_inclusion_fees_xlm,

SUM(ft.FEE_CHARGED/10000000.0) AS total_fees_xlm

FROM stellar.core.fact_transactions ft

WHERE ft.BLOCK_TIMESTAMP >= DATEADD(DAY, -30, CURRENT_DATE)

GROUP BY 1

),

daily_prices AS (

SELECT

DATE_TRUNC(DAY, hour) AS date,

AVG(price) as avg_daily_price

FROM crosschain.price.ez_prices_hourly

WHERE symbol = 'XLM'

AND blockchain = 'stellar'

AND is_native = TRUE

AND hour >= DATEADD(DAY, -30, CURRENT_DATE)

GROUP BY 1

SELECT

df.date,

df.total_resource_fees_xlm * dp.avg_daily_price AS total_resource_fees_usd,

df.total_inclusion_fees_xlm * dp.avg_daily_price AS total_inclusion_fees_usd,

df.total_fees_xlm * dp.avg_daily_price AS total_fees_usd,

SUM(df.total_fees_xlm * dp.avg_daily_price) OVER (ORDER BY df.date) AS cumulative_fees_usd

FROM daily_fees df

LEFT JOIN daily_prices dp ON df.date = dp.date

ORDER BY df.date;