SELECT

TRUNC(hour, 'day') AS date,

AVG(price) AS price_usd

FROM ethereum.price.ez_prices_hourly

WHERE symbol = 'WETH'

GROUP BY date

),

DecodedData AS (

SELECT

DATE_TRUNC('day', el.Block_timestamp) AS date,

el.tx_hash,

el.origin_from_address,

el.origin_to_address,

regexp_substr_all(SUBSTR(el.data, 3), '.{64}') AS segmented,

'0x' || SUBSTR(segmented[0], 25, 40) AS bridgers, -- Extract Ethereum address

CAST(livequery.utils.udf_hex_to_int(segmented[1]) AS DOUBLE) / 1e18 AS eth_amount, -- Convert from Wei to ETH

livequery.utils.udf_hex_to_string('7375706572627269646765000000000000000000000000000000000000000000') AS bridge

FROM swell.core.fact_event_logs AS el

WHERE el.origin_from_address = '0xf7ba9ef12995defc5ff47ec0e13252f0e690486a'

AND el.origin_to_address = '0x4200000000000000000000000000000000000007'

AND el.topics[0] = '0xb0444523268717a02698be47d0803aa7468c00acbed2f8bd93a0459cde61dd89'

AND el.Block_timestamp > '2024-11-27'

AND el.tx_succeeded = TRUE

--and tx_hash = '0x4b25f0ef1e4fc9299c91e9fb6dc3f6a162185528ddf40cd667e3cd1633613f6c'

),

UserTx AS (

SELECT

bridgers,

COUNT(tx_hash) AS tx_count -- Count of transactions per user

FROM DecodedData

GROUP BY bridgers