with pool_Address as (

SELECT

DISTINCT DECODED_LOG:pool as pool_ca,

DECODED_LOG:token0 as token0_ca,

DECODED_LOG:token1 as token1_ca

FROM

blast.core.ez_decoded_event_logs

where

tx_hash in (

SELECT

TX_HASH

from

blast.core.fact_traces

WHERE

TYPE ILIKE 'create%'

AND to_address IS NOT NULL

AND input IS NOT NULL

AND input != '0x'

AND tx_status = 'SUCCESS'

AND trace_status = 'SUCCESS'

and to_address in(

SELECT

lo2.decoded_log:pool as pool

FROM

blast.core.ez_decoded_event_logs lo

inner join blast.core.ez_decoded_event_logs lo2 on lo2.tx_hash = lo.tx_hash

WHERE

lo.contract_Address = '0x121b5ac4de4a3e6f4171956bc26ceda40cb61a56'

and lo.event_name = 'OpenPosition'

and lo2.event_name = 'Collect'

and lo2.CONTRACT_NAME is not null

)

)

and event_name = 'PoolCreated'

and TX_STATUS = 'SUCCESS'