Composite Market Data Analysis
==============================

A series of simple examples are provided showing how to analyze consolidated market data across multiple venues using OneTick Cloud composite databases.
Composite databases aggregate trades and quotes from multiple exchanges and venues into a single unified dataset.

Composite Databases
###################

Composite databases consolidate fragmented liquidity across multiple equity venues for a given region or country.
Consolidating trades, quotes, and providing an NBBO and daily rollup.

This is available for:

* ``AU_COMP`` - Consolidated Across All Australian Equity Venues
* ``CA_COMP`` - Consolidated Across All Canadian Equity Venues
* ``EU_COMP`` - Consolidated Across All European Equity Venues
* ``IN_COMP`` - Consolidated Across All Indian Equity Venues
* ``JP_COMP`` - Consolidated Across All Japanese Equity Venues
* ``KR_COMP`` - Consolidated Across All Korean Equity Venues
* ``MX_COMP`` - Consolidated Across All Mexican Equity Venues
* ``TW_COMP`` - Consolidated Across All Taiwanese Equity Venues
* ``US_COMP`` - Consolidated Across All US Equity Venues

Samples of the Composite datasets covering the first 3 months of 2024 are available for:

* ``CA_COMP_SAMPLE`` - Consolidated Trades & Quotes Across All Canadian Venues
* ``EU_COMP_SAMPLE`` - Consolidated Trades & Quotes Across All European Venues
* ``US_COMP_SAMPLE`` - Consolidated Trades & Quotes Across All US Venues

Data is aggregated by venue in fields such as ``QUOTE_VENUE``, ``TRADE_VENUE``, or ``EXCHANGE`` (for US_COMP).

Quote Count Per Venue
#####################

The number of quotes received from each venue can be calculated by aggregating quote data.
This provides visibility into the trading activity and data volume from each exchange.

.. code-block:: sql
   :caption: Quote Count Per Venue for Canadian Composite (TD symbol)

   select QUOTE_VENUE, COUNT(*) as QUOTE_COUNT
   from CA_COMP_SAMPLE.QTE
   where SYMBOL_NAME='TD'
   and TIMESTAMP >= '2024-01-03 09:30:00 UTC'
   and TIMESTAMP < '2024-01-03 16:00:00 UTC'
   group by QUOTE_VENUE


Trade Count Per Venue
#####################

The number of trades executed on each venue can be calculated by aggregating trade data.
This shows the distribution of trading activity across different exchanges.

.. code-block:: sql
   :caption: Trade Count Per Venue for Canadian Composite (TD symbol)

   select TRADE_VENUE, COUNT(*) as TRADE_COUNT
   from CA_COMP_SAMPLE.TRD
   where SYMBOL_NAME='TD'
   and TIMESTAMP >= '2024-01-03 09:30:00 UTC'
   and TIMESTAMP < '2024-01-03 16:00:00 UTC'
   group by TRADE_VENUE


Volume Traded Across All Symbols Per Venue
############################################

The total volume traded across all symbols for each venue can be calculated using daily summary data.
This shows the overall trading volume aggregated by venue.

Daily volume data from the ``DAY`` table provides pre-aggregated volume statistics, avoiding the need to aggregate individual trades.
The composite venue (empty string ``''``) and primary venue (``'PRIM'``) are typically filtered out to show individual venue volumes.

.. code-block:: sql
   :caption: Volume Traded Across All Symbols Per Venue for US Composite

   select sum(VOLUME) as VOLUME, EXCHANGE
   from US_COMP_SAMPLE_DAILY.DAY
   where SYMBOL_NAME LIKE '%'
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   and EXCHANGE!=''
   and EXCHANGE!='PRIM'
   group by EXCHANGE


Daily Composite Trade Bars
##########################

Returns aggregated Daily OHLCV (Open, High, Low, Close, Volume) data for a specified symbol and date range from composite databases.
Multiple rows are returned per day, with each row representing either the exchange/venue or aggregated composite data.
In US_COMP, venues are identified by the ``EXCHANGE`` field, while other composites use ``VENUE_ID``.

Each daily bar includes comprehensive volume and price information:

