dydx

Chain ID: dydx_mainnet

For dydx you'll find the following tables (new schema):

1. Raw Tables

2. Example Queries

Raw Tables

When querying in BigQeury make sure you add the prefix dydx_ to all of the name tables below. For example, the table blocks should be:

select * from numia-data.dydx_mainnet.dydx_blocks

blocks

Contains each validated block in the chain. block_timestamp is only available in this table, so you need to do an inner join on block_height to get block_timestamp to the other tables.

Field	Type
block_height	STRING
block_timestamp	TIMESTAMP
chain_id	STRING
proposer_address	STRING
validators_hash	STRING

block_events

Contains all events and their corresponding attributes partitioned by block_height. Does not contain events triggered from messages and transactions signed by addresses. Events and their corresponding event_index are relative to the parent block , not the message or transactions. Events are identified by the event_type column and contains attributes by identified by attribute_key.

Field	Type
block_height	INTEGER
block_timestamp	TIMESTAMP
event_index	INTEGER
event_type	STRING
event_attributes	JSON
source	STRING
ingestion_timestamp	TIMESTAMP

message_event

Contains all events and their corresponding attributes partitioned by tx_id and message_index. Events and their corresponding event_index are relative to the parent message_index which is relative to the tx_id. Events are identified by the event_type column and attributes can be extracted in event_atributes. Events are linked to transactions via column tx_id.

Field	Type
block_height	STRING
block_timestamp	TIMESTAMP
tx_hash	STRING
tx_index	INTEGER
message_index	INTEGER
event_index	INTEGER
event_type	STRING
event_attributes	JSON
ingestion_timestamp	TIMESTAMP

transactions

Contains relevant data associated with a transaction.

Field	Type
block_height	STRING
chain_id	STRING
tx_id	STRING
tx_status	STRING
tx_log_message	STRING
memo	STRING
fee	STRING
tx_code	INTEGER
tx_index	INTEGER
gas_used	INTEGER
gas_wanted	INTEGER
ingestion_timestamp	TIMESTAMP

tx_messages

Contains the messages and their corresponding payload. Partitioned by tx_id where the combination of tx_id and message_index makes up the primary key.

Field	Type
block_height	STRING
chain_id	STRING
tx_id	STRING
tx_index	STRING
message_index	STRING
message_type	STRING
messafe	JSON
tx_code	INTEGER
schema_version	INTEGER
block_timestamp	INTEGER
ingestion_timestamp	TIMESTAMP

validators

Contains all information about active and non-active validators.

operator_address	STRING
account_address	STRING
consensus_pubkey	STRING
jailed	BOOLEAN
status	STRING
tokens	STRING
moniker	STRING
identity	STRING
website	STRING
security_contact	STRING
details	STRING
unbonding_height	INTEGER
unbonding_time	DATETIME
commission_rate	FLOAT
commission_max_rate	FLOAT
commission_max_change_rate	FLOAT
commission_update_time	DATETIME
min_self_delegation	BIGNUMERIC
snapshot_height	INTEGER
snapshot_time	DATETIME
ingestion_timestamp	TIMESTAMP

Example Queries

Example queries for a given address, example used here is: dydx000000000000000000000000000000000000000 (replace with address that is of interest)

Trades

This outputs all the trades for a given address

SELECT *
  FROM `numia-data.dydx_mainnet.dydx_match` 
  WHERE taker = 'dydx000000000000000000000000000000000000000'
     OR maker = 'dydx000000000000000000000000000000000000000'

Deposits

This outputs all the deposits for a given address

SELECT *
  FROM `numia-data.dydx_mainnet.dydx_deposit`
 WHERE recipient = 'dydx000000000000000000000000000000000000000'

Transfers

This outputs all the subaccount transfers for a given address

SELECT *
  FROM `numia-data.dydx_mainnet.dydx_transfer`
 WHERE sender = 'dydx000000000000000000000000000000000000000' 
    OR recipient = 'dydx000000000000000000000000000000000000000'

Withdrawals

This outputs all the withdrawals for a given address

SELECT *
  FROM `numia-data.dydx_mainnet.dydx_withdrawal`
  WHERE sender = 'dydx000000000000000000000000000000000000000'

Maker Rebate

This calculates all the maker rebates for a given address

SELECT SUM(maker_order_fee_quote_quantums)/1e6 AS maker_rebate_usdc
  FROM `numia-data.dydx_mainnet.dydx_match` 
  WHERE maker = 'dydx000000000000000000000000000000000000000'

Funding

This outputs all the funding for a given address

SELECT *
  FROM `numia-data.dydx_mainnet.dydx_settled_funding` 
 WHERE subaccount = 'dydx000000000000000000000000000000000000000'

Wallet transfers

This outputs all the wallet to wallet transfers of DYDX for a given sender

SELECT  tm.tx_id
      , CAST(JSON_EXTRACT_SCALAR(JSON_EXTRACT(tm.message, '$.amount')[0], '$.amount') AS NUMERIC)/1e18 AS amount
      , JSON_EXTRACT_SCALAR(tm.message, '$.from_address') AS sender
      , JSON_EXTRACT_SCALAR(tm.message, '$.to_address') AS recipient
  FROM `numia-data.dydx_mainnet.dydx_tx_messages` tm
  -- need to join to ensure the txn didn't fail
  JOIN `numia-data.dydx_mainnet.dydx_transactions` t
   ON tm.tx_id = t.tx_id 
   AND t.tx_log_message IS NULL
 WHERE tm.message_type = '/cosmos.bank.v1beta1.MsgSend' 
   AND JSON_EXTRACT_SCALAR(JSON_EXTRACT(tm.message, '$.amount')[0], '$.denom') = 'adydx'
   AND JSON_EXTRACT_SCALAR(tm.message, '$.from_address') = 'dydx000000000000000000000000000000000000000'

Trading Rewards

This outputs the trading rewards received in dydx in each block for each address

SELECT block_height
     , JSON_EXTRACT_SCALAR(event_attributes, '$.recipient')
     , CAST(SUBSTR(JSON_EXTRACT_SCALAR(event_attributes, '$.amount'), 1, LENGTH(JSON_EXTRACT_SCALAR(event_attributes, '$.amount'))-5) AS NUMERIC)/1e18  AS dydx_received
  FROM `numia-data.dydx_mainnet.dydx_block_events` 
 WHERE event_type = 'transfer'
 -- dydx16wrau2x4tsg033xfrrdpae6kxfn9kyuerr5jjp is the rewards treasury address
 AND JSON_EXTRACT_SCALAR(event_attributes, '$.sender') = 'dydx16wrau2x4tsg033xfrrdpae6kxfn9kyuerr5jjp'
 AND JSON_EXTRACT_SCALAR(event_attributes, '$.recipient') = 'dydx000000000000000000000000000000000000000'