select

livequery.live.udf_api('https://flipsidecrypto.xyz/api/v2/queries/9a9f9912-99c0-4d96-8331-6f22976df14f/data/latest') as response

),

token_mappings as (

select

value,

parse_json(value:CLEANED_JSON) as cleaned,

cleaned:name::string as name_raw,

cleaned:symbol::string as symbol_raw,

cleaned:token_id::string as token_id,

cleaned:decimals::string as decimals_raw,

utils.udf_hex_to_string(substr(utils.udf_int_to_hex(split(name_raw, 'u')[0]::string) ,3)) as name,

utils.udf_hex_to_string(substr(utils.udf_int_to_hex(split(symbol_raw, 'u')[0]::string) ,3)) as symbol,

split(decimals_raw, 'u')[0]::int as decimals

from raw_tokens , lateral flatten (input => response:data)

),

raw_pools as (

select

livequery.live.udf_api('https://flipsidecrypto.xyz/api/v2/queries/f55d87d8-d995-4e3b-b2ae-d3202cc4eba1/data/latest') as response

),

pools as (

select

value,

value:DATA:ampl_coef::string as ampl_coef,

value:DATA:id::string as pool_address,

split(value:DATA:swap_fee::string, 'u')[0]::int as swap_fee,

split(value:DATA:pool_type::string, 'u')[0]::int as pool_type,