Basics
======
A series of simple examples are provided showing how to retrieve, filter, aggregate and join datasets, using OneTick Cloud sample databases.


Data Retrieval
##############

Data can be retrieved by selecting
 
* Database & Table (separated by a period.  e.g. ``LSE_SAMPLE.TRD``).
* Symbol or Symbols using the field ``SYMBOL_NAME``
* Time Range using field ``TIMESTAMP`` with ``>=`` and ``<`` or Time equal to, using ``TIMESTAMP =``

All three must be provided for results to be retrieved.


.. code-block:: sql
   :caption: Simple Retrieval Example

   select  PRICE, SIZE, EXCHANGE
   from US_COMP_SAMPLE.TRD                  
   where SYMBOL_NAME='CSCO'                    
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'  
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   limit 10


Time Zones for Filtering
########################

All times are assumed to be UTC.
The filtered time range can be specified in a given time zone by appending the time zone to the timestamp.

.. code-block:: sql
   :caption: Filtering on US Eastern Time Zone

   select PRICE, SIZE, EXCHANGE
   from US_COMP_SAMPLE.TRD                  
   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'
   limit 10


Time Zones for Resultsets
#########################

All times are assumed to be UTC.
The resultset can be also returned in a given time zone.

For ``REST`` queries:

.. code-block:: python
   :caption: Specifying the returned time zone for a REST API Request parameters

   params = {
       "query_type": "sql",
       "timezone" : "America/New_York",
       "response": "csv",
       "show_times_as_nanos" : "true",
       "all_times_are_readable" : "false",
       "compression":"gzip",
       "statement":sql_statement
       }
   
   access_token = get_access_token(access_token_url, client_id, client_secret)
   headers = {
       "Authorization": f"Bearer {access_token}",
       "Content-Type": "application/json"
   }
   response = requests.post(http_address, json=params, headers=headers)




For ``onetick.query_webapi`` the ``otq.run`` method should specify the time zone of the response.

.. code-block:: sql
   :caption: Specifying the returned time zone for onetick.query_webapi

   result = otq.run(sql_query,
       http_address=http_address,access_token=access_token,
       output_mode="pandas",
       timezone="America/New_York")


For ``onetick.py`` the ``otp.run`` method should specify the time zone of the response.

.. code-block:: python
   :caption: Specifying the returned time zone for onetick.py using otp.run

   result = otp.run(otp.SqlQuery(sql_statement),
			timezone='America/New_York')

Or the ``otp.config.tz`` attribute can be set:

.. code-block:: python
   :caption: Specifying the returned time zone for onetick.py using otp.config.tz

   otp.config.tz = 'America/New_York'
   result = otp.run(otp.SqlQuery(sql_statement))




Sample Databases & Tables
#########################

The full list of 200+ Global Equities, Futures, Options & Indices databases is available in 
`OneTick Cloud <https://authdash.cloud.onetick.com/web_dashboard/?dash=db_list>`_

The list of sample databases are additionally included below:

.. csv-table:: **OneTick Cloud Sample Databases**
   :header: "Database", "Description", "Available Tables"
   :widths: 15, 30, 15

   "CA_COMP_SAMPLE", "Consolidated Trades & Quotes Across All Canadian Venues", "QTE, NBBO, STAT, TRD"
   "CA_COMP_SAMPLE_BARS", "Consolidated Canadian Trade & Quote 1 Minute Bars", "QTE_1M, TRD_1M"
   "EU_COMP_SAMPLE", "Consolidated Trades & Quotes Across All European Venues", "QTE, STAT, TRD"
   "EU_COMP_SAMPLE_BARS", "Consolidated European Trade & Quote 1 Minute Bars", "QTE_1M, TRD_1M"
   "LSE_SAMPLE", "London Stock Exchange Trades, Quotes & Book Depth", "DAY, IND, MKT, PRL_FULL, QTE, STAT, TRD"
   "LSE_SAMPLE_BARS", "LSE Trade & Quote 1 Minute Bars", "QTE_1M, TRD_1M, DAY"
   "TDI_FUT_SAMPLE", "Global Futures  Trades & Quotes", "QTE, STAT, TRD"
   "TDI_FUT_SAMPLE_BARS", "Global Futures Trades & Quote 1 Minute Bars", "QTE_1M, TRD_1M"
   "US_COMP_SAMPLE", "Consolidated Trades & Quotes Across All US Venues", "QTE, STAT, TRD"

   "SYMBOL_UNIVERSE","Symbol Universe across all available Venues","STAT"
   "OQD_MKT_CAL","Market Holidays & Trading Hours","MKTCAL"



Data is stored in standardized tables

