SELECT

recorded_hour,

price,

currency,

symbol

FROM sei.price.ez_prices

),

volume AS (

SELECT

v.amount,

v.currency,

v.decimal,

p.symbol,

p.recorded_hour,

(v.amount / NULLIF(POW(10, v.decimal), 0)) * p.price AS volume_usd

FROM sei.core.fact_transfers v

JOIN prices p

ON v.currency = p.currency

AND v.block_timestamp = p.recorded_hour

WHERE v.amount IS NOT NULL

AND v.decimal IS NOT NULL

AND p.price IS NOT NULL

SELECT

DATE_TRUNC('month', recorded_hour) AS month,

SUM(volume_usd) AS monthly_total_amount_usd

FROM volume

GROUP BY month

ORDER BY month;