Nested Select within IN() not working - NRQL

So I am trying to nest a select inside an IN(), but seems NRQL doesn’t like it:

SELECT orderId 
FROM prod_PlansEventLog 
WHERE orderId IN (
    (SELECT orderId FROM prod_PlansEventLog WHERE step like 'step9.1%')
)
AND step NOT LIKE 'step10%'

What I am trying to do, is find orderIds that made it to step9.1 but did not make it to step10. If nested selects inside IN() is not allowed, can anyone think of a way I could do this in NRQL?

Much appreciated

Unfortunately, it’s not possible to sub query. We only have sub aggregation.

Does the event have an error on the step when it can’t make it to 10?

Thanks for the reply.

Shame, the issue is we don’t know where its failing, looks like networking issue between two applications. So we are trying to find these users that get to step9 but never makes it to step 10.

Thought we could get round it with group by but doesn’t look like that is supported either

Hi, @oli.girling1: In NRQL, GROUP BY is FACET.

1 Like

Thanks, turns out this cant work actually. Been playing around with filter as well to see if that could work but also no luck :frowning:

You can try something like this:
SELECT count(*) FROM prod_PlansEventLog WHERE step like 'step9.1%' AND step LIKE 'step10%' facet orderId, step limit max

Facets can be a drag in the way they display for step, but you can look at combining facet with filter(). This will get you a table you can process in excel etc.

Thanks for the reply. That query doesnt give us any data sadly.

Here is what our data looks like:

Our new attempt is below which seems to only get the ids we want, but the problem is it doesn’t get all the results and when you run it multiple times it often returns different results. All the results are actually correct but it doesn’t get all of them

SELECT * FROM 
	 (SELECT 
	  filter(count(*), WHERE step LIKE 'step10%') AS 'step10', 
	  filter(count(*), WHERE step LIKE '%ReturnUrl%') AS 'step9' 
	 FROM prod_PlansEventLog 
	 WHERE 
		 (step LIKE '%ReturnUrl%' OR step LIKE 'step10%')
	 FACET numeric(orderId) as 'OrderId' LIMIT MAX) 
WHERE step10 = 0 
SINCE 10 day ago LIMIT MAX

Also when I download the results via CSV also often get different or less amount of results. See gif below

Tab-1631703406297

A few things going on there. The facet is limited to maybe 2000 items (could be more for some accounts I think). So your sub aggregation will return up to 2000 items. You are then filtering it on your outside. Since your facet isn’t getting you everything its not going to be consistent.

Since you are using filter you do not need the Where in the sub aggregation.

Before using sub aggregation did you simply try to produce a table with the results like this?

SELECT filter(count(*), WHERE step LIKE '%ReturnUrl%') AS 'step9', filter(count(*), WHERE step LIKE 'step10%') AS 'step10' FROM prod_PlansEventLog FACET orderId SINCE 10 day ago LIMIT MAX

Without knowing what you have in your data we have to guess at some some things. I’m going to guess you have more than 2000 orderId’s in the 10 day window. If that’s the case reduce your time window to a period of time when you don’t have 2000 orderid’s. Try that and see if it works.

2 Likes