Calculate average elapsed time

[SmartProtection]

Hi everybody, have been tasked with a KPI that I can't seem to figure out, so we'll see if anyone can give ideas. 

To keep it very simple, my company sends out queries to the internet, and clients hire us to do this for their products and present them with the results we collect. 

Depending on the service level contracted, we have to send out queries at least 1x every 24 hours, 1x every 12 hrs, 1x every 6 hrs, 1x every hr, etc. My boss wants me to calculate a KPI that would be the average time elapsed between each query sent out. From there I could place filters to segment by client, product, service, etc. 

We have a table, queries_executed, that includes a column which registers the time that each query was sent out. Is there a possible way to calculate the time difference for each of the executed queries in chronological order, and then calculate the average of these time differences? 

As an alternative, I have been able to calcule the avg. number of queries sent out per week/day/hr., but my boss is determined to have a timestamp KPI representing the average time difference between executed queries, to ensure that we are meeting the agreed-upon service levels  for each client, which is sending out a query once every x hrs (depending on service level contracted). 

Including some of the relevant column headers in the Queries_Executed table: 

- query_execution_id (generates 1 unique id for each query that is generated)

- query_executed_time (timestamp)

- query_id (this is the id of the phrase used in the query, which can be executed more than 1 time)

- network 

-client_id

-product_id

-number_results_collected

Any feedback or ideas to get me on the right track would be greatly appreciated! Thanks!

[edwin · Accepted solution]

depending on volume:

1. you can loop through your data (as you said) in chronological order and use the peek function to get the previous time stamp

2. if the volume is large add a rownumber to each record (order by timestamp), then inner join to itself rownumber-1 and the timestamp, this way you have in one record the current rownumber, the current timestamp of that row, the timestamp of prior row (rownumber-1), you can now get the difference/interval

[edwin]

obviously, the first row will drop off (as there will be no row 0 to join with).  if you need that row, do a left join

[SmartProtection]

Thanks! The peek function worked. I have the flat rate for all overall queries, however it doesn't work when I apply filters (such as client, week, product, etc.) - the equation continues calculating the time difference between the query executed immediately before, regardless of the filters applied. 

I'm happy with having at least the overall flat rate, but if you have any ideas regarding the filters, I'd be happy to hear you out. Thanks!

[edwin]

both will work but be warned that the solution using peek function is slower and may not be feasible for large volume.  glad it helped and yw.