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
Limiting the volume of returned records with LIMIT

All three must be provided for results to be retrieved.

Simple Retrieval Example#

SELECT * 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

Sample Databases & Tables#

The full list of 200+ Global Equities, Futures, Options & Indices databases is available in OneTick Cloud

The list of sample databases are additionally included below:

**OneTick Cloud Sample Databases**#
Database	Description	Available Tables
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

**OneTick Cloud Standard Tables**#
Table	Description
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

Retrieving Multiple Symbols#

Multiple symbols can be retrieved through specifying IN or LIKE.

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

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

Retrieving Specific Fields#

Specific Fields can be retrieved by specifying the fields after the SELECT.

Retrieval of Specific Fields Example#

SELECT PRICE, SIZE, TRADE_CURRENCY 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 Calculated Fields#

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

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.

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.

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.

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