MEV_query

gistfile1.txt

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