SELECT

DATE_TRUNC('day', hour) AS date,

AVG(price) AS price_usd

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0x0a6e7ba5042b38349e437ec6db6214aec7b35676'

GROUP BY DATE_TRUNC('day', hour)

),

ambient AS (

SELECT

Block_timestamp,

tx_hash,

origin_from_address AS swapper,

MAX(CAST(livequery.utils.udf_hex_to_int(data) AS DOUBLE)) / 1e18 AS swell_amount

FROM swell.core.fact_event_logs

WHERE contract_address = '0x2826d136f5630ada89c1678b64a61620aab77aea'

AND origin_to_address IN (

'0xaaaaaaaa82812f0a1f274016514ba2ca933bf24d',

'0xc29a46cd4c484bc3cf39409f246f3f27727cd713',

'0xe5c689d34e03611ac55a603831b00606a8800514'

)

AND topic_0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

AND Block_timestamp >= '2024-12-24'

GROUP BY Block_timestamp, tx_hash, origin_from_address

),

UserVolume AS (

SELECT

a.swapper,

SUM(a.swell_amount * e.price_usd) AS total_usd_volume

FROM ambient a

LEFT JOIN EthPrice e ON DATE_TRUNC('day', a.Block_timestamp) = e.date

GROUP BY a.swapper

SELECT

CASE