Book Depth 
==========
A series of simple examples are provided showing how to retrieve book depth from `OneTick Cloud <https://www.onetick.com/cloud-services>`_.

Book Depth Events
#################

Book depth events are retrieved by specifying either the ``PRL`` or ``PRL_FULL`` table, depending on whether Market by Level (MBL) or Market by Order (MBO) data is available.

* ``PRL`` Table - Market by Level (MBL)
* ``PRL_FULL`` Table - Market by Order (MBO)


Typically book depth data is analysed using orderbook processing syntax, while the order update message can be retrieved with a simple select from the database and table.

.. code-block:: sql
   :caption: Book Depth Event Retrieval from the LSE_SAMPLE Database

   SELECT * FROM LSE_SAMPLE.PRL_FULL 
   WHERE SYMBOL_NAME='VOD' 
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


.. csv-table:: Book Depth Event Retrieval Results
   :header: Symbol,Timestamp,PRICE,SIZE,SYMBOL_NAME,TICK_TYPE,OMDSEQ,DELETED_TIME,TICK_STATUS,BUY_SELL_FLAG,UPDATE_TYPE,ORDER_ID,PART_ID,ORDER_TYPE,RECORD_TYPE
   
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,0,0,LSE_SAMPLE::VOD,PRL_FULL,0,,0,0,,,,,Z
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,69,600,LSE_SAMPLE::VOD,PRL_FULL,0,,0,0,A,233244094926020495,,L,R
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,69,"5,800",LSE_SAMPLE::VOD,PRL_FULL,1,,0,0,A,233244094926209592,,L,R
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,67.2,"50,000",LSE_SAMPLE::VOD,PRL_FULL,2,,0,0,A,232452446553925487,,L,R
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,67,"10,000",LSE_SAMPLE::VOD,PRL_FULL,3,,0,0,A,233244094925636785,,L,R
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,66.94,"7,300",LSE_SAMPLE::VOD,PRL_FULL,4,,0,0,A,233244094925636407,,L,R
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,66.66,"20,000",LSE_SAMPLE::VOD,PRL_FULL,5,,0,0,A,233244094925845524,,L,R
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,66.34,"7,360",LSE_SAMPLE::VOD,PRL_FULL,6,,0,0,A,233244094925636400,,L,R
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,66,"3,000",LSE_SAMPLE::VOD,PRL_FULL,7,,0,0,A,233244094925636417,,L,R
   LSE_SAMPLE::VOD,2024-01-03 05:00:07.876,65,"70,000",LSE_SAMPLE::VOD,PRL_FULL,8,,0,0,A,233244094925636406,,L,R



Order Book Price Level Updates
##############################

Updates to order books are retrieved by using the ``OB_SNAPSHOT`` syntax, specifying ``IS_RUNNING_AGGR='TRUE'`` and ``SHOW_ONLY_CHANGES='TRUE'``, 
along with defining the table which is either  ``PRL`` or ``PRL_FULL`` , depending on whether Market by Level (MBL) or Market by Order (MBO) data is available. 
In this case the ``SYMBOL_NAME`` should specify both the database and symbol seperated by ``::``.   e.g. ``LSE_SAMPLE::HSBA``
An initial state of the book is returned, following by deltas if ``SHOW_ONLY_CHANGES='TRUE'``.  Alternatively each new book can be output if ``SHOW_ONLY_CHANGES='FALSE'``.

When using the ``OB_SNAPSHOT`` syntax, data is returned with each row representing a price level identified with the ``LEVEL`` field, and side identified with the ``BUY_SELL_FLAG`` field.  
``PRICE`` and ``SIZE`` fields present the current price and remaining size or quantity of the price level.

Alternative representations are available using:

 * ``OB_SNAPSHOT`` for each record representing either an order, or a price level and side.
 * ``OB_SNAPSHOT_WIDE`` for each record representing a price level with both Bid and Ask.
 * ``OB_SNAPSHOT_FLAT`` for each record represnting every level in the book


The returned results can be further filtered by additionally specifying:

 * Maximum Number of Price Levels to return through ``MAX_LEVELS``
 * Maximum Depth in Cumulative Size to return through ``MAX_DEPTH_SHARES``
 * Maximum Depth in % Price Skew from BBO to return through ``MAX_DEPTH_FOR_PRICE``
 * Maximum Depth in Absolute Spread through ``MAX_SPREAD``


