TheLaughingManFriends - Address to ENS map
    Updated 2023-08-11
    with base_accounts as (
    SELECT
    DISTINCT ORIGIN_FROM_ADDRESS as account

    from base.core.fact_event_logs
    WHERE
    ORIGIN_FUNCTION_SIGNATURE = '0x6945b123'
    AND contract_address = '0xcf205808ed36593aa40a44f10c7f7c2f67d4a4d4'
    AND concat('0x',RIGHT(regexp_substr_all(SUBSTR(DATA,3,len(DATA)),'.{64}')[1], 40)) = ORIGIN_FROM_ADDRESS
    ),

    account_funders as (
    SELECT
    t.tx_hash
    , t.block_timestamp
    , to_address as account
    , from_address as funder
    , eth_value
    , ENS_NAME
    , rank() OVER (PARTITION BY to_address ORDER BY t.block_timestamp ASC) as rank
    from base.core.fact_transactions t
    LEFT JOIN crosschain.core.ez_ens e ON t.from_address=e.owner
    WHERE 1=1
    AND ETH_VALUE>0
    AND ENS_SET!='N'
    AND to_address IN (SELECT account as to_address from base_accounts)
    qualify rank=1
    LIMIT 10
    )

    SELECT * from account_funders