Skip to main content

FAQ

1) I have market data stored in a stream with a package header format. How can I select all trades and/or L1 quotes from this stream?

Answer

The package header format stores all data in an array of polymorphic objects called entries. Use array join construction to 'unfold' this array into separate entry messages, and filter them by type (TradeEntry, L1Entry, etc).

--Query example
SELECT entry.exchangeId as 'exchangeId', entry.price as 'price', entry.size as 'size' 
TYPE "deltix.timebase.api.messages.TradeMessage" 
FROM "COINBASE" 
ARRAY JOIN entries AS entry 
WHERE entry IS TradeEntry
2) When I select entry.side, I get an error.

Question

I get an error related to the entry.side field when I try selecting trades from the stream.

Error message: "Illegal type in: ENTRY.SIDE; Types should be equal".

Answer

The elements of the entries array can be of various types: TradeEntry, L1Entry, L2EntryNew, and L2EntryUpdate. There may potentially be an ambiguity with the side field because it can have differing types, for example, AggressorSide for TradeEntry.side and QuoteSide for others.

To resolve this, you can cast the entries array to a fixed TradeEntry array type using the syntax array(TradeEntry):

SELECT 
    entry.exchangeId, entry.price, entry.size, entry.side 
TYPE "deltix.timebase.api.messages.TradeMessage"
FROM "binance" 
ARRAY JOIN (entries AS array(TradeEntry)) AS entry
WHERE entry != null
3) I have market data stored in a stream with a Package Header format. How can I select all trades as a TradeMessage and L1 entries as a BestBidOfferMessage?

Answer

The Package Header format stores all data in an array of polymorphic objects called entries. Construct two queries that select TradeEntry and BestBidOfferMessage separately as we did in the first question. For that, use the array join construction to 'unfold' the entries array into separate entry messages and filter them by type (TradeEntry and L1Entry). Then, use the keyword TYPE to name the output message types as standard TimeBase API Messages TradeMessage and BestBidOfferMessage. And finally, use UNION to merge the queries into a polymorphic output:

SELECT 
  entry.exchangeId AS 'exchangeId', 
  entry.price AS 'price', 
  entry.size AS 'size' 
TYPE "deltix.timebase.api.messages.TradeMessage" 
FROM "BITFINEX" 
ARRAY JOIN entries AS entry 
WHERE entry IS TradeEntry
UNION 
SELECT 
  (entry.exchangeId if entry.side == BID) AS 'bidExchangeId', 
  (entry.price if entry.side == BID) AS 'bidPrice', 
  (entry.size if entry.side == BID) AS 'bidSize',
  (entry.exchangeId if entry.side == ASK) AS 'offerExchangeId', 
  (entry.price if entry.side == ASK) AS 'offerPrice', 
  (entry.size if entry.side == ASK) AS 'offerSize' 
TYPE "deltix.timebase.api.messages.BestBidOfferMessage" 
FROM "BITFINEX" 
ARRAY JOIN (entries AS array(L1Entry)) AS entry 
WHERE entry != null

As an alternative, use the RECORD keyword instead of UNION:

WITH
  entry as TradeEntry as trade,
  entry as L1Entry as l1
SELECT 
  RECORD 
    trade.exchangeId FIELD 'exchangeId', 
    trade.price FIELD 'price', 
    trade.size FIELD 'size'
  TYPE "deltix.timebase.api.messages.TradeMessage" WHEN entry IS TradeEntry
  RECORD
    (l1.exchangeId if l1.side == BID) FIELD 'bidExchangeId',
    (l1.price if l1.side == BID) FIELD 'bidPrice',
    (l1.size if l1.side == BID) FIELD 'bidSize',
    (l1.exchangeId if l1.side == ASK) FIELD 'offerExchangeId',
    (l1.price if l1.side == ASK) FIELD 'offerPrice',
    (l1.size if l1.side == ASK) FIELD 'offerSize'
  TYPE "deltix.timebase.api.messages.BestBidOfferMessage" WHEN entry is L1Entry
FROM "BITFINEX" 
ARRAY JOIN entries AS entry
4) How can I fill gaps with the last available value?

Question

I am trying to calculate a spread for L1 market data. The problem is that I cannot do it by taking the difference between bids and asks because quotes in my data have different timestamps and there are also missing price values here and there. I want to resolve this by filling the missing price values with the last available data. How do I do that?

SELECT
  entry[side == ASK].price AS askPrice, 
  entry[side == BID].price AS bidPrice
FROM "BINANCE"
ARRAY JOIN (entries AS array(L1Entry)) AS entry
WHERE entry != null AND symbol == 'BTC/USDT'

Answer

Use the lastNotNull{}(field) function with the SELECT RUNNING keyword construction. You need a RUNNING keyword to avoid calculating results for the entire stream.

