SELECT DATE_TRUNC('week', a.block_timestamp) as day, symbol, COUNT(DISTINCT a.origin_from_address) as num_unique_users_out, --in unique user data is not correct, need to update, counting unique relayers not bridge users

COUNT(*) as num_transactions_out, SUM(amount_usd) as vol_usd_out, SUM(tx_fee) as sum_fee_eth_out

FROM ethereum.core.ez_token_transfers a LEFT JOIN ethereum.core.fact_transactions b

ON a.tx_hash = b.tx_hash

WHERE a.to_address = '0x4d9079bb4165aeb4084c526a32695dcfd2f77381'

AND a.origin_function_signature = '0x49228978'

AND symbol IS NOT NULL

AND amount_usd IS NOT NULL

GROUP BY day, symbol

),

in_ethereum AS (

SELECT DATE_TRUNC('week', a.block_timestamp) as day, symbol, COUNT(DISTINCT a.origin_from_address) as num_unique_users_in,

COUNT(*) as num_transactions_in, SUM(amount_usd) as vol_usd_in, SUM(tx_fee) as sum_fee_eth_in

FROM ethereum.core.ez_token_transfers a LEFT JOIN ethereum.core.fact_transactions b

ON a.tx_hash = b.tx_hash

WHERE a.to_address = '0x4d9079bb4165aeb4084c526a32695dcfd2f77381'

AND (a.origin_function_signature = '0x89a153cc'

OR a.origin_function_signature = '0xac9650d8')

AND symbol IS NOT NULL

AND amount_usd IS NOT NULL

GROUP BY day, symbol

),

optimism_prices AS (

SELECT DATE_TRUNC('DAY', hour) as day, token_address, symbol,

AVG(price) / AVG(POWER(10, decimals)) as price_multiplier

FROM optimism.core.fact_hourly_token_prices

GROUP BY day, token_address, symbol

),

polygon_prices AS (

SELECT DATE_TRUNC('DAY', hour) as day,

CASE WHEN symbol = 'WETH' OR symbol = 'ETH' THEN '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619'

WHEN symbol = 'WBTC' THEN '0x1bfd67037b42cf73acf2047067bd4f2c47d9bfd6'