quenineDAU copy
    Updated 2024-04-24
    -- forked from rezarwz / DAU @ https://flipsidecrypto.xyz/rezarwz/q/LclJTVYgYqJl/dau

    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'
    QueryRunArchived: QueryRun has been archived