SELECT

hour::date AS date,

token_address,

decimals,

symbol,

AVG(price) AS token_price_usd

FROM kaia.price.ez_prices_hourly

GROUP BY 1, 2, 3, 4

),

swaps AS (

SELECT

tx_hash,

block_timestamp,

origin_from_address AS swapper,

REPLACE(topics[2], '0x000000000000000000000000', '0x') AS token_in_contract,

REPLACE(topics[3], '0x000000000000000000000000', '0x') AS token_out_contract,

utils.udf_hex_to_int('0x' || SUBSTRING(data, 67, 64)) AS amount_in_unadj,

utils.udf_hex_to_int('0x' || RIGHT(data, 64)) AS amount_out_unadj

FROM kaia.core.fact_event_logs

WHERE tx_succeeded

AND topics[0] = '0x20efd6d5195b7b50273f01cd79a27989255356f9f13293edc53ee142accfdb75'

AND origin_to_address = '0xf50782a24afcb26acb85d086cf892bfffb5731b5'

),

main AS (

SELECT

swaps.*,

NVL(a.symbol, c.symbol) AS symbol_in,

NVL(b.symbol, d.symbol) AS symbol_out,

amount_in_unadj / POW(10, IFF(token_in_contract = '0x0000000000000000000000000000000000000000', 18, NVL(a.decimals, 18))) AS amount_in,

amount_out_unadj / POW(10, IFF(token_out_contract = '0x0000000000000000000000000000000000000000', 18, NVL(b.decimals, 18))) AS amount_out,

NVL(amount_in, 0) * NVL(c.token_price_usd, 0) AS amount_in_usd,

NVL(amount_out, 0) * NVL(d.token_price_usd, 0) AS amount_out_usd,

COALESCE(amount_in_usd, amount_out_usd, 0) AS amount_usd