Trades

Availability

This collection is available for the Ethereum, Polygon, Arbitrum, and Base.

Points-of-Presence	Tables
eu-west-1	defi_trades_ethereum_mainnet_v1 defi_trades_polygon_mainnet_v1 defi_trades_arbitrum_mainnet_v1 defi_trades_base_mainnet_v1

Methodology

The table is built by extracting data from DEXes activity and then normalizing it to fit a unified data model.

We currently support the following DEXes:

• Uniswap V2

• Uniswap V3

• Curve

We extract data from any contract compatible with one of the above DEXes ABI.

It means that any DEXes that forked or tried to be compatible at the ABI level with these contracts will be indexed automatically.

Table Schema

Column Name	Column Type	Description
chain_name	string	Name of the chain (ethereum, arbitrum, polygon, ...).
chain_network_name	string	name of the network (mainnet).
block_hash	string	Block hash encoded as binary string
block_number	uint64	Block height
transaction_index	uint64	The index of the transaction in the block
timestamp	datetime	UNIX timestamp for when the block was collated
decoder_name	string	The internal name of the decoder used to decode this trade (uniswap_v2_trade, curve_trade)
factory	string	The address of the DEX factory contract (if any)
contract	string	The address of the DEX pair/pool the actually executed the trade
sender	string	The address of the account the called the contract
receiver	string	The address of the account that received the swapped amount
origin	string	The address of the EOA that triggered the transaction
token_sold_address	string	The address of the sold token
token_sold_symbol	string	The symbol of the sold token
token_sold_raw_amount	uint256	The amount of token sold
token_sold_amount	float64	The amount of token sold divided by pow(10, decimals) where decimals is the number of decimals declared by the token (USDT has 6 decimals)
token_bought_address	string	The address of the bought token
token_bought_symbol	string	The symbol of the bought token
token_bought_raw_amount	uint256	The amount of token bought
token_bought_amount	float64	The amount of token bought divided by pow(10, decimals) where decimals is the number of decimals declared by the token (USDT has 6 decimals)
price	float64	The price of the trade, computed by doing: token_bought_amount / token_sold_mount

Usage

The query below makes use of the defi_trades_ethereum_mainnet_v1 table to get the necessary data to display the well-known candlestick chart for the price of USDC (0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48) on the Uniswap V3 USDC/ETH Pool (0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640) for the last 30 days.

select 
  date_trunc('day', timestamp) as date,
  argMin(price, timestamp) as open,
  argMax(price, timestamp) as close,
  min(price) as _min,
  max(price) as _max
from defi_trades_ethereum_mainnet_v1
where contract = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'
and token_sold_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
and timestamp >= now() - interval 30 day
group by date
order by date desc