WITH PRICE AS

(

SELECT

DATE_TRUNC('DAY',HOUR) AS DATE

,TOKEN_ADDRESS

,AVG(PRICE) AS Price

FROM

aptos.price.ez_prices_hourly

GROUP BY 1,2

),

T1 AS

(SELECT

BLOCK_TIMESTAMP

,ACCOUNT_ADDRESS AS Pool_address

,SPLIT(SPLIT(EVENT_TYPE,'<')[1],',')[0] AS Token_contract0

,SPLIT(SPLIT(EVENT_TYPE,'<')[1],',')[1] AS Token_contract1

,CASE WHEN EVENT_DATA['x_in']='0' THEN EVENT_DATA['x_out']*-1 ELSE EVENT_DATA['x_in'] END AS Amount_raw0

,CASE WHEN EVENT_DATA['y_in']='0' THEN EVENT_DATA['y_out']*-1 ELSE EVENT_DATA['y_in'] END AS Amount_raw1

,TX_HASH

FROM

aptos.core.fact_events

WHERE

SPLIT(EVENT_RESOURCE,'<')[0]='SwapEvent'

AND

SUCCESS='TRUE'

AND

tx_type = 'user_transaction'

AND

EVENT_MODULE IN ('liquidity_pool')

),