.. code-block:: sql
   :caption: Order Book Depth Updates Retrieval

   SELECT * FROM OTQ_CHAIN."OB_SNAPSHOT(IS_RUNNING_AGGR='TRUE',SHOW_ONLY_CHANGES='TRUE');PRL_FULL"
   WHERE SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


.. csv-table:: Order Book Depth Updates Retrieval Results
   :header: Symbol,Timestamp,PRICE,SIZE,SYMBOL_NAME,TICK_TYPE,UPDATE_TIME,LEVEL,BUY_SELL_FLAG

   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,640,"7,035",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 16:47:11.727,1,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,642,"2,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 16:35:12.759,2,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,650,"38,600",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 17:10:02.567,3,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,655,"16,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 17:30:00.059,4,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,659.8,800,LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 07:50:03.005,5,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,660,"16,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 17:30:00.059,6,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,665,"32,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 16:50:00.058,7,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,669.3,"12,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 07:50:03.004,8,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,680,"15,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 16:40:15.570,9,1
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,682,"4,800",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 07:50:03.004,10,1



Order Book L2 Depth At Time
###########################

For Level 2 or Level 3 Book depth stored in ``PRL`` or ``PRL_FULL`` tables, the state of the book can be retrieved at a point in time by specifying:

* ``SYMBOL_NAME`` with ``[Database]::[Symbol]`` Syntax.  e.g. ``LSE_SAMPLE::HSBA``
* ``OB_SNAPSHOT()`` or ``OB_SNAPSHOT_WIDE()`` or ``OB_SNAPSHOT_FLAT`` depending on the required ouptut format
* ``TIMESTAMP`` field set to equals ``=`` the required point in time.

.. code-block:: sql
   :caption: Order Book Retrieval by Level (MBL) from L3 Data at the Selected Point in Time

   select * from OTQ_CHAIN."OB_SNAPSHOT();PRL_FULL"
   where SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP = '2024-01-03 12:00:00 UTC'
   limit 1000



Order Book L3 Depth At Time
###########################

For Level 3 Book depth stored in ``PRL_FULL`` tables, the details state of the book can be retrieved by book updates can be retrieved at an order by order basis by specifying:

* ``OB_SNAPSHOT()`` or ``OB_SNAPSHOT_WIDE()`` or ``OB_SNAPSHOT_FLAT`` depending on the required ouptut format
* ``SHOW_FULL_DETAIL='TRUE'``  inside the OB syntax.
* ``SYMBOL_NAME`` with ``[Database]::[Symbol]`` Syntax.  e.g. ``LSE_SAMPLE::HSBA``
* ``TIMESTAMP`` field set to equals ``=`` the required point in time.

In this case the ORDER_ID field will also be output in the results.

.. code-block:: sql
   :caption: Retrieving the Book to MBO (Market by Order) at a specified time

   select * from OTQ_CHAIN."OB_SNAPSHOT(SHOW_FULL_DETAIL='TRUE');PRL_FULL"
   where SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP = '2024-01-03 12:00:00 UTC'
   limit 1000



Order Book L3 Depth Updates
###########################

For Level 3 Book depth stored in ``PRL_FULL`` tables, book updates can be retrieved at an order by order basis by specifying ``SHOW_FULL_DETAIL='TRUE'``.
In this case the ORDER_ID field will also be output in the results.


.. code-block:: sql
   :caption: Order Book Full Depth Updates Retrieval

   SELECT * FROM OTQ_CHAIN."OB_SNAPSHOT(IS_RUNNING_AGGR='TRUE',SHOW_ONLY_CHANGES='TRUE',SHOW_FULL_DETAIL='TRUE');PRL_FULL"
   WHERE SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