.. csv-table:: **OneTick Cloud Standard Tables**
   :header: "Table", "Description"
   :widths: 5, 30

   "DAY", "End of Day Record typically covering Closing Price & Open Interest for Derivatives Markets"
   "IND", "Indicative Prices occuring during Auction phases"
   "QTE", "Quote Events"
   "STAT", "Static Reference Data for the Instrument"
   "TRD", "Trade Events"
   "NBBO", "National Best Bid & Offer Quotes"
   "PRL", "Book Depth - Market By Level"
   "PRL_FULL", "Book Depth - Market by Order"
   "MKTCAL", "Market Holiday & Trading Hours"
   "TRD_1M", "1 Minute Trade Bar"
   "QTE_1M", "1 Minute Quote Bar"


Field Selection
###############

All of the fields in a table can be retrieved by using the ``*`` syntax.

.. code-block:: sql
   :caption: Retrieving All Fields using select *

   select *
   from US_COMP_SAMPLE.TRD                  
   where SYMBOL_NAME='CSCO'                    
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'  
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'   


A limited set of fields can be specified by replacing the ``*`` with the comma separated set of fields.

.. code-block:: sql
   :caption: Retrieving PRICE, SIZE and EXCHANGE fields

   select  PRICE, SIZE, EXCHANGE
   from US_COMP_SAMPLE.TRD                  
   where SYMBOL_NAME='CSCO'                    
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'  
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'





Limiting Returned Rows
######################

The set of returned rows can be limited using the ``LIMIT`` and ``OFFSET`` clauses. 
``LIMIT`` specifies how many rows to retrieve.

.. code-block:: sql
   :caption: Retrieving the First 10 records

   select  PRICE, SIZE, EXCHANGE
   from US_COMP_SAMPLE.TRD                  
   where SYMBOL_NAME='CSCO'                    
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'  
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   limit 10

While ``OFFSET`` specifis how many initial records should be skipped before returning results.

.. code-block:: sql
   :caption: Retrieving 10 records offset by 5

   select  PRICE, SIZE, EXCHANGE
   from US_COMP_SAMPLE.TRD                  
   where SYMBOL_NAME='CSCO'                    
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'  
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   limit 10 offset 5



Retrieving Multiple Symbols
###########################

Multiple symbols can be retrieved through specifying ``IN`` or ``LIKE``.

.. code-block:: sql
   :caption: Retrieval of Two Symbols using IN

   SELECT * FROM LSE_SAMPLE.TRD 
   WHERE SYMBOL_NAME in ('VOD','BARC')    
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10

.. code-block:: sql
   :caption: Wildcarded Symbol Selection using LIKE

   SELECT * FROM LSE_SAMPLE.TRD 
   WHERE SYMBOL_NAME LIKE 'BA%'
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


Adding Calculated Fields
########################

Additional fields can be added through calculation and assigned an alias.

.. code-block:: sql
   :caption: Adding Calculated Fields

   SELECT PRICE, SIZE, TRADE_CURRENCY, (PRICE*SIZE) as TRD_VALUE FROM LSE_SAMPLE.TRD 
   WHERE SYMBOL_NAME='VOD' 
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


Adding Filters
##############

Results can be filtered by adding additional filters to the ``WHERE`` clause.

.. code-block:: sql
   :caption: Adding Filters

   SELECT PRICE, SIZE, TRADE_CURRENCY, (PRICE*SIZE) as TRD_VALUE FROM LSE_SAMPLE.TRD 
   WHERE SYMBOL_NAME='VOD' 
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   and TRADE_CURRENCY = 'EUR'
   and SIZE > 100
   LIMIT 10


Aggregating Across Results
##########################

Results can be aggregated by specifying an aggregation for each field and defining an alias.

.. code-block:: sql
   :caption: Aggregating Across Results

   select  
   AVG(PRICE) as MEAN_PRICE,
   STDDEV(PRICE) as STDDEV_PRICE,
   MEDIAN(PRICE) as MEDIAN_PRICE,
   MAX(PRICE) as MAX_PRICE,
   MIN(PRICE) as MIN_PRICE,
   TW_AVG(PRICE) as TWA_PRICE,
   SUM(PRICE*SIZE)/SUM(SIZE) as VWAP_PRICE,
   COUNT(PRICE) as COUNT_PRICE
   from LSE_SAMPLE.TRD 
   where symbol_name = 'VOD' 
   and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
   and TIMESTAMP < '2024-01-04 16:00:00 UTC'


Additionally results can be grouped by adding the ``GROUP BY`` clause.

.. code-block:: sql
   :caption: Aggregating Across Results with Group By

   select  TRADE_CURRENCY,
   AVG(PRICE) as MEAN_PRICE,
   STDDEV(PRICE) as STDDEV_PRICE,
   MEDIAN(PRICE) as MEDIAN_PRICE,
   MAX(PRICE) as MAX_PRICE,
   MIN(PRICE) as MIN_PRICE,
   TW_AVG(PRICE) as TWA_PRICE,
   SUM(PRICE*SIZE)/SUM(SIZE) as VWAP_PRICE,
   COUNT(PRICE) as COUNT_PRICE
   from LSE_SAMPLE.TRD
   where symbol_name = 'VOD'
   and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
   and TIMESTAMP < '2024-01-04 16:00:00 UTC'
   group by TRADE_CURRENCY


