WITH ETHprice AS (

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,

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'

AND ft.block_timestamp >= '2024-10-31'

SELECT day, count(DISTINCT tx_id) AS transaction,

SUM(transaction) OVER (ORDER BY DAY) AS cum_tx,

sum(amount_usd) as amountUsd,

SUM(amountUsd) OVER (ORDER BY DAY) AS cum_usd,

FROM BridgeData

GROUP by day

order by day desc