.. csv-table:: Order Book Full Depth Updates Retrieval Results
   :header: Symbol,Timestamp,PRICE,SIZE,SYMBOL_NAME,TICK_TYPE,OMDSEQ,DELETED_TIME,TICK_STATUS,UPDATE_TIME,LEVEL,BUY_SELL_FLAG,UPDATE_TYPE,ORDER_ID,PART_ID,ORDER_TYPE,RECORD_TYPE

   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,640,"7,035",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 05:00:09.192,16,2024-01-02 05:00:09.192,1,1,A,225960944935921699,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,642,"2,000",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 07:50:03.010,16,2024-01-02 07:50:03.010,2,1,A,233244109958022143,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,650,"2,000",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 05:00:09.192,16,2024-01-02 05:00:09.192,3,1,A,232452461586102765,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,650,"36,600",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 07:50:03.006,16,2024-01-02 07:50:03.006,3,1,A,233244109958022117,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,655,"16,000",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 07:50:03.004,16,2024-01-02 07:50:03.004,4,1,A,233244109958022100,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,659.8,800,LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 07:50:03.005,16,2024-01-02 07:50:03.005,5,1,A,233244109958022109,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,660,"16,000",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 07:50:03.005,16,2024-01-02 07:50:03.005,6,1,A,233244109958022111,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,665,"16,000",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 05:00:09.192,16,2024-01-02 05:00:09.192,7,1,A,228177560377546089,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,665,"16,000",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 07:50:03.007,16,2024-01-02 07:50:03.007,7,1,A,233244109958022126,,L,R
   LSE_SAMPLE::HSBA,2024-01-03 00:00:00,669.3,"12,000",LSE_SAMPLE::HSBA,PRL_FULL,21,2024-01-02 07:50:03.004,16,2024-01-02 07:50:03.004,8,1,A,233244109958022099,,L,R



Order Book Depth Bars
#####################

Order Book state can be returned at periodic intervals generating book depth bars, by specifying the ``BUCKET_INTERVAL`` parameter, whcih represents a time in seconds.  
Milliseconds are supported by specifying a decimal (e.g. 0.1)

As before, alternative representations are available using:

 * ``OB_SNAPSHOT`` for each record representing either an order, or a price level and side.
 * ``OB_SNAPSHOT_WIDE`` for each record representing a price level with both Bid and Ask.
 * ``OB_SNAPSHOT_FLAT`` for each record represnting every level in the book


.. code-block:: sql
   :caption: Order Book 1 Minute Bars to 5 Levels of Depth Retrieval

   SELECT * FROM OTQ_CHAIN."OB_SNAPSHOT(BUCKET_INTERVAL='60',MAX_LEVELS='5');PRL_FULL"
   WHERE SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


.. csv-table:: Order Book 1 Minute Bars to 5 Levels of Depth Retrieval Results
   :header: Symbol,Timestamp,PRICE,SIZE,SYMBOL_NAME,TICK_TYPE,UPDATE_TIME,LEVEL,BUY_SELL_FLAG

   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,640,"7,035",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 16:47:11.727,1,1
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,642,"2,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 16:35:12.759,2,1
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,650,"38,600",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 17:10:02.567,3,1
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,655,"16,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 17:30:00.059,4,1
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,659.8,800,LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 07:50:03.005,5,1
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,610,80,LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 16:40:00.145,1,0
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,600,500,LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 17:30:00.058,2,0
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,580,50,LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 17:30:00.059,3,0
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,575,"1,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 07:50:03.003,4,0
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,500,"2,000",LSE_SAMPLE::HSBA,PRL_FULL,2024-01-02 16:40:00.145,5,0



.. code-block:: sql
   :caption: Order Book Wide (Bid & Ask on same row) 1 Minute Bars to 5 Levels of Depth Retrieval

   SELECT * FROM OTQ_CHAIN."OB_SNAPSHOT_WIDE(BUCKET_INTERVAL='60',MAX_LEVELS='5');PRL_FULL"
   WHERE SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


