SELECT

date_trunc('week', x.BLOCK_TIMESTAMP) AS week,

COUNT(DISTINCT x.TX_HASH) AS num_bridge_transactions,

COUNT(DISTINCT decoded_log:user) AS num_bridge_users,

SUM(decoded_log:amount/pow(10,18)) AS total_amount_bridged,

LAG(num_bridge_transactions) OVER (ORDER BY week) AS num_bridge_transactions_last_week,

LAG(num_bridge_users) OVER (ORDER BY week) AS num_bridge_users_last_week,

LAG(total_amount_bridged) OVER (ORDER BY week) AS total_amount_bridged_last_week

FROM

blast.core.ez_decoded_event_logs x

WHERE

x.BLOCK_TIMESTAMP >= '2024-02-29 10:00'

and BLOCK_TIMESTAMP < date_trunc('week',current_date)

and event_name in('BridgedDeposit','BridgedWithdrawal')

GROUP BY 1

),

prices as (

trunc(hour,'week') as weeks,

avg(price) as price_usd

from blast.price.ez_prices_hourly

where symbol='ETH'

group by 1

week,

num_bridge_transactions,

num_bridge_transactions_last_week,

(num_bridge_transactions - num_bridge_transactions_last_week) / num_bridge_transactions_last_week * 100 AS num_transactions_diff,

num_bridge_users,

num_bridge_users_last_week,

(num_bridge_users - num_bridge_users_last_week) / num_bridge_users_last_week * 100 AS num_users_diff,

total_amount_bridged*price_usd as total_amount_bridged_usd,

total_amount_bridged_last_week*price_usd as total_amount_bridged_last_week_usd,