SELECT

DATE_TRUNC('month', block_timestamp) AS month,

SUM(amount_in_usd) AS monthly_trading_volume,

COUNT(DISTINCT origin_from_address) AS active_users,

AVG(amount_in_usd) AS avg_transaction_size

FROM avalanche.defi.ez_dex_swaps

WHERE block_timestamp >= '2024-01-01'

AND platform = 'trader-joe-v1' OR platform = 'trader-joe-v2'

AND amount_in_usd IS NOT NULL

GROUP BY month

LIMIT 1000

),

monthly_fees AS (

SELECT

DATE_TRUNC('month', block_timestamp) AS month,

SUM(tx_fee) AS total_fees,

AVG(gas_used) AS avg_gas_fees,

SUM(gas_used) AS total_gas_fees

FROM avalanche.core.fact_transactions

WHERE block_timestamp >= '2024-01-01'

AND tx_fee IS NOT NULL

AND gas_used IS NOT NULL

GROUP BY month

),

joe_token_metrics AS (

SELECT

DATE_TRUNC('month', hour) AS month,

MAX(price) AS joe_price_usd

FROM avalanche.price.ez_prices_hourly

WHERE hour >= '2024-01-01'

AND price IS NOT NULL

AND symbol = 'JOE'

GROUP BY month

),