DEV Community

Sort
Sort

Posted on • Edited on

ENS Registration Data with Sort

Clickable examples in this post:

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

View results on sort.xyz

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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)

Read next

philip_zhang_854092d88473 profile image

The Superior Alternative to Thunder Client with 100% Postman Script Compatibility

Philip -

vania_gomes profile image

Entendendo props e useState no React: Como Passar e Gerenciar Dados Entre Componentes

Vânia Gomes -

sarahthomasdev profile image

Which iOS Performance Testing Tools should be used in 2025?

Sarah Thomas -

farhan_ahmedfarhan41230 profile image

how can I add search functionality in this code?

Farhan Ahmed (Farhan41230301394) -