using output of one query in another in kql

Question

let average = materialize(FooTable
| summarize avg(value) by group, class
| summarize arg_min(avg_value, class) by group

This should output something like (i.e. minimum value for the group averages per class):

group	class	avg_value
G1	C1	100
G2	C2	150
..	..	..

Now, I would like to display all the group, class and value row which shows the delta from their group's minimum class average as calculated by the query above.

FooTable
| where value > ( here I want to insert the query to get min by group and class)

Output should be something like:

group	class	min_avg_value	delta
G1	C1	100	0
G1	C2	120	20
G2	C1	200	50
G2	C2	150	0
..	..	..	..

Thanks for the help in advance!

Answer 1

join

let FooTable = datatable (group:string, class:string, value:int)
[
    'G1' ,'C1', 100
   ,'G1' ,'C2', 120
   ,'G2' ,'C1', 200
   ,'G2' ,'C2', 150
];
let average = materialize(
FooTable
| summarize avg(value) by group, class
| summarize min(avg_value) by group
);
average
| join kind=inner FooTable on group
| extend delta = value - min_avg_value 

group	min_avg_value	group1	class	value	delta
G1	100	G1	C1	100	0
G1	100	G1	C2	120	20
G2	150	G2	C1	200	50
G2	150	G2	C2	150	0

Fiddle

Answer 2

lookup

let FooTable = datatable (group:string, class:string, value:int)
[
    'G1' ,'C1', 100
   ,'G1' ,'C2', 120
   ,'G2' ,'C1', 200
   ,'G2' ,'C2', 150
];
let average = materialize(
FooTable
| summarize avg(value) by group, class
| summarize min(avg_value) by group
);
FooTable
| lookup kind=inner average on group
| extend delta = value - min_avg_value 

Show expand view	group	class	value	min_avg_value	delta
G1	C1	100	100	0
G1	C2	120	100	20
G2	C1	200	150	50
G2	C2	150	150	0

Fiddle