Query Total of Number of Sessions over 2 months during standard business hours

Just as the lengthy title describes, our bot went live on 3/26, I am trying to query the total of number of sessions over two months during “core business hours” and “non-core business hours”. Core business hours being 09:00 to 17:00 and and non-core business hours being 00:01 to 08:59 and 17:01 to 23:59. I assume there is a loop I can write to make this work but I can’t figure it out. Below is a query that returns the total number of sessions on April 1st during core business hours, hopefully this helps whoever knows the answer out there. Thank you

d date: catd(model=“date”) s.beginTime as date , s.transactionCount > 1, s.beginTime == in {“2020-04-01T09:00”…“2020-04-01T17:00”}

assuming that it’s ok to do a bit of post-processing, I would use this query to extract the counts of sessions per hour

distribute hour : catd(pattern="HH") s.beginTime as hour, s.transactionCount > 1 order by hour asc

which also has the added bonus of being nicely graph-able. :slight_smile:

Please let me know if this helps, or if I can help further!


Here’s another option that combines tinglesby87’s and eaili’s query.

d date: 
  catd(model="date") s.beginTime as date, 
  catd(pattern="HH") s.beginTime as hour, 
  hour == in {"09".."18"}
1 Like