Average Time Between First and Last Event with Given Attribute

Our iOS app allows airline employees to manage guest reservations. We’re looking to measure the employee/guest interaction time, and I thought a reliable way to do this would be to measure the average time between the first custom event and the last custom event with a given attribute - the confirmation code.

Here’s what I started with:
SELECT (max(timestamp)-min(timestamp))/60000 as 'Time in Minutes' FROM <CustomEvents> WHERE appName = <appName> AND confirmationCode IS NOT NULL FACET confirmationCode

While this gives me a list of interaction times, chart types and functions can’t be nested, so I can’t throw an average() in there or seemingly look at this data in any meaningful way.

Then I thought of adding something like timeInReservation as a custom attribute to my events, but the problem with that is we have no way of knowing which event a user will trigger last. I tried this:
SELECT latest(timeInReservation) FROM <CustomEvents> WHERE appName = <appName> FACET confirmationCode
But again, this just gives me a list and can’t be nested with average().

Is there some NRQL magic I could use here?

Hi, @megan.mcpherson: I will give you the answer for 1000 bonus miles. :upside_down_face:

NRQL does support nested queries; you may be able to nest your first query inside an outer query to compute the average.

2 Likes

Hi @philweber, thanks so much for the answer! I would totally give you 1000 bonus miles if I had the power :slight_smile:

You’re exactly right! NRQL does support nested queries, I was just doing it wrong and you helped me find the right documentation. For others who might look at this, here’s what I ended up doing:

SELECT average(timeInMinutes)
FROM (
  SELECT (max(timestamp)-min(timestamp))/60000 as timeInMinutes
  FROM <CustomEvents>
  WHERE appName = <appName> AND confirmationCode IS NOT NULL 
  FACET confirmationCode, sessionId
  LIMIT MAX
)
SINCE <timeRange>
LIMIT MAX

Thanks again!!

1 Like

Keep in mind this this is limited by facet max. Probably 3000.

There is earliest() and latest().

Tracing might be something you could make use of in the future.