SELECT

value as value, --total amount being sent

block_timestamp, --timing

DATE_TRUNC('hour', block_timestamp) AS rounded_hour,

from_address as user

FROM

avalanche.core.fact_transactions

WHERE

block_timestamp BETWEEN '2022-01-01' AND '2024-12-31 23:59:59'

),

token_price_data AS (

SELECT

symbol,

price,

hour

FROM

avalanche.price.ez_prices_hourly

WHERE

symbol = 'AVAX'

AND hour BETWEEN '2022-01-01' AND '2024-12-31 23:59:59'

),

transactions_with_prices AS (

SELECT

t.value,

t.rounded_hour,

p.price,

t.value * p.price AS transaction_value,

DATE_TRUNC('month', t.rounded_hour) AS rounded_month,

t.user

FROM

transaction_data t

LEFT JOIN

token_price_data p

ON

t.rounded_hour = p.hour