Returns the latest ENS registration for an address
Parameters
address
(address as hex string, default: 0x48D004a6C175dB331E99BeAf64423b3098357Ae7)
Returns
name_claim_block
(int64)account_address
(address as hex string)name
(string)text_key
(string)name_expiry_timestamp
(int64)
Query
WITH latest_block_numbers AS (
SELECT
account_address,
labelhash,
node,
MAX(block_number) AS latest_block_number
FROM
@sim.ens_primary_names
WHERE
account_address = LOWER($address)
GROUP BY
account_address,
labelhash,
node
ORDER BY
latest_block_number DESC
LIMIT 1
),
latest_expiries AS (
SELECT
labelhash,
MAX(expiry_timestamp) AS name_expiry_timestamp
FROM
@sim.ens_expiries
WHERE
labelhash IN (
SELECT DISTINCT labelhash
FROM latest_block_numbers
)
GROUP BY
labelhash
),
latest_text_keys AS (
SELECT
text_key,
node,
MAX(block_number) AS latest_bn
FROM
@sim.ens_texts
WHERE
node IN (
SELECT DISTINCT node
FROM latest_block_numbers
)
GROUP BY
text_key,
node
),
latest_texts AS (
SELECT
text_key,
text,
node
FROM
@sim.ens_texts
WHERE
text_key IN (
SELECT DISTINCT text_key
FROM latest_text_keys
)
AND node IN (
SELECT DISTINCT node
FROM latest_text_keys
)
AND block_number IN (
SELECT DISTINCT latest_bn
FROM latest_text_keys
)
)
SELECT DISTINCT
e.block_number AS name_claim_block,
e.account_address,
e.name,
t.text_key,
t.text,
x.name_expiry_timestamp
FROM
@sim.ens_primary_names e
INNER JOIN latest_block_numbers lbs ON lbs.latest_block_number = e.block_number
INNER JOIN latest_expiries x ON x.labelhash = e.labelhash
LEFT JOIN latest_texts t ON t.node = e.node
WHERE
e.account_address = LOWER($address)
AND e.is_forward_resolved = TRUE;