Query Data Availability Status
##############################

The data availability status can be queried to determine when data loads have completed for a specific database.
This is useful for monitoring data freshness and verifying that recent data is available before running analysis queries.

Data availability is tracked through the ``DB_INFO.PROC_EVENTS`` table, which contains load completion events for each database.
The ``EVENT_NAME`` field indicates the type of event, with ``'Load finished'`` indicating successful data load completion.

.. code-block:: sql
   :caption: Retrieve Completed Data Load Events for the last 7 days

   SELECT *
   FROM DB_INFO.PROC_EVENTS                      -- Database info and process events table
   WHERE SYMBOL_NAME LIKE '%'                    -- Filter for all symbols using wildcard
     AND TIMESTAMP >= DATEADD('DAY', -7, NOW())  -- Include events from the last 7 days
     AND TIMESTAMP < NOW()                       -- Exclude future events, capture up to current time
     AND EVENT_NAME = 'Load finished'            -- Filter to only successful load completion events


Point in Time Trade Snapshot Across Venue
##########################################

Retrieves a snapshot of trade data at a specific point in time across a venue, looking back a specified number of seconds to find the prevailing trade before the selected time.

A specific point in time is selected with the 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 before the selected time, up to the lookback period.

.. code-block:: sql
   :caption: Point in Time Trade Snapshot Across Venue

    select SYMBOL_NAME, PRICE, SIZE, COND, EXCHANGE
    from US_COMP_SAMPLE.TRD
    where SYMBOL_NAME LIKE '%'          -- Retrieve All Symbols
    and TIMESTAMP = '2024-01-03 12:30:00 America/New_York'
    and init_lookback = 86400           --how many seconds to look back for prevailing value


Point in Time Trade and Quote Snapshot Across Venue
###################################################

Retrieves a combined snapshot of both trade and quote data at a specific point in time across a venue, using a lookback period to find the most recent data before the selected time.

A specific point in time is selected with the 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 quote before the selected time, up to the lookback period.

.. code-block:: sql
   :caption: Point in Time Trade and Quote Snapshot Across Venue

    select t.SYMBOL_NAME as SYMBOL_NAME,
    t.PRICE as PRICE, t.SIZE as SIZE,
    q.BID_PRICE as BID_PRICE, q.ASK_PRICE as ASK_PRICE
    from LSE_SAMPLE.TRD t, LSE_SAMPLE.QTE q
    where t.SYMBOL_NAME LIKE '%'          -- Retrieve All Symbols
    and t.SYMBOL_NAME = q.SYMBOL_NAME
    and TIMESTAMP = '2024-01-03 12:30:00 Europe/London'
    and t.init_lookback = 86400           --how many seconds to look back for prevailing Trade
    and q.init_lookback = 86400           --how many seconds to look back for prevailing Quote


Spread and Mid from Venue
#########################

Calculates bid-ask spread and mid-price from quote data at a venue, providing key pricing metrics for market analysis.

Venue datasets include Top of Book Quotes in the QTE table. SPREAD = ASK_PRICE - BID_PRICE and MID_PRICE = (BID_PRICE + ASK_PRICE)/2.

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

    select BID_PRICE, ASK_PRICE,
    (BID_PRICE + ASK_PRICE)/2 as MID_PRICE,
    (ASK_PRICE - BID_PRICE) as SPREAD
    from LSE_SAMPLE.QTE
    where SYMBOL_NAME = 'VOD'
    and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
    and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
    and BID_SIZE > 0 and ASK_SIZE > 0
    limit 1000


Spread and Mid Statistics from Venue
####################################

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

Venue datasets include Top of Book Quotes in the QTE table. 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 Venue

    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 LSE_SAMPLE.QTE
      where SYMBOL_NAME = 'VOD'
      and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
      and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
      and BID_SIZE > 0 and ASK_SIZE > 0
    )


Retrieving Valid Quotes for Specified Trading Sessions
#######################################################

Retrieves valid quotes for a specified market phase or trading session by joining quote data with market phase information. Quotes are filtered to those with valid sizes and then matched with the prevailing market phase. This allows analysis of quotes during specific trading periods such as continuous trading.

.. code-block:: sql
   :caption: Valid Quotes for LSE VOD during Continuous Trading Session


    select *
    from (
    select q.BID_PRICE as BID_PRICE, q.ASK_PRICE as ASK_PRICE,
    m.OMD_STATUS as OMD_STATUS
    from LSE_SAMPLE.QTE q, LSE_SAMPLE.MKT m
    where q.SYMBOL_NAME='VOD'
    and q.SYMBOL_NAME = m.SYMBOL_NAME
    and sametime_as_existing(q.TIMESTAMP, m.TIMESTAMP, 0) = TRUE
    and TIMESTAMP >= '2024-01-03 00:00:00 Europe/London'
    and TIMESTAMP < '2024-01-04 00:00:00 Europe/London'
    and q.BID_SIZE > 0 and q.ASK_SIZE > 0
    )
    where OMD_STATUS = 'T'