SELECT RUNNING
  lastNotNull{}(entry[side == ASK].price) AS askPrice, 
  lastNotNull{}(entry[side == BID].price) AS bidPrice,
  askPrice - bidPrice AS spread
FROM "BINANCE"
ARRAY JOIN (entries AS array(L1Entry)) AS entry
WHERE entry != null AND symbol == 'BTC/USDT'
5) I want to build an order book snapshot for each message in the stream.

Answer

Use the orderBook{}() function. This function builds an order book by combining the snapshot with incremental updates of data stored in the stream. It then returns the current state of the order book as a snapshot in the package header format.

WITH
orderbook{maxDepth: 20}(this.packageType, this.entries) AS book
SELECT RUNNING 
  book AS entries, PERIODICAL_SNAPSHOT AS packageType 
TYPE "deltix.timebase.api.messages.universal.PackageHeader"
FROM "BINANCE"
WHERE symbol == 'BTC/USDT' and size(book) > 0
6) How can I flatten order book entries?

Question

I have an order book built from a package header stream. How can I take the entries array and represent its elements as individual messages?

For example, I want to take the top 2 levels of an order book and flatten them, so each entries array element is projected into a separate message. I would expect the result to look like this:

symbol, timestamp, exchangeId, price, size, level, side
BTCTUSD, 2023-04-25T13:07:30.758Z, BINANCE, 27441.29, 0.9988, 0, ASK
BTCTUSD, 2023-04-25T13:07:30.758Z, BINANCE, 27441.58, 0.12762, 1, ASK
BTCTUSD, 2023-04-25T13:07:30.758Z, BINANCE, 27441.28, 0.00026, 0, BID
BTCTUSD, 2023-04-25T13:07:30.758Z, BINANCE, 27441.21, 0.0004, 1, BID

Answer

Use this query:

WITH
    orderbook{maxDepth: 2}(this.packageType, this.entries[not this is TradeEntry]) AS book
SELECT RUNNING
    book_entry.exchangeId AS 'exchangeId',
    book_entry.price AS 'price',
    book_entry.size AS 'size',
    book_entry.level AS 'level',
    book_entry.side AS 'side'
FROM "BINANCE" 
ARRAY JOIN (book AS array(L2EntryNew)) AS book_entry
WHERE symbol == 'BTCTUSD'
7) What query can I use to count messages by day?

Answer

Use the over time construction to aggregate values:

SELECT count{}() FROM "BINANCE" 
OVER TIME(1d)
8) How can I calculate a message rate over 1 second intervals or 1 minute intervals for the universal market data format?

Answer

To calculate rates in a single query, use the UNION construction:

SELECT count{}() AS 'seconds_rate' FROM "COINBASE" 
OVER TIME(1s)
UNION
SELECT count{}() AS 'minutes_rate' FROM "COINBASE" 
OVER TIME(1m)

Refer to UNION to learn more.

9) How can I calculate the maximum size of the entries array in PackageHeader snapshots?

Answer

Use the MAX{}() and SIZE() funtions:

SELECT max{}(size(entries)) FROM "COINBASE" 
where packageType == PERIODICAL_SNAPSHOT
10) In a TimeBase stream containing bars for the two symbols BTCUSDT and ETHUSDT, is it possible to select the prices of BTCUSDT and ETHUSDT in a single row and calculate the difference between them?

Answer

Use the if operator to filter the price for the specified symbol. Then, utilize the lastNotNull{}() function to fill any gaps in the results.

WITH
  lastNotNull{}(closeAsk if symbol == 'BTCUSDT') AS 'price1',
  lastNotNull{}(closeAsk if symbol == 'ETHUSDT') AS 'price2'
SELECT RUNNING 
  price1, price2, price1 - price2 AS diff 
FROM Bars
WHERE symbol IN ('BTCUSDT', 'ETHUSDT') and price1 != null and price2 != null
11) How can I create a query to retrieve prices for the same symbol that's available on two exchanges? For example, I want to find the price difference of BTC/USDT between BINANCE and BITFINEX (spread).

Answer

To achieve this, construct two order books for each exchange utilizing the OrderBook{}() function. Use the UNION keyword to select from both streams. First, filter entries from the stream for each exchange using a predicate like ((THIS.entries[exchangeId == 'BINANCE']) as 'BinanceEntries'). Then, pass the filtered data into the OrderBook{}() function and fill any gaps using lastNotNull{}(). Finally, calculate the spread from the top of the order books.

