SELECT

date_trunc('week', hour) as _date,

avg(price) as avg_price

FROM base.price.ez_prices_hourly

WHERE symbol = 'WETH'

AND _date BETWEEN '2024-01-01' and CURRENT_TIMESTAMP

GROUP BY 1

LIMIT 100

date_trunc('week', BLOCK_TIMESTAMP) as week,

count (DISTINCT tx_hash) as transactions,

count(DISTINCT From_address) as active_users,

sum(value) as amount,

sum(tx_fee) as total_tx_fee,

avg(tx_fee) as avg_tx_fee,

ROUND(sum(tx_fee*avg_price),0) as Total_fee_usd,

ROUND(sum(value*avg_price),0) as Total_volume_usd,

LAG(active_users) OVER (ORDER BY week) as last_week_users,

LAG(transactions) OVER (ORDER BY week) as last_week_transactions,

LAG(Total_volume_usd) OVER (ORDER BY week) as last_week_volume,

((transactions - LAG(transactions) OVER (ORDER BY week))/LAG (transactions) OVER (ORDER BY week))*100 as transaction_diff,

((active_users - LAG(active_users) OVER (ORDER BY week))/LAG (active_users) OVER (ORDER BY week))*100 as active_users_diff,

((Total_volume_usd - LAG(Total_volume_usd) OVER (ORDER BY week))/LAG (Total_volume_usd) OVER (ORDER BY week))*100 as Total_volume_diff

FROM base.core.fact_transactions b

LEFT JOIN price p ON BLOCK_TIMESTAMP::date = p._date

WHERE block_timestamp BETWEEN '2024-05-01' and '2024-08-03'

GROUP BY 1

ORDER BY 1 DESC

LIMIT 100