.. csv-table:: Order Book Wide (Bid & Ask on same row) 1 Minute Bars to 5 Levels of Depth Retrieval Results
   :header: Symbol,Timestamp,BID_PRICE,ASK_PRICE,BID_SIZE,ASK_SIZE,SYMBOL_NAME,TICK_TYPE,LEVEL,BID_UPDATE_TIME,ASK_UPDATE_TIME

   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,610,640,80,"7,035",LSE_SAMPLE::HSBA,PRL_FULL,1,2024-01-02 16:40:00.145,2024-01-02 16:47:11.727
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,600,642,500,"2,000",LSE_SAMPLE::HSBA,PRL_FULL,2,2024-01-02 17:30:00.058,2024-01-02 16:35:12.759
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,580,650,50,"38,600",LSE_SAMPLE::HSBA,PRL_FULL,3,2024-01-02 17:30:00.059,2024-01-02 17:10:02.567
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,575,655,"1,000","16,000",LSE_SAMPLE::HSBA,PRL_FULL,4,2024-01-02 07:50:03.003,2024-01-02 17:30:00.059
   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,500,659.8,"2,000",800,LSE_SAMPLE::HSBA,PRL_FULL,5,2024-01-02 16:40:00.145,2024-01-02 07:50:03.005
   LSE_SAMPLE::HSBA,2024-01-03 00:02:00,610,640,80,"7,035",LSE_SAMPLE::HSBA,PRL_FULL,1,2024-01-02 16:40:00.145,2024-01-02 16:47:11.727
   LSE_SAMPLE::HSBA,2024-01-03 00:02:00,600,642,500,"2,000",LSE_SAMPLE::HSBA,PRL_FULL,2,2024-01-02 17:30:00.058,2024-01-02 16:35:12.759
   LSE_SAMPLE::HSBA,2024-01-03 00:02:00,580,650,50,"38,600",LSE_SAMPLE::HSBA,PRL_FULL,3,2024-01-02 17:30:00.059,2024-01-02 17:10:02.567
   LSE_SAMPLE::HSBA,2024-01-03 00:02:00,575,655,"1,000","16,000",LSE_SAMPLE::HSBA,PRL_FULL,4,2024-01-02 07:50:03.003,2024-01-02 17:30:00.059
   LSE_SAMPLE::HSBA,2024-01-03 00:02:00,500,659.8,"2,000",800,LSE_SAMPLE::HSBA,PRL_FULL,5,2024-01-02 16:40:00.145,2024-01-02 07:50:03.005


.. code-block:: sql
   :caption: Order Book Flat (All Levels on same row) 1 Minute Bars to 5 Levels of Depth Retrieval

   SELECT * FROM OTQ_CHAIN."OB_SNAPSHOT_FLAT(BUCKET_INTERVAL='60',MAX_LEVELS='5');PRL_FULL"
   WHERE SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


.. csv-table:: Order Book Flat (All Levels on same row) 1 Minute Bars to 5 Levels of Depth Retrieval Results
   :header: Symbol,Timestamp,SYMBOL_NAME,TICK_TYPE,BID_PRICE1,BID_UPDATE_TIME1,BID_SIZE1,ASK_PRICE1,ASK_UPDATE_TIME1,ASK_SIZE1,BID_PRICE2,BID_UPDATE_TIME2,BID_SIZE2,ASK_PRICE2,ASK_UPDATE_TIME2,ASK_SIZE2,BID_PRICE3,BID_UPDATE_TIME3,BID_SIZE3,ASK_PRICE3,ASK_UPDATE_TIME3,ASK_SIZE3,BID_PRICE4,BID_UPDATE_TIME4,BID_SIZE4,ASK_PRICE4,ASK_UPDATE_TIME4,ASK_SIZE4,BID_PRICE5,BID_UPDATE_TIME5,BID_SIZE5,ASK_PRICE5,ASK_UPDATE_TIME5,ASK_SIZE5

   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:02:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:03:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:04:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:05:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:06:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:07:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:08:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:09:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800
   LSE_SAMPLE::HSBA,2024-01-03 00:10:00,LSE_SAMPLE::HSBA,PRL_FULL,610,2024-01-02 16:40:00.145,80,640,2024-01-02 16:47:11.727,"7,035",600,2024-01-02 17:30:00.058,500,642,2024-01-02 16:35:12.759,"2,000",580,2024-01-02 17:30:00.059,50,650,2024-01-02 17:10:02.567,"38,600",575,2024-01-02 07:50:03.003,"1,000",655,2024-01-02 17:30:00.059,"16,000",500,2024-01-02 16:40:00.145,"2,000",659.8,2024-01-02 07:50:03.005,800



Book Depth Statistics
#####################

