Kusto Join Query - How to join 2 tables on $left.column1 < $right.column2

Question

I have 2 tables in Kusto:

1. windowScans - each row is from this format : windowStart:long, windowEnd:long
2. files - each row is from this format: timestamp:long, fileId:string

I would like to join for each file all the windowScan rows that matches: timestamp > windowStart && timestamp < windowEnd. The result should be a table of all files, and for each file all the matching pairs of windowScans. It is possible that a windowScan row will appear in many files. Any idea how to perform the query?

Answer 1

Here is one solution:

let windowsScan = datatable(windowStart:long, windowEnd:long)[1,5, 6,8, 10, 14];
let files = datatable(timestamp:long, fileId:string)[3,"a", 4,"b", 4,"c", 6, "a", 11,"a", 13, "b"];
windowsScan
| extend timestamp = range(windowStart, windowEnd, 1)
| mv-expand timestamp to typeof(long)
| join kind=inner (files) on timestamp
| summarize take_any(windowStart, windowEnd) by fileId, timestamp 

Results:

fileId	timestamp	windowStart	windowEnd
a	3	1	5
b	4	1	5
c	4	1	5
a	6	6	8
a	11	10	14
b	13	10	14