Finance functions

This page contains functions useful for finance use-cases.


l2price(target_quantity, quantity_0, price_0, quantity_1, price_1, quantity_n, price_n, ...)

Calculates the level two order book price, based on an order target quantity, and a variable number of available orders.

The function aggregates orders until the target size is reached, then returns the average price per unit for the trade to be fulfilled.

It also:

  • takes a variable number of quantity/price pairs, each representing a specific order

  • expects all values to be convertible to doubles


  • target_quantity: The target_size to be fulfilled by the trades.
  • quantity*: The size of the trade.
  • price*: The price of the trade.

Return value

Return value type is double, representing the average price per unit to fulfill the trade.

Returns null if the price not calculable. For example, if the target quantity can't be fulfilled, or there is incomplete data in the set (nulls).


SELECT l2price(35, 8, 5.2, 23, 9.3, 42, 22.1);