select

lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token,

TRY_HEX_DECODE_STRING(SUBSTRING(data, 515, 64)) AS name,

TRY_HEX_DECODE_STRING(SUBSTRING(data, 643, 64)) AS symbol

from ronin.core.fact_event_logs

where topics[0] = '0xf372df0af3c16a1deb41b25691dd807a3836ba8f443e83d24c1406ae3748232e'

and contract_address = '0xa54b0184d12349cf65281c6f965a74828ddd9e8f'

and TX_SUCCEEDED

and block_timestamp::date >= '2025-01-20'

,

ron_price as (

select

date_trunc('hour', hour) as date,

avg(price) as ron_price

from ronin.price.ez_prices_hourly

where symbol = 'RON'

and is_native

group by 1

, trade as (

select

block_timestamp,

tx_hash,

origin_from_address,

lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token,

b.symbol as symbol,

lower('0x' || substr(topics[2]::string, 27, 40) :: string) as sender,

lower('0x' || substr(topics[3]::string, 27, 40) :: string) as "to",

REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data,

case when livequery.utils.udf_hex_to_int(segmented_data[0]) = 1

then 'Buy' else 'Sell' end as type,

livequery.utils.udf_hex_to_int(segmented_data[3])/pow(10,18) AS price,

livequery.utils.udf_hex_to_int(segmented_data[1])/pow(10,18) AS amount_in,

livequery.utils.udf_hex_to_int(segmented_data[2])/pow(10,18) AS amount_out,