* ``VOLUME`` - Total volume
* ``VOLUME_ODD_LOT`` - Volume from odd-lot trades (< 100 shares)
* ``VOLUME_ROUND_LOT`` - Volume from round-lot trades (>= 100 shares)
* ``VOLUME_OFF_EXCHANGE`` - Volume from off-exchange trades
* ``VOLUME_PRE_MARKET`` - Pre-market volume (before market open)
* ``VOLUME_POST_MARKET`` - Post-market volume (after market close)
* ``VOLUME_OPENING_AUCTION`` - Volume from opening auction
* ``VOLUME_CLOSING_AUCTION`` - Volume from closing auction
* ``OPEN_PRICE`` - Opening price
* ``CLOSE_PRICE`` - Closing price
* ``PRICE_OPENING_AUCTION`` - Opening auction execution price
* ``PRICE_CLOSING_AUCTION`` - Closing auction execution price
* ``HIGH_PRICE`` - Highest price during the day
* ``LOW_PRICE`` - Lowest price during the day


.. code-block:: sql
   :caption: Daily Trade Bars for US Composite (CSCO)

   select * from US_COMP_SAMPLE_DAILY.DAY
   where SYMBOL_NAME='CSCO'
   and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York'
   and TIMESTAMP < '2024-01-04 00:00:00 America/New_York'
   limit 1000


Daily Quote NBBO Composite Bars
###############################

Returns aggregated Daily NBBO (National Best Bid and Offer) data from composite quote databases.
Similar to trade bars, multiple rows represent different venues, the primary exchange, and the composite aggregate.
Useful for analyzing best bid/ask pricing across consolidated venues.

.. code-block:: sql
   :caption: Daily Quote NBBO Bars for US Composite (CSCO)

   select * from US_COMP_SAMPLE_DAILY.QTE
   where SYMBOL_NAME='CSCO'
   and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York'
   and TIMESTAMP < '2024-01-04 00:00:00 America/New_York'
   limit 1000


Daily Trade NBBO Composite Bars
###############################

Returns aggregated Daily Trade and NBBO data combined from composite databases.
Provides unified view of both trade activity (OHLCV) and prevailing bid/ask prices on a daily basis.
Useful for complete market structure analysis combining executions with prevailing quotes.

.. code-block:: sql
   :caption: Daily Trade NBBO Bars for US Composite (CSCO)

   select * from US_COMP_SAMPLE_DAILY.NBBO
   where SYMBOL_NAME='CSCO'
   and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York'
   and TIMESTAMP < '2024-01-04 00:00:00 America/New_York'
   limit 1000


NBBO Retrieval from Composite
##############################

Retrieves the consolidated National Best Bid and Offer (NBBO) quote data from the composite. For the US, NBBOs are pre-calculated by the consolidated tape. For other regions, NBBOs are constructed by OneTick across all venues that contribute to the composite. NBBO records include bid and ask prices, sizes, and the exchanges providing the best bid and offer.

.. code-block:: sql
   :caption: NBBO Retrieval from Composite

    select * from US_COMP_SAMPLE.NBBO
    where SYMBOL_NAME='AAPL'
    and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
    and TIMESTAMP < '2024-01-04 00:00:00 UTC'
    limit 1000


Trade Volume to NBBO Calculation
################################

Classifies and aggregates trade volumes by their position relative to the National Best Bid and Offer (NBBO).
This analysis shows how many shares were executed at the bid, at the ask, inside the spread, at the mid price, and outside the NBBO.
Useful for understanding order execution quality, market impact analysis, and trade classification for TCA (Transaction Cost Analysis).

The analysis joins trade data with prevailing NBBO quotes and classifies each trade into one of four categories:

* ``VOLUME_AT_MID`` - Volume executed at the midpoint price
* ``VOLUME_INSIDE_NBBO`` - Volume executed strictly inside the bid-ask spread (between bid and ask, but not at either)
* ``VOLUME_AT_NBBO`` - Volume executed at the bid or ask price
* ``VOLUME_OUTSIDE_NBBO`` - Volume executed outside the NBBO (below bid or above ask)