Statistics on book depth can be retrieved by using the ``OB_SUMMARY`` syntax, and specifying a book filter

 * Maximum Number of Price Levels through ``MAX_LEVELS``
 * Maximum Depth in Cumulative Size  through ``MAX_DEPTH_SHARES``
 * Maximum Depth in % Price Skew from BBO through ``MAX_DEPTH_FOR_PRICE``
 * Maximum Depth in Absolute Spread through ``MAX_SPREAD``


Book depth statistics return:

 * ``BEST_BID_PRICE`` and ``BEST_ASK_PRICE``
 * Cumulative ``BID_SIZE`` and ``ASK_SIZE`` 
 * ``NUM_ASK_LEVELS`` and ``NUM_BID_LEVELS``
 * ``WORST_BID_PRICE`` and ``WORST_ASK_PRICE``
 * ``BID_VWAP`` and ``ASK_VWAP``


.. code-block:: sql
   :caption: Book Depth Statistics by minute to trade a quantity of 1000 Retrieval

   SELECT * FROM OTQ_CHAIN."OB_SUMMARY(BUCKET_INTERVAL='60',MAX_DEPTH_SHARES='1000');PRL_FULL"
   WHERE SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10


.. csv-table:: Book Depth Statistics by minute to trade a quantity of 1000 Retrieval Results
   :header: Symbol,Timestamp,BID_SIZE,ASK_SIZE,SYMBOL_NAME,TICK_TYPE,ASK_VWAP,BID_VWAP,BEST_ASK_PRICE,BEST_BID_PRICE,WORST_ASK_PRICE,WORST_BID_PRICE,NUM_ASK_LEVELS,NUM_BID_LEVELS

   LSE_SAMPLE::HSBA,2024-01-03 00:01:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:02:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:03:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:04:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:05:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:06:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:07:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:08:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:09:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4
   LSE_SAMPLE::HSBA,2024-01-03 00:10:00,"1,000","1,000",LSE_SAMPLE::HSBA,PRL_FULL,640,590.55,640,610,640,575,1,4



Book Depth at Time Limited to Specific Levels
#############################################

A book can be retrieved filtered to a specified number of levels by specifying: ``MAX_LEVELS``
If this not specified, all levels of the book are returned (assuming no other filters).

.. code-block:: sql
   :caption: Retrieving the Book at a specified time filtered upto 5 Levels

   select * from OTQ_CHAIN."OB_SNAPSHOT(MAX_LEVELS='5');PRL_FULL"
   where SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP = '2024-01-03 12:00:00 UTC'



Book Depth at Time Limited to a % Skew from Best
################################################

A book can be retrieved filtered to a specified % Skew from Best by specifying: ``MAX_DEPTH_FOR_PRICE``
If this not specified, all levels of the book are returned (assuming no other filters).
Note:  0.005 = 0.5%

.. code-block:: sql
   :caption: Retrieving the Book at a specified time filtered to a % Skew from Best

   select * from OTQ_CHAIN."OB_SNAPSHOT(MAX_DEPTH_FOR_PRICE='0.005');PRL_FULL"
   where SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP = '2024-01-03 12:00:00 UTC'
   limit 1000



Book Depth at Time Limited to an Accumulated Size
#################################################

A book can be retrieved filtered to a specified Accumulated Size by specifying: ``MAX_DEPTH_SHARES``
If this not specified, all levels of the book are returned (assuming no other filters).

.. code-block:: sql
   :caption: Retrieving the Book at a specified time filtered to a Accumulated Size of 10000 Shares

   select * from OTQ_CHAIN."OB_SNAPSHOT(MAX_DEPTH_SHARES='10000');PRL_FULL"
   where SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP = '2024-01-03 12:00:00 UTC'
   limit 1000


Book Depth at Time Limited to a defined Spread
##############################################

A book can be retrieved filtered to a specified Absolute Spread by specifying: ``MAX_SPREAD``
If this not specified, all levels of the book are returned (assuming no other filters).

.. code-block:: sql
   :caption: Retrieving the Book at a specified time filtered to an Absolute Spread of 1.0.

   select * from OTQ_CHAIN."OB_SNAPSHOT(SHOW_FULL_DETAIL='TRUE',MAX_SPREAD='1');PRL_FULL"
   where SYMBOL_NAME = 'LSE_SAMPLE::HSBA'
   and TIMESTAMP = '2024-01-03 12:00:00 UTC'
   limit 1000