pool_data AS (

value:TOKEN0_ADDRESS::string as token0_address,

value:TOKEN1_ADDRESS::string as token1_address,

value:FEE::numeric as fee,

value:FEE_PERCENT::numeric as fee_percent,

value:TICK_SPACING::numeric as tick_spacing,

value:POOL_ADDRESS::string as pool_address

FROM (

SELECT

livequery.live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/42a28f2a-5745-4383-8019-7297aeefddaf/data/latest') as response

), lateral FLATTEN (input => response:data)

),

base_swaps AS (

SELECT

*,

regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,

CONCAT('0x', SUBSTR(topics [1] :: STRING, 27, 40)) AS sender,

CONCAT('0x', SUBSTR(topics [2] :: STRING, 27, 40)) AS recipient,

utils.udf_hex_to_int(

's2c',

segmented_data [0] :: STRING

) :: FLOAT AS amount0_unadj,

utils.udf_hex_to_int(

's2c',

segmented_data [1] :: STRING

) :: FLOAT AS amount1_unadj,

utils.udf_hex_to_int(

's2c',

segmented_data [2] :: STRING

) :: FLOAT AS sqrtPriceX96,

utils.udf_hex_to_int(