SELECT

DATE_TRUNC('WEEK', block_timestamp) AS weekly_timeframe,

COUNT(DISTINCT tx_hash) AS total_transactions,

COUNT(DISTINCT from_address) AS active_users,

SUM(tx_fee) AS total_gas

FROM

arbitrum.core.fact_transactions

WHERE

block_timestamp >= '2024-01-01'

AND block_timestamp <= '2024-12-31'

AND status = 'SUCCESS'

GROUP BY

weekly_timeframe

),

usd_conversion AS (

SELECT

avg(price) AS eth_price_usd

FROM

arbitrum.price.ez_prices_hourly

WHERE

symbol = 'ETH'

AND hour >= '2024-01-01'

AND hour <= '2024-12-31'

),

weekly_fees_in_usd AS (

SELECT

wd.weekly_timeframe,

wd.total_transactions,

wd.active_users,

wd.total_gas * (uc.eth_price_usd) AS total_gas_usd

FROM

weekly_data wd

CROSS JOIN

usd_conversion uc