WITH p0 AS (

SELECT token_address AS mint

, DATE_TRUNC('hour', recorded_hour) AS hour

, AVG(close) AS price

FROM solana.price.ez_token_prices_hourly p

WHERE recorded_hour::date >= CURRENT_DATE - 30

AND is_imputed = FALSE

GROUP BY 1, 2

), p1 AS (

SELECT token_address AS mint

, DATE_TRUNC('day', recorded_hour) AS date

, AVG(close) AS price

FROM solana.price.ez_token_prices_hourly p

WHERE recorded_hour >= CURRENT_DATE - 30

AND is_imputed = FALSE

GROUP BY 1, 2

), p2 AS (

SELECT token_address AS mint

, DATE_TRUNC('week', recorded_hour) AS week

, AVG(close) AS price

FROM solana.price.ez_token_prices_hourly p

WHERE recorded_hour >= CURRENT_DATE - 30

AND is_imputed = FALSE

GROUP BY 1, 2

), t0 AS (

SELECT block_timestamp::date AS date

, SUM(amount * COALESCE(p0.price, p1.price, 0)) AS fees_usd

FROM solana.core.fact_transfers t

LEFT JOIN p0

ON p0.hour = DATE_TRUNC('hour', t.block_timestamp)

AND p0.mint = t.mint

LEFT JOIN p1

ON p1.date = DATE_TRUNC('day', t.block_timestamp)

AND p1.mint = t.mint