ENS Registration Data with Sort
Published: 2022-07-19 23:04:33

_Clickable examples in this post:_

- [Latest ENS registered domains](https://sort.xyz/query/62d5f985667f5ef099bc6e81)
- [ENS daily registrations](https://sort.xyz/query/62d58a1e663a6a902f723ff0)
- [ENS name taken query](https://sort.xyz/query/62d587fcb8281dc0b0369755)
- [ENS names that start with 'ape'](https://sort.xyz/query/62d5886e663a6a902f723fea)
- [ENS transactions for a wallet address](https://sort.xyz/query/62d6286b667f5ef099bc6e8d)

ENS stands for **E**thereum **N**aming **S**ervice, [in their words](https://docs.ens.domains): _**'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](https://sort.xyz) 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](https://sort.xyz):

```
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
```

[View results on sort.xyz](https://sort.xyz/query/62d5f985667f5ef099bc6e81)


## 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
```

- [View results on sort.xyz](https://sort.xyz/query/62d58a1e663a6a902f723ff0)

## 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
```

- [View Results on sort.xyz](https://sort.xyz/query/62d587fcb8281dc0b0369755)

## 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
```

- [View results on sort.xyz](https://sort.xyz/query/62d5886e663a6a902f723fea)

## 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'
```

- [View results on sort.xyz](https://sort.xyz/query/62d6286b667f5ef099bc6e8d)

_**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](https://discord.gg/sort) (and ask lots of questions), visit [sort.xyz](https://sort.xyz), or visit [Sort documentation](https://docs.sort.xyz) to learn more!