SELECT trunc(hour, 'day') as date,

AVG(price) AS price_usd,

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

GROUP BY date

),

DailyActivity AS (

SELECT

'ethereum' AS chain,

DATE_TRUNC('day', block_timestamp) AS day,

COUNT(DISTINCT tx_hash) AS daily_transactions,

COUNT(DISTINCT from_address) AS daily_users,

SUM (value *ep.price_usd) AS daily_volume

FROM ethereum.core.fact_transactions ef

JOIN Ethprice ep

ON day = ep.date

WHERE to_address = '0xfdf7c22ca4704dfef46e7e5ef53dca1d5a9f8e12'

AND block_timestamp >= '2024-05-24'

AND status = 'SUCCESS'

GROUP BY chain, day

UNION ALL

SELECT

'optimism' AS chain,

DATE_TRUNC('day', block_timestamp) AS day,

COUNT(DISTINCT tx_hash) AS daily_transactions,

COUNT(DISTINCT from_address) AS daily_users,

SUM(value * ep.price_usd) AS daily_volume

FROM optimism.core.fact_transactions

JOIN Ethprice ep

ON day = ep.date

WHERE to_address = '0xfdf7c22ca4704dfef46e7e5ef53dca1d5a9f8e12'

AND block_timestamp >= '2024-05-24'

AND status = 'SUCCESS'