DEX Pools Tables Overview
Overview
A DEX, short for decentralized exchange, is a type of peer-to-peer marketplace where cryptocurrency traders can conduct transactions directly with one another. By eliminating the need for intermediaries such as banks and brokers, DEXs facilitate the core promise of cryptocurrency, which is to enable financial transactions without centralized control.
In Santiment Queries, we collect data from protocols that provide liquidity pools (aggregators are not included):
- Balancer
- Bamboodefi
- Bancor-v3
- ChickenSwap
- Clipper
- Curve
- DefiPlaza
- DefiSwap
- DoDo
- Elk
- EmpireDex
- KwikSwap
- KyberSwap
- MooniSwap
- RadioShack
- Saddle
- ShibaSwap
- StandardTech
- Sum Swap
- SushiSwap
- Swapr
- Synapse
- Uniswap v2/v3
- YouSwap
This information gives you the ability to check:
- the DEX market activity
- the smart money movement on certain tokens
- information about liquidity pools
- the most active tokens, addresses, and DEX protocols
- different DEX protocols and to compare them
List of DEX Tables
All our tables that contain DEX-related data have the string 'dex' in their name. You can easily find them by running the following query:
1
SHOW TABLES LIKE '%dex%'
1
2
3
4
┌─name───────────────────┐
│ dex_pools │
│ dex_pools_trades │
└────────────────────────┘
Exploring the Tables
We can inspect the tables using the commands we learned in Exploration.
Below, we have listed all the DEX pools tables with their columns, brief descriptions, and some example queries that you can use to explore the tables.
dex_pools
The dex_pools
table is designed to store information about liquidity pool creation events on DEX protocols. It has the following columns:
- dt (DateTime): Date and time of the pool deployment.
- chain (String): The chain that the pool is deployed on.
- tx_hash (String): Hash of the transaction.
- log_index (UInt32): Log index of the pool event.
- project_name (String): Name of the DEX or projects, such as uniswap_v3, curve, dydx, etc.
- factory_address (Nullable(String)): Address of the factory that created the pool; NULL if there is no factory (e.g., clipper.exchange).
- pool_address (String): Address of the pool.
- token_address (Array(Nullable(String))): Array of token addresses used in the pool.
- fee (Nullable(Float64)): Fee of the pool in percentage; NULL if not available or variable.
- computed_at (DateTime): The timestamp when the pool was inserted into the table.
dex_pools_trades
The dex_pools_trades
is a table designed to store trading events on DEX protocols. It has the following columns:
- dt (DateTime): Date and time of trade
- chain (String): The chain that the pool is deployed on
- tx_hash (String): Hash of the transaction in which the trade has been included
- log_index (UInt32): Log index of trade event
- trade_index (UInt32): The sequence order of the trade in the transaction. For example, if there are 5 trades in a transaction, the trade index should be 0 to 4 accordingly.
- pool_address (String): Address of the pool
- router_address (String): Address of the router used in this transaction. A router routes trade orders to the most appropriate pool of liquidity for efficient execution and optimal pricing based on available liquidity.*
- from (String): Sender address of the transaction.**
- to (String): Receiver address of the transaction
- token_in (Nullable(String)): Address of token sent, NoneType if it's ETH
- token_out (Nullable(String)): Address of token received
- amount_in (UInt256): The amount of the token sent
- amount_out (UInt256): The amount of the token received
- asset_in_ref_id (UInt64): Reference ID of the currency contract that was sent which can be used to retrieve additional information about the asset from the
asset_metadata
table. - asset_out_ref_id (UInt64): Reference ID of the currency contract that was received.
- computed_at (DateTime): The timestamp when the trade was inserted into the table.
* In decentralized exchanges (DEXs), a router is used to direct transactions between different blockchain networks or between different liquidity pools within the same network. When a user wants to make a trade on a DEX, the router identifies the best path for the trade to take in order to get the best possible price. The router can also split the trade into smaller orders and route them through multiple liquidity pools in order to find the best overall price. Additionally, the router can be used to manage liquidity and minimize the impact of large trades on the market by routing the trade through multiple liquidity pools. By doing so, the router ensures that the trade does not cause significant price movements in any single pool, which could adversely affect the price of the traded asset.
Note about from and to: They can be equal when there is an internal router transaction, then the address in from will be the same as to and also the router_address**. However, it is also possible to have routed transactions in which they are not equal, such as when the transaction is to or from the router or when a router is used for a normal trade between addresses.
Sample Queries
Volume of a specific token on a specific exchange for the last 30 days
1
2
3
4
5
6
7
8
9
10
SELECT
SUM(dpt.amount_in)/1e6 as total_amount
FROM
dex_pools_trades dpt
INNER JOIN dex_pools dp
ON dp.pool_address = dpt.pool_address
WHERE
dp.project_name = 'uniswap_v3'
AND dpt.token_in = LOWER('0xdAC17F958D2ee523a2206206994597C13D831ec7') --USDT
AND dt >= now() - interval 30 DAY
Test in Queries
Number of trades that occurred in the past 30 days on a specific exchange
1
2
3
4
5
6
7
8
9
SELECT
COUNT(*) AS trade_count
FROM
dex_pools_trades dpt
INNER JOIN dex_pools dp
ON dp.pool_address = dpt.pool_address
WHERE
dp.project_name = 'curve'
AND dt >= now() - interval 30 DAY
Test in Queries
Unique Traders in the Past 30 Days on DODO Exchange
1
2
3
4
5
6
7
8
9
SELECT
COUNT(DISTINCT "from") AS unique_trader
FROM
dex_pools_trades dpt
INNER JOIN dex_pools dp
ON dp.pool_address = dpt.pool_address
WHERE
dp.project_name = 'dodo'
AND dt >= now() - interval 30 DAY
Test in Queries
Number of Pools on a Specific Exchange
1 2 3 4 5 6
SELECT COUNT(DISTINCT pool_address) AS pool_count FROM dex_pools WHERE project_name = 'sushi_v2'
Test in Queries
Pool Fees Ranked Descending
1 2 3 4 5 6
SELECT pool_address, project_name, fee FROM dex_pools ORDER BY fee DESC, project_name DESC LIMIT 50
Test in Queries
Projects Ranked by Number of Pools
1 2 3 4 5 6 7 8
SELECT project_name, MIN(dt) as start_date, COUNT(DISTINCT pool_address) AS pool_count FROM dex_pools GROUP BY project_name ORDER BY pool_count DESC
Test in Queries