with
  (THIS.entries[exchangeId == 'BINANCE']) as 'BinanceEntries',
  (THIS.entries[exchangeId == 'BITFINEX']) as 'BitfinexEntries',
  lastNotNull{}(ORDERBOOK{}(THIS.packageType, BinanceEntries)) AS array(L2EntryNew) AS 'BinanceBook',
  lastNotNull{}(ORDERBOOK{}(THIS.packageType, BitfinexEntries)) AS array(L2EntryNew) AS 'BitfinexBook',
  (BinanceBook[level==0 and side == BID].price[0] + BinanceBook[level==0 and side == ASK].price[0]) / 2 AS 'BinancePrice',
  (BitfinexBook[level==0 and side == BID].price[0] + BitfinexBook[level==0 and side == ASK].price[0]) / 2 AS 'BitfinexPrice',
  BinancePrice - BitfinexPrice AS 'Spread'
SELECT 
  BinancePrice, BitfinexPrice, Spread
FROM ("BITFINEX" UNION "BINANCE")
over time(100ms)
WHERE symbol == 'BTC/USDT' and BinanceBook != null and BitfinexBook != null
12) How can I retrieve the latest best prices for symbols present in two separate streams?

Answer

WITH
  (THIS.entries if symbol == 'BTCUSDT') as 'CashEntries',
  (THIS.entries if symbol == 'BTCPC-T') as 'FutureEntries',
  lastNotNull{}(ORDERBOOK{}(THIS.packageType, CashEntries)) as array(L2EntryNew) as 'CashBook',
  lastNotNull{}(ORDERBOOK{}(THIS.packageType, FutureEntries)) as array(L2EntryNew) as 'FutureBook',
  CashBook[level==0 and side == BID].price[0] as 'CASH',
  FutureBook[level==0 and side == BID].price[0] as 'FUTURE'
SELECT 
  FUTURE, CASH
FROM ("BINANCE" UNION "BINANCEFUT")
over time(100ms)
WHERE symbol IN ('BTCUSDT', 'BTCPC-T') and CashBook != null and FutureBook != null
13) Is there a windowed standard deviation function available in QQL, equivalent to the Pandas operation df['price'].rolling('60s').std()?

Yes, use the statWindow function with the initial timePeriod parameter:

SELECT RUNNING statWindow{timePeriod:60s}(volume).standardDeviation
FROM "1sec.bars"
WHERE symbol == 'AAPL'
14) How can I calculate the difference between the current and previous volume values in my bars stream?

Use the window function with a fixed period of size 2:

WITH
  window{period:2}(volume) as w
SELECT RUNNING w[1] - w[0]
FROM "bars"
WHERE symbol == 'AAPL'
15) How can I select all TimeBase streams and their types?

To retrieve a list of all streams along with their associated metadata, utilize the streams() function. Afterwards, you can iterate through the streams using the ARRAY JOIN clause to extract the specific information you require.

SELECT s.key AS key, s.topTypes[not isAbstract].name AS types
ARRAY JOIN streams() AS s
16) How do I fetch symbols from a securities stream?

To retrieve symbols from a securities stream, use the symbols() function in the following manner:

SELECT s
ARRAY JOIN symbols('securities') AS s
17) How can I select all available functions supported by QQL?

To obtain a list of all available functions in QQL, you can make use of the stateless_functions() and stateful_functions() functions, for example:

SELECT f.id, f.arguments.name, f.arguments.dataType.baseName
ARRAY JOIN stateless_functions() AS f
18) I need to map my result set to a standard BarMessage (from TimeBase API Messages).

To set the type name of the query result, use the keyword TYPE.

SELECT open, close, high, low, volume TYPE "deltix.timebase.api.messages.BarMessage"
FROM MyStream
19) How do I change the precision of the receiveTimestamp and originalTimestamp fields in my stream to nanosecond?

Use the ALTER STREAM query:

ALTER STREAM BINANCE
  ALTER CLASS "deltix.timebase.api.messages.MarketMessage" (
    ALTER FIELD "receiveTimestamp" SET encoding NANOSECOND;
    ALTER FIELD "originalTimestamp" SET encoding NANOSECOND
  )
CONFIRM CONVERT_DATA
20) How can I count the number of distinct symbols in a securities stream?

Utilize COLLECT_UNIQUE function:

SELECT SIZE(COLLECT_UNIQUE{}(symbol)) FROM "securities"

Since 5.6.78+ you can use Inner select:

SELECT COUNT{}() FROM (SELECT DISTINCT symbol FROM "securities")
21) I have an "ember-messages" stream. How to calculate sum of trade quantities by each instrument for each day (for partially and completely filled orders), given that the trading day starts at 17:00 UTC.

Since 5.6.78+ QQL supports over time with offset:

SELECT
  SUM{}(tradeQuantity * -1 if orderEvent:Side == SELL else tradeQuantity) 
FROM "ember-messages"
OVER Time(1d, 17h)
WHERE orderStatus IN (COMPLETELY_FILLED, PARTIALLY_FILLED)
GROUP BY symbol