Skip to content

Instantly share code, notes, and snippets.

@cbergz
Created January 2, 2024 23:27
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save cbergz/5ede427bbd12a4f264e79c0bba8dbe8a to your computer and use it in GitHub Desktop.
Save cbergz/5ede427bbd12a4f264e79c0bba8dbe8a to your computer and use it in GitHub Desktop.
MEV_query
WITH
taker AS (
SELECT
"taker" AS user_side,
JSON_EXTRACT_SCALAR(m.attributes, '$.tx_msg_type') AS order_type,
JSON_EXTRACT_SCALAR(m.attributes, '$.timestamp') AS order_submitted_time,
m.publish_time AS numia_publish_time,
(
SELECT
MAX(block_timestamp)
FROM
`numia-data.dydx_mainnet.dydx_block_events`
WHERE
block_height = 4869848) AS block_timestamp,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.order_id.client_id') AS trade_id,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.order_id.subaccount_id.owner') AS subaccount,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.order_id.clob_pair_id') AS clob_pair_id,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.quantums') AS trade_volume,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.side') AS side,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.subticks') AS subticks,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.GoodTilOneof.good_til_block') AS good_til_block
FROM
`numia-data.dydx_mainnet.dydx_mempool_transactions` m
WHERE
1 = 1
AND JSON_EXTRACT_SCALAR(m.attributes, '$.msg_signer') = 'dydx17cyzu9lmhszszspy33jtk6adgzkaunksr4eedt'
AND JSON_EXTRACT_SCALAR(m.attributes, '$.tx_msg_type') = '/dydxprotocol.clob.MsgPlaceOrder'
AND JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.order_id.clob_pair_id') = '29'
AND JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.side') = '1'
AND m.publish_time < (
SELECT
MAX(block_timestamp)
FROM
`numia-data.dydx_mainnet.dydx_block_events`
WHERE
block_height = 4869848)
),
maker AS (
SELECT
"maker" AS user_side,
JSON_EXTRACT_SCALAR(m.attributes, '$.tx_msg_type') AS order_type,
JSON_EXTRACT_SCALAR(m.attributes, '$.timestamp') AS order_submitted_time,
m.publish_time AS numia_publish_time,
(
SELECT
MAX(block_timestamp)
FROM
`numia-data.dydx_mainnet.dydx_block_events`
WHERE
block_height = 4869848) AS block_timestamp,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.order_id.client_id') AS trade_id,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.order_id.subaccount_id.owner') AS subaccount,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.order_id.clob_pair_id') AS clob_pair_id,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.quantums') AS trade_volume,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.side') AS side,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.subticks') AS subticks,
JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.GoodTilOneof.good_til_block') AS good_til_block
FROM
`numia-data.dydx_mainnet.dydx_mempool_transactions` m
WHERE
1 = 1
AND JSON_EXTRACT_SCALAR(m.attributes, '$.msg_signer') = 'dydx14dltc2w6y3dhf0naz8luglsvjt0vhvswm2j6d0'
AND (JSON_EXTRACT_SCALAR(m.attributes, '$.tx_msg_type') = '/dydxprotocol.clob.MsgPlaceOrder' OR JSON_EXTRACT_SCALAR(m.attributes, '$.tx_msg_type') = '/dydxprotocol.clob.MsgCancelOrder')
AND JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.order_id.clob_pair_id') = '29'
AND JSON_EXTRACT_SCALAR(CAST(m.data AS string), '$.order.side') = '2'
AND CAST(JSON_EXTRACT(CAST(m.data AS string), '$.order.GoodTilOneof.good_til_block') AS INT64) >= 4869848
AND m.publish_time < (
SELECT
MAX(block_timestamp)
FROM
`numia-data.dydx_mainnet.dydx_block_events`
WHERE
block_height = 4869849) ## using the next block for maker to catch last microsecond additions
)
SELECT
*
FROM
maker
UNION ALL
SELECT
*
FROM
taker
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment