DEV Community 👩‍💻👨‍💻

Sort
Sort

Posted 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)

🌚 Browsing with dark mode makes you a better developer.

It's a scientific fact.