token_info as (

select

token_address,

symbol,

name,

decimals

from aptos.core.dim_tokens

,prices as (

select

hour::date as p_date,

symbol,

decimals,

token_address,

avg(price) as avg_price

from aptos.price.ez_prices_hourly

GROUP by 1, 2, 3, 4

)

, swaps as (

SELECT

BLOCK_TIMESTAMP,

TX_HASH,

EVENT_DATA,

EVENT_TYPE,

sender as swapper,

CASE

WHEN event_data:x_in :: int = 0 THEN TRIM(SPLIT_PART(SPLIT(event_type, ',')[1], ' ',2) :: string, '>')

WHEN event_data:x_in :: int != 0 THEN TRIM(SPLIT_PART(SPLIT(event_type, ',')[0], '<', 2) :: string, ' ')

END AS token_in,

CASE

WHEN event_data:y_in :: int = 0 THEN TRIM(SPLIT_PART(SPLIT(event_type, ',')[1], ' ',2) :: string, '>')

WHEN event_data:y_in :: int != 0 THEN TRIM(SPLIT_PART(SPLIT(event_type, ',')[0], '<', 2) :: string, ' ')

END AS token_out,

CASE

WHEN event_data : x_in :: int = 0 THEN event_data : y_in :: int