date_range AS (

SELECT DATE_TRUNC('day', CURRENT_DATE) - INTERVAL '3 MONTHS' AS start_date

),

ranked_prices AS (

SELECT

DATE_TRUNC('day', hour) AS day,

price,

ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('day', hour) ORDER BY hour DESC) AS rank

FROM kaia.price.ez_prices_hourly

WHERE is_native = true

),

kaia_daily_prices AS (

SELECT day, price

FROM ranked_prices

WHERE rank = 1

),

kaia_daily_fees AS (

SELECT

DATE_TRUNC('day', t.BLOCK_TIMESTAMP) AS day,

SUM(t.tx_fee) AS daily_fees_native,

MAX(p.price) AS daily_price

FROM kaia.core.fact_transactions t

JOIN kaia_daily_prices p

ON DATE_TRUNC('day', t.BLOCK_TIMESTAMP) = p.day

WHERE t.BLOCK_TIMESTAMP >= (SELECT start_date FROM date_range)

GROUP BY DATE_TRUNC('day', t.BLOCK_TIMESTAMP)

),

kaia_fee_metrics AS (

SELECT

'kaia' AS dataset,

SUM(daily_fees_native * daily_price) AS total_fees_usd,

SUM(daily_fees_native) AS total_fees_native

FROM kaia_daily_fees

GROUP BY 'kaia'

),