WITH daily_revenue AS (

SELECT

DATE_TRUNC('month', block_timestamp_hour) AS month,

DATE_TRUNC('day', block_timestamp_hour) AS day,

SUM(total_fees_usd) AS revenue

FROM

axelar.stats.ez_core_metrics_hourly

WHERE

total_fees_usd IS NOT NULL

AND

DATE_PART('quarter', block_timestamp_hour) = 1

AND

DATE_PART('year', block_timestamp_hour) = 2024

GROUP BY

day, month

SELECT

month,

day,

revenue,

revenue / LAG(revenue) OVER (ORDER BY day ASC) AS revenue_ratio,

revenue - LAG(revenue) OVER (ORDER BY day ASC) AS revenue_growth,

(revenue - LAG(revenue) OVER (ORDER BY day ASC)) / LAG(revenue) OVER (ORDER BY day ASC) * 100 AS revenue_percentage_growth,

LEAD(revenue, 1) OVER (ORDER BY day ASC) AS next_day_revenue

FROM

daily_revenue;