.. code-block:: sql
   :caption: Trade Volume by NBBO Level for US Composite (CSCO)

   SELECT
       t.SYMBOL_NAME,
       t.EXCHANGE,
       SUM(SIZE_AT_MID) AS VOLUME_AT_MID,
       SUM(SIZE_INSIDE_NBBO) AS VOLUME_INSIDE_NBBO,
       SUM(SIZE_AT_NBBO) AS VOLUME_AT_NBBO,
       SUM(SIZE_OUTSIDE_NBBO) AS VOLUME_OUTSIDE_NBBO,
       COUNT(*) AS TRD_COUNT
   FROM (
       SELECT
           t.SYMBOL_NAME,
           t.PRICE,
           t.SIZE,
           t.EXCHANGE,
           q.BID_PRICE,
           q.ASK_PRICE,
           (q.BID_PRICE + q.ASK_PRICE) / 2 AS MID_PRICE,
           CASE
               WHEN t.PRICE = (q.BID_PRICE + q.ASK_PRICE) / 2 THEN t.SIZE
               ELSE 0
           END AS SIZE_AT_MID,
           CASE
               WHEN t.PRICE > q.BID_PRICE AND t.PRICE < q.ASK_PRICE THEN t.SIZE
               ELSE 0
           END AS SIZE_INSIDE_NBBO,
           CASE
               WHEN t.PRICE = q.BID_PRICE OR t.PRICE = q.ASK_PRICE THEN t.SIZE
               ELSE 0
           END AS SIZE_AT_NBBO,
           CASE
               WHEN t.PRICE < q.BID_PRICE OR t.PRICE > q.ASK_PRICE THEN t.SIZE
               ELSE 0
           END AS SIZE_OUTSIDE_NBBO
       FROM US_COMP_SAMPLE.TRD t, US_COMP_SAMPLE.NBBO q
       WHERE t.SYMBOL_NAME = q.SYMBOL_NAME
           AND sametime_as_existing(t.TIMESTAMP, q.TIMESTAMP, 0) = TRUE
           AND t.SYMBOL_NAME = 'CSCO'
           AND TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
           AND TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
   ) t
   GROUP BY t.SYMBOL_NAME, t.EXCHANGE
   ORDER BY TRD_COUNT DESC


Point in Time Trade and NBBO Snapshot for Composite
####################################################

Retrieves a combined snapshot of trade and NBBO quote data at a specific point in time for a composite dataset, using a lookback period to find the most recent data.

A specific point in time is selected with ``TIMESTAMP`` equal to a specified value. All Symbols are retrieved with ``SYMBOL_NAME LIKE '%'``. A Lookback is defined in seconds, to check for the prevailing trade and NBBO before the selected time, up to the lookback period. Both the TRD and NBBO tables are retrieved from the composite.

.. code-block:: sql
   :caption: Point in Time Trade and NBBO Snapshot for Composite

    select t.SYMBOL_NAME as SYMBOL_NAME,
    t.PRICE as PRICE, t.SIZE as SIZE,
    n.BID_PRICE as BID_PRICE, n.ASK_PRICE as ASK_PRICE
    from US_COMP_SAMPLE.TRD t, US_COMP_SAMPLE.NBBO n     -- Both the TRD and NBBO tables are retrieved
    where t.SYMBOL_NAME LIKE '%'          -- Retrieve All Symbols
    and t.SYMBOL_NAME = n.SYMBOL_NAME
    and TIMESTAMP = '2024-01-03 12:30:00 America/New_York'
    and t.init_lookback = 86400           --how many seconds to look back for prevailing Trade
    and n.init_lookback = 86400           --how many seconds to look back for prevailing NBBO Quote


Spread and Mid from Composite Quotes Per Venue
###############################################

Calculates bid-ask spread and mid-price from composite quotes by venue/exchange, providing venue-level pricing analysis.

Composite datasets include both quotes per venue/exchange and an NBBO. The ``QTE`` table includes the Top of Book Quotes associated with each venue. US_COMP uses the ``EXCHANGE`` field, while other Composites use ``QUOTE_VENUE``. ``SPREAD = ASK_PRICE - BID_PRICE`` and ``MID_PRICE = (BID_PRICE + ASK_PRICE)/2``.

