Make use of sub queries

Sub queries in TQL enable you to take your queries to the next level. A sub query is a complete TQL query by itself, though it is embedded within a main query. In the selection part of the main query you can access the results of the sub query, which effectively adds an extra level of processing you would otherwise not have. We introduced sub queries briefly on the page about constraints. On the page about query variables, we showed you how to add a Safetynet counter to your solution. Let's revisit that example now to see how a sub query makes it possible to learn more about Safetynet activation across sessions.

Compute aggregate information about a Safetynet counter

Referring back to the Safetynet counter we added, we can run a simple query to show the total number of times the Safetynet became active per session:

1la s.sv:n:safetyNetCounter
2

The result of the query delivers raw information that, by itself, is not very helpful:

For example, you may want to know the average number of Safetynet hits across sessions or other aggregate information. Obviously, it would be extremely inefficient to compute this by hand. This is where a sub query can help us, combined with aggregate functions available in TQL. You can embed your simple query into a larger context to get the mean, minimum, and maximum value of the Safetynet counter. This is a query to achieve that:

1lu  mean result.count as average, 
2    min result.count as minValue, 
3    max result.count as maxValue:
4result = @(la s.sv:n:safetyNetCounter as count)
5

The main query is able to look at the results of the sub query and process information about it:

Additional sub query examples

Here are some additional examples showing how you can use sub queries.

This query retrieves the mean number of sessions per day in a particular month:

1lu mean x.count as meanCount: x=@(d date: s.beginTime == '2024-04' , catd(model="date") s.beginTime as date order by count)
2

If your bot uses multiple channels and you require usage information about each of the channels, you can use a query similar to this, based on the channel parameter used:

1d result.date as date, result.channel as channel :
2    result = @( lu s.id as id, t.e.requestParameters.channel as channel, date:
3    		catd(model="date") s.beginTime as date)
4    order by date desc
5

Keep in mind that if you use relative dates in your queries in the Log Data Source, you should remove the time constraint in the Date Range Selector to avoid having potentially mismatching time ranges.

Multiple sub queries

One strength of sub queries is that you can combine multiple sub queries into a single query, and then present results from each of them.

This example shows relative occurrences of a particular product name in a single query. You would otherwise have to run each query separately to obtain the complete results:

1la sum espresso.count, sum macchiato.count:
2
3espresso = @(ca t.id as count :
4      t.e.type == "request",
5      t.e.userInput ~= ".*espresso.*"),
6
7macchiato = @(ca t.id as count :
8      t.e.type == "request",
9      t.e.userInput ~= ".*macchiato.*")
10

The query generates the following result: