Inspect the Rows Captured by a Window Join

Question

When computing a moving average using a window join, it's often useful to inspect which rows fall into each time window. Using the trades table and window w from the previous question (repeated below), how can you modify the window join so that, for each row in the output, you can see the full list of price values that were selected from the look-back window?

trades:([] sym:`A;
           time:09:30:00.000 09:30:15.500 09:30:32.100 09:30:58.750 09:31:12.300 09:31:45.600 09:32:03.200 09:32:28.900 09:32:55.400 09:33:18.700;
           price:100.5 100.7 100.3 100.9 101.2 101.0 100.8 101.5 101.3 101.1)
w:-00:01 00:00 +\: trades`time

Solution

out:wj1[w;`sym`time;update p:price from trades;(trades;((::);`price))]
out

sym time         price                   p
----------------------------------------------
A   09:30:00.000 ,100.5                  100.5
A   09:30:15.500 100.5 100.7             100.7
A   09:30:32.100 100.5 100.7 100.3       100.3
A   09:30:58.750 100.5 100.7 100.3 100.9 100.9
A   09:31:12.300 100.7 100.3 100.9 101.2 101.2
A   09:31:45.600 100.9 101.2 101         101
A   09:32:03.200 101.2 101 100.8         100.8
A   09:32:28.900 101 100.8 101.5         101.5
A   09:32:55.400 100.8 101.5 101.3       101.3
A   09:33:18.700 101.5 101.3 101.1       101.1

Explanation

Swapping the aggregation function avg for identity ((::)) tells wj1 not to reduce the window down to a single number — instead, it returns the entire list of price values that fell inside each row's window, unreduced.

The result column is still named price, matching the column it was built from (`price), and it now holds a list per row rather than a scalar — exactly the contents that avg (or any other aggregation) would otherwise have collapsed down to one value. This is a useful debugging step before trusting a window-join aggregate: confirm the right rows are actually landing in each window before computing a statistic over them. (Try the same query with wj instead of wj1: from 09:31:12.300 onwards, each list also starts with the last price before the window opened.)

The p column — the row's own original price, kept separate via update p:price from trades — is unaffected, and still shows a single value per row as before.