SELECT

TRUNC(hour, 'day') AS date,

AVG(price) AS price_usd

FROM crosschain.price.ez_prices_hourly

WHERE symbol = 'WETH'

GROUP BY date

),

TokenTransactions AS (SELECT

f.block_timestamp,

f.tx_hash,

'0x' || SUBSTRING(topic_2, 27, 40) AS bridger,

CASE

WHEN f.contract_address = '0x2aabea2058b5ac2d339b163c6ab6f2b6d53aabed' THEN 'USDF'

WHEN f.contract_address = '0x674843c06ff83502ddb4d37c2e09c01cda38cbc8' THEN 'USDT'

WHEN f.contract_address = '0xf1815bd50389c46847f0bda824ec8da914045d14' THEN 'sgUSDC'

WHEN f.contract_address = '0x2f6f07cdcf3588944bf4c42ac74ff24bf56e7590' THEN 'WETH'

ELSE 'UNKNOWN'

END AS token_symbol,

-- Convert amounts based on token decimals

CASE

WHEN f.contract_address IN ('0x2aabea2058b5ac2d339b163c6ab6f2b6d53aabed',

'0x674843c06ff83502ddb4d37c2e09c01cda38cbc8',

'0xf1815bd50389c46847f0bda824ec8da914045d14')

THEN CAST(livequery.utils.udf_hex_to_int(f.data) AS DOUBLE) / 1e6 -- Stablecoins (6 decimals)

WHEN f.contract_address = '0x2f6f07cdcf3588944bf4c42ac74ff24bf56e7590'

THEN (CAST(livequery.utils.udf_hex_to_int(f.data) AS DOUBLE) / 1e18) * e.price_usd -- WETH (18 decimals)

ELSE NULL

END AS token_amount

FROM flow.core_evm.fact_event_logs f

LEFT JOIN EthPrice e

ON TRUNC(f.block_timestamp, 'day') = e.date -- Match price based on the transaction day

WHERE f.contract_address IN ('0x2aabea2058b5ac2d339b163c6ab6f2b6d53aabed', -- USDF