Highest event data by period

While looking at the SLA report email, I thought that the number of PageViews on one of our applications was the highest we had seen. I managed to write a nested query to return the event data to confirm my suspicions. I was then looking at how to present this information for all applications. Unfortunately, I could not seem to get a query working.

The initial query returns the maximum number of PageViews and the week.

SELECT max(page_hits) FROM (SELECT count(*) AS page_hits FROM PageView WHERE appName='xxxxxx' FACET weekOf(timestamp)) FACET weekOf(timestamp) since 12 months ago limit max

I haven’t figured out a way to get multiple rows for each Browser application. Anyone able to help me get the results?

Hi, @stefan_garnham: Try this:

SELECT max(page_hits) FROM (SELECT count(*) AS page_hits FROM PageView FACET appName, weekOf(timestamp)) FACET appName, weekOf(timestamp) SINCE 12 months ago LIMIT MAX

Unfortunately that only gives a single application, not all applications with their highest throughput period.

Nearest I have managed is the query below, but the timestamp is the same for all applications, which I don’t think is correct.

SELECT max(page_hits) FROM (SELECT count(*) AS page_hits FROM PageView FACET appName) FACET appName, weekOf(timestamp) SINCE 12 months ago LIMIT MAX

Edited to add: I have confirmed the page_hits is correct but the timestamp is the same for whichever is the top throughput application’s period.

Hey @stefan_garnham - Did you ever get this sorted? If not, let’s see if we can dig up and answer.

No, I didn’t get any further with this query.