SELECT

TRUNC(hour, 'day') AS day,

AVG(price) AS price

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

GROUP BY day

),

BridgeData AS (

SELECT

TRUNC(block_timestamp, 'day') AS day,

tx_id,

tx_to AS bridger,

amount / POW(10, decimal) AS amounts,

(amount / POW(10, decimal)) * ep.price AS amount_usd,

CASE

WHEN tx_from = 'F7p3dFrjRTbtRp8FRF6qHLomXbKRBzpvBLjtQcfcgmNe' THEN 'deposit'

WHEN tx_to = 'F7p3dFrjRTbtRp8FRF6qHLomXbKRBzpvBLjtQcfcgmNe' THEN 'withdraw'

END AS transaction_type

FROM eclipse.core.fact_transfers ft

JOIN ETHprice ep ON TRUNC(ft.block_timestamp, 'day') = ep.day

WHERE (tx_to = 'F7p3dFrjRTbtRp8FRF6qHLomXbKRBzpvBLjtQcfcgmNe'

OR tx_from = 'F7p3dFrjRTbtRp8FRF6qHLomXbKRBzpvBLjtQcfcgmNe')

AND succeeded = 'TRUE'

SELECT

-- Total deposit and withdraw amounts (USD and ETH)

SUM(CASE WHEN transaction_type = 'deposit' THEN amounts ELSE 0 END) AS total_deposit_eth,

SUM(CASE WHEN transaction_type = 'deposit' THEN amount_usd ELSE 0 END) AS total_deposit_usd,

SUM(CASE WHEN transaction_type = 'withdraw' THEN amounts ELSE 0 END) AS total_withdraw_eth,

SUM(CASE WHEN transaction_type = 'withdraw' THEN amount_usd ELSE 0 END) AS total_withdraw_usd,

-- Net deposit/withdrawal (ETH and USD)

SUM(CASE WHEN transaction_type = 'deposit' THEN amounts ELSE 0 END) -