Must-haves for array querying

CREATE TABLE fx_order_book (
    ts TIMESTAMP,
    symbol SYMBOL,
    bids DOUBLE[][],  -- bids[1]: prices, bids[2]: volumes
    asks DOUBLE[][]
);

Basic order book analytics

What is the bid-ask spread at any moment?

SELECT ts, symbol, asks[1][1] - bids[1][1] FROM fx_order_book

How much volume is available within 1% of the best price?

SELECT array_sum(
    asks[2, 1:insertion_point(asks[1], 1.01 * asks[1, 1])]
) volume FROM fx_order_book

Liquidity-driven execution

How much of a large order can be executed without moving the price more than a set amount?

Find the order book level at which the price passes a threshold, and then sum the sizes up to that level.

SELECT array_sum(asks[2, 1:insertion_point(asks[1], asks[1,1] + 0.1)]) volume 
FROM fx_order_book

What price level will a buy order for the given volume reach?

WITH 
    q1 AS (SELECT asks, array_cum_sum(asks[2]) cum_volumes FROM fx_order_book), 
    q2 AS (SELECT asks, cum_volumes, insertion_point(cum_volumes, 30.0, true) target_level FROM q1) 
SELECT cum_volumes, target_level, asks[1, target_level] price 
FROM q2

Order book imbalance

Imbalance at the top level

What is the ratio of bid volume to ask volume at the top level of the order book?

This indicates pressure in one direction (e.g. buyers heavily outweighing sellers at the top of the book).

SELECT
  bids[2, 1] / asks[2, 1]
FROM fx_order_book

Cumulative imbalance (Top 3 Levels)

WITH q1 AS (
    SELECT
        array_sum(asks[2, 1:4]) ask_vol, 
        array_sum(bids[2, 1:4]) bid_vol
    FROM fx_order_book
)
SELECT ask_vol, bid_vol, bid_vol / ask_vol ratio
FROM q1

Detect quote stuffing/fading (Volume dropoff)

Detect where the order book thins out rapidly after the first two levels. This signals lack of depth (fading) or fake orders (stuffing).

SELECT
  avg(asks[2, 1:3]) top,
  avg(asks[2, 3:6]) deep
FROM fx_order_book
WHERE top > 3 * deep

Detect sudden bid/ask drop

Look for cases where the top bid/ask volume dropped compared to the prior snapshot — potential order withdrawal ahead of adverse movement.

SELECT * FROM (
    SELECT
        t2.ts,
        t2.symbol, 
        t1.asks[2, 1] prev_ask_vol,
        t2.asks[2, 1] curr_ask_vol,
        t1.bids[2, 1] prev_bid_vol,
        t2.bids[2, 1] curr_bid_vol
    FROM fx_order_book t1 JOIN fx_order_book t2 
    ON t1.symbol = t2.symbol AND t2.ts = t1.ts + 1_000_000)
WHERE prev_bid_vol > curr_bid_vol * 1.5 OR prev_ask_vol > curr_ask_vol * 1.5

Price-weighted volume imbalance

For each level, calculate the deviation from the mid price (midpoint between best bid and best ask), and weight it by the volume at that level. This shows us whether there's stronger buying or selling interest.

WITH q1 AS (
    SELECT *, round((asks[1][1] + bids[1][1]) / 2, 2) mid_price
    FROM fx_order_book
)
SELECT
    ts, symbol,
    mid_price,
    (asks[1] - mid_price) * asks[2] weighted_ask_pressure,
    (mid_price - bids[1]) * bids[2] weighted_bid_pressure
FROM q1

Detect Price Wall

We want to find an outlier in the record book's volumes. Is there a level at which there's much more volume than average volume at all levels? This requires array filtering.

Other similar use cases:

• Place Just Behind a Large Order (Iceberg): Identify price levels with unusually high volume and place just behind them.

One example for solving it:

SELECT
  generate_subscripts(asks, 1) AS lvl,
  (asks[lvl])[1] AS price,
  (asks[lvl])[2] AS volume
FROM fx_order_book
WHERE volume >= 5*avg(asks[2])

Another approach is to support inline array filtering, like this:

SELECT 
  asks[2][ vol -> vol > 5*array_avg(asks[2]) ] 
FROM fx_order_book

LIMITATION: the above syntax does not give us the index (i.e., the order book level) at which an outlier was found.

Placing smart limit orders

Choose optimal price levels where your order is likely to get filled with minimal slippage and risk

Smart-place at hidden liquidity gaps (identify weak support/resistance)

Query: Detect a price level where volume sharply drops from the previous one.

SELECT
  asks[1] AS price,
  asks[2] AS volume,
  shift(ask_volume, 1) AS volume_shr,
  transpose(ARRAY[price, volume, volume / volume_shr]) AS with_drop_ratio,
  with_drop_ratio[ x -> x[3] > 2 ] AS large_drops
FROM fx_order_book
WHERE dim_length(large_drops, 1) > 0

TODOs derived from the use cases:

• reference the result of a function in the same projection list: SELECT f(x), x + 1 FROM long_sequence(1)
• temporary columns in a projection, just for the sake of referencing them in others, not visible in result
• find the index of an element in a sorted array, or insertion point if not present: SELECT index_of(asks, 3.32) FROM orderbook
• sum, avg over an array
• csum(ary) -- array of cumulative sums
• array filtering using arbitrary boolean expression
• basic arithmetic (+ - * /) between array and scalar
• generate_subscripts table function

More TODOs

• arr[1:-1]
• stddev window function
• array_min, array_max, array_stddev
• lag(array) window function

Use the issue body as the entry point and choose one unchecked array-querying TODO, such as arbitrary boolean filtering or generate_subscripts. First confirm the intended syntax and scope against the listed SQL examples; done means the selected capability works for its corresponding use case and the TODO can be marked complete.