.. code-block:: sql
   :caption: Spread and Mid from Composite Quotes Per Venue

    select EXCHANGE, BID_PRICE, ASK_PRICE,
    (BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
    (ASK_PRICE - BID_PRICE) as SPREAD
    from US_COMP_SAMPLE.QTE
    where SYMBOL_NAME = 'CSCO'
    and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
    and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
    and BID_SIZE > 0 and ASK_SIZE > 0
    limit 1000


Rolling Spread and Mid Statistics from Composite NBBO
#####################################################

Calculates rolling spread and mid-price statistics at each point in time from composite NBBO data, showing how these metrics evolve throughout a trading period.

Composite datasets include both quotes per venue/exchange and an NBBO. The NBBO can either be regulatory in the case of the US, or constructed. Rolling Statistics across the period calculate the Time Weighted Average, High and Low at every point in time for spread and mid-price metrics.

.. code-block:: sql
   :caption: Rolling Spread and Mid Statistics from Composite NBBO

    select
    BID_PRICE, ASK_PRICE, MID_PRICE, SPREAD,
    TW_AVG(MID_PRICE) OVER(order by TIMESTAMP asc) as  ROLLING_TW_AVG_MID_PRICE,
    MAX(MID_PRICE) OVER(order by TIMESTAMP asc) as  ROLLING_HIGH_MID_PRICE,
    MIN(MID_PRICE) OVER(order by TIMESTAMP asc) as  ROLLING_LOW_MID_PRICE,
    TW_AVG(SPREAD) OVER(order by TIMESTAMP asc) as  ROLLING_TW_AVG_SPREAD,
    MAX(SPREAD) OVER(order by TIMESTAMP asc) as  ROLLING_HIGH_SPREAD,
    MIN(SPREAD) OVER(order by TIMESTAMP asc) as  ROLLING_LOW_SPREAD
    from
    (
      -- Calculate the Mid Price and Spread
      select BID_PRICE, ASK_PRICE,
      (BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
      (ASK_PRICE - BID_PRICE) as SPREAD
      from US_COMP_SAMPLE.NBBO
      where SYMBOL_NAME = 'CSCO'
      and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
      and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
      and BID_SIZE > 0 and ASK_SIZE > 0
    )


Spread and Mid from Composite NBBO
##################################

Calculates bid-ask spread and mid-price from composite NBBO (National Best Bid and Offer) data, providing consolidated pricing metrics across all venues.

Composite datasets include both quotes per venue/exchange and an NBBO. The NBBO can either be regulatory in the case of the US, or constructed. SPREAD = ASK_PRICE - BID_PRICE and MID_PRICE = (BID_PRICE + ASK_PRICE)/2.

.. code-block:: sql
   :caption: Spread and Mid from Composite NBBO

    select BID_PRICE, ASK_PRICE,
    (BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
    (ASK_PRICE - BID_PRICE) as SPREAD
    from US_COMP_SAMPLE.NBBO
    where SYMBOL_NAME = 'CSCO'
    and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
    and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
    and BID_SIZE > 0 and ASK_SIZE > 0
    limit 1000


Spread and Mid Statistics from Composite NBBO
##############################################

Aggregates spread and mid-price statistics from composite NBBO quote data, calculating time-weighted averages and extremes across a trading period.

Composite datasets include both quotes per venue/exchange and an NBBO. The NBBO can either be regulatory in the case of the US, or constructed. Statistics across the period calculate the Time Weighted Average, High and Low for spread and mid-price metrics.

.. code-block:: sql
   :caption: Spread and Mid Statistics from Composite NBBO

    select
    TW_AVG(MID_PRICE) as TW_AVG_MID_PRICE,
    MAX(MID_PRICE) as HIGH_MID_PRICE,
    MIN(MID_PRICE) as LOW_MID_PRICE,
    TW_AVG(SPREAD) as TW_AVG_SPREAD,
    MAX(SPREAD) as HIGH_SPREAD,
    MIN(SPREAD) as LOW_SPREAD,
    count(SPREAD) as QTE_COUNT
    from
    (
      -- Calculate the Mid Price and Spread
      select BID_PRICE, ASK_PRICE,
      (BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
      (ASK_PRICE - BID_PRICE) as SPREAD
      from US_COMP_SAMPLE.NBBO
      where SYMBOL_NAME = 'CSCO'
      and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
      and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
      and BID_SIZE > 0 and ASK_SIZE > 0
    )


Spread and Mid Statistics from Composite Venues
###############################################

Aggregates spread and mid-price statistics from composite venue quotes, grouped by exchange to show venue-level pricing analysis.

Composite datasets include both quotes per venue/exchange and an NBBO. The QTE table includes the Top of Book Quotes associated with each venue. US_COMP uses the EXCHANGE field, while other Composites use QUOTE_VENUE. Statistics across the period calculate the Time Weighted Average, High and Low, grouped by EXCHANGE.

.. code-block:: sql
   :caption: Spread and Mid Statistics from Composite Venues

    select
    EXCHANGE,
    TW_AVG(MID_PRICE) as TW_AVG_MID_PRICE,
    MAX(MID_PRICE) as HIGH_MID_PRICE,
    MIN(MID_PRICE) as LOW_MID_PRICE,
    TW_AVG(SPREAD) as TW_AVG_SPREAD,
    MAX(SPREAD) as HIGH_SPREAD,
    MIN(SPREAD) as LOW_SPREAD,
    count(SPREAD) as QTE_COUNT
    from
    (
      -- Calculate the Mid Price and Spread
      select EXCHANGE, BID_PRICE, ASK_PRICE,
      (BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
      (ASK_PRICE - BID_PRICE) as SPREAD
      from US_COMP_SAMPLE.QTE
      where SYMBOL_NAME = 'CSCO'
      and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
      and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
      and BID_SIZE > 0 and ASK_SIZE > 0
    )
    group by EXCHANGE


European Composite Data
########################

The European Composite (``EU_COMP``) consolidates trades and quotes across multiple European venues. Symbols in ``EU_COMP`` are defined as ISINs rather than ticker symbols, as ticker symbols are not consistent across European venues. Trades include additional MiFID-related fields such as trading venue, currency, trade period, and book type.

European Composite Trade Data Retrieval
########################################

Retrieves trade records from the European Composite across multiple venues. Symbols are defined as ISINs to provide consistency across European trading venues.

.. code-block:: sql
   :caption: European Composite Trade Data Retrieval

    SELECT * FROM EU_COMP_SAMPLE.TRD
    WHERE SYMBOL_NAME in ('GB00BH4HKS39','FR001400J770','DE0005140008','GB00BP6MXD84')
    and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
    and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
    LIMIT 1000


European Composite Bar Creation
################################

Returns OHLC prices for the European Composite aggregated by venue and currency into fixed time intervals. The query applies grouping by ``TRADE_VENUE`` and ``CURRENCY`` to return calculated bars per venue, since the European Composite includes trades in multiple currencies.

.. code-block:: sql
   :caption: European Composite 5-Minute Bar Creation by Venue

    SELECT TRADE_VENUE, CURRENCY,
    FIRST(PRICE) as FIRST_PRICE,
    MAX(PRICE) as HIGH_PRICE,
    MIN(PRICE) as LOW_PRICE,
    LAST(PRICE) as LAST_PRICE,
    VWAP(PRICE_FIELD_NAME=PRICE, SIZE_FIELD_NAME=SIZE) as VWAP_PRICE,
    SUM(SIZE) as VOLUME,
    SUM(PRICE*SIZE) as TRADE_VALUE,
    COUNT(SIZE) as TRADE_COUNT
    FROM EU_COMP_SAMPLE.TRD
    WHERE SYMBOL_NAME = 'GB00BH4HKS39'
    and TIMESTAMP >= '2024-01-03 10:23:01 Europe/London'
    and TIMESTAMP < '2024-01-03 10:27:48 Europe/London'
    group by TRADE_VENUE, CURRENCY


European Composite Bar Creation with Fill Forward
##################################################

Returns OHLC prices for the European Composite with the last price filled forward for periods without trades. Uses ``TIME_SERIES_TYPE='STATE_TS'`` to extend the last price forward into subsequent time buckets. Filters trades to continuous trading periods and lit order book trades.

.. code-block:: sql
   :caption: Returns OHLC prices for the European 5-Minute Bar Creation with Fill Forward

    SELECT TRADE_VENUE,CURRENCY,
    FIRST(PRICE) as FIRST_PRICE,
    MAX(PRICE) as HIGH_PRICE,
    MIN(PRICE) as LOW_PRICE,
    LAST(PRICE,TIME_SERIES_TYPE='STATE_TS') as LAST_PRICE,
    VWAP(PRICE_FIELD_NAME=PRICE, SIZE_FIELD_NAME=SIZE) as VWAP_PRICE,
    SUM(SIZE) as VOLUME,
    COUNT(SIZE) as TRADE_COUNT
    FROM EU_COMP.TRD
    WHERE SYMBOL_NAME = 'GB00BH4HKS39'
    and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
    and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
    and TRADE_PERIOD = '-'
    and BOOK_TYPE = '0'
    group by time_bucket(INTERVAL '5' MINUTE), TRADE_VENUE, CURRENCY


European Trade Daily Composite Bars
####################################

Returns pre-calculated Daily OHLCV data from the European Composite for specified symbols and date ranges. Multiple rows are returned per day representing different venues and currencies. The composite aggregate is represented by empty string values for ``VENUE_ID`` and ``CURRENCY`` fields.

.. code-block:: sql
   :caption: European Trade Daily Composite Bars


    SELECT * FROM EU_COMP.DAY
    WHERE SYMBOL_NAME ='GB00BH4HKS39'
    and TIMESTAMP >= '2024-01-03 00:00:00 Europe/London'
    and TIMESTAMP < '2024-01-04 00:00:00 Europe/London'
    LIMIT 1000