WITH daily_prices AS (

SELECT

DATE_TRUNC('day', hour) AS day,

AVG(price) AS avg_daily_price

FROM solana.price.ez_prices_hourly

WHERE token_address = 'So11111111111111111111111111111111111111112'

AND hour >= CURRENT_DATE - INTERVAL '30 days'

-- AND hour > '2024-01-01'

AND hour < CURRENT_DATE + INTERVAL '1 day'

GROUP BY day

),

daily_revenue_sol AS (

SELECT

DATE_TRUNC('day', block_timestamp) AS day,

SUM(amount) AS revenue_sol

FROM solana.core.fact_transfers

WHERE tx_to = 'CebN5WGQ4jvEPvsVU4EoHEpgzq1VV7AbicfhtW4xC9iM'

AND mint = 'So11111111111111111111111111111111111111112'

AND block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

AND block_timestamp < CURRENT_DATE + INTERVAL '1 day'

GROUP BY day

SELECT

dr.day,

dr.revenue_sol,

dr.revenue_sol * dp.avg_daily_price AS revenue_usd,

SUM(revenue_usd) over(order by dr.day) as cumulative_usd_revenue

FROM daily_revenue_sol dr