Orderbook level market stats. for vertex protocol

    This SQL query retrieves aggregated data from the `arbitrum.vertex.ez_market_stats` table, combining on-chain data with data from Vertex's ticker V2 API. It calculates the total fee amount and total amount in USD for each ticker_id from the on-chain data. Additionally, it aggregates the last price and quote volume for each ticker_id from the Vertex ticker V2 API data within the last 24 hours. The results are joined based on the ticker_id column and ordered by the third column in descending order.

    Introduction:

    This SQL query retrieves comprehensive market metrics by combining on-chain data with data from Vertex's ticker V2 API. By aggregating information from both sources, it offers valuable insights into market activity and performance over a specified time period.

    Metrics Covered:

    1. Total Fee Amount: Sum of all fee amounts incurred across different market pairs.
    2. Total Amount in USD: Sum of all trade amounts in USD across different market pairs.
    3. Last Price: Maximum last transacted price recorded within the last 24 hours for each market pair.
    4. Quote Volume: Total quote volume traded within the last 24 hours for each market pair.

    By analyzing these metrics, stakeholders can gain a holistic understanding of market trends, liquidity, and trading activity across various market pairs on the Vertex platform.

    db_img