Clickable examples in this post:
- Latest ENS registered domains
- ENS daily registrations
- ENS name taken query
- ENS names that start with 'ape'
- ENS transactions for a wallet address
ENS stands for Ethereum Naming Service, in their words: 'The Ethereum Name Service (ENS) is a distributed, open, and extensible naming system based on the Ethereum blockchain.'
There is a large amount of ENS volume on Ethereum each month, we'll use Sort to dig into it!
Building an ENS SQL query with Sort
Sort allows you to write SQL statements for accessing Blockchain data. All responses are available through an API or can be exported to a spreadsheet.
Let's start by writing our most basic SQL statement for ENS on Sort:
select
*
from
ethereum.transaction t
where
t.to = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
order by
timestamp desc
Results all come back as a single _json column per row, with Sort's SQL flavor you must be specific about the fields requested.
Let's expand on our query above to get more information:
select
t.timestamp,
t.value_eth eth_value,
t.gas.transaction_fee.eth as eth_gas,
t.function.name,
t._id as hash
from
ethereum.transaction t
where
t.to = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
order by
timestamp desc
That's cool! Now we get to see function names for each transaction.
There is one more table to add to our query: 'transaction_log'. The 'transaction_log' table stores the outcome of the transaction, and is very useful in determining what is happening behind the scenes (e.g. maybe additional contracts are called from this transaction).
Latest ENS registered domains (using the 'registerWithConfig' function)
select
params [1].value as name,
params [5].value as expires,
transaction_hash,
timestamp,
transaction_value_eth
from
ethereum.transaction_log t
where
t.transaction_to = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
and name = 'NameRegistered'
and transaction_function_name = 'registerWithConfig'
and function_address = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
order by
timestamp desc
ENS daily registrations
select
DATE(timestamp) as date,
count(*) as number_of_registrations
from
ethereum.transaction t
where
t.to = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
and t.function.name = 'registerWithConfig'
group by
date
order by
date desc
limit
30
ENS name taken query
select
params [1].value as ens_name,
params [5].value as expires,
transaction_hash,
timestamp,
transaction_value_eth
from
ethereum.transaction_log t
where
t.transaction_to = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
and name = 'NameRegistered'
and transaction_function_name = 'registerWithConfig'
and function_address = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
and params [1].value = 'realdisco'
order by
timestamp desc
ENS names that start with 'ape'
select
params [1].value as ens_name,
params [5].value as expires,
transaction_hash,
timestamp,
transaction_value_eth
from
ethereum.transaction_log t
where
t.transaction_to = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
and name = 'NameRegistered'
and transaction_function_name = 'registerWithConfig'
and function_address = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
and REGEXP_LIKE(params [1].value, '^ape')
order by
timestamp desc
ENS transactions for a wallet address
select
t.timestamp,
t.value_eth eth_value,
t.gas.transaction_fee.eth as eth_gas,
t.value_eth + t.gas.transaction_fee.eth as eth_total,
t.function.name,
t._id as hash
from
ethereum.transaction t
where
t.to in (
'0x283af0b28c62c092c9727f1ee09c02ca627eb7f5',
'0xff252725f6122a92551a5fa9a6b6bf10eb0be035',
'0x4976fb03c32e5b8cfe2b6ccb31c09ba78ebaba41',
'0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85',
'0x00000000000C2E074eC69A0dFb2997BA6C7d2e1e',
'0x58774bb8acd458a640af0b88238369a167546ef2',
'0x084b1c3c81545d370f3634392de611caabff8148'
)
and t."from" = '0x179a862703a4adfb29896552df9e307980d19285'
Note: we've been primarily querying the '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5' contract address, however, there are additional ENS contract addresses to consider (mentioned in the last query above).
Primer for querying ENS data with Sort
Hopefully this has been a useful primer for querying ENS data with Sort (and SQL!). Please don't hesitate to join our Discord (and ask lots of questions), visit sort.xyz, or visit Sort documentation to learn more!
Top comments (0)