Build stats page to show how the average time of a request in a queue has changed over time

[stwalkerster]

I built this query for LuK3, which should be usable as a starting point query.

WITH
targetqueue AS (SELECT 'Deferred to proxy check' target),
queuetime AS (
    SELECT id, name, date, SUM(TIMESTAMPDIFF(SECOND, entrytime, exittime)) duration, COUNT(1) deferrals
    FROM (
        SELECT
            r.id,
            r.name,
            r.date,
            lstart.timestamp AS entrytime,
            lend.timestamp AS exittime,
            CASE WHEN MIN(lend.timestamp) OVER (PARTITION BY r.id, lstart.timestamp) = lend.timestamp THEN 1 ELSE 0 END AS selector
        FROM request r
        INNER JOIN log lstart ON r.id = lstart.objectid AND lstart.objecttype = 'Request'
        INNER JOIN log lend ON r.id = lend.objectid AND lend.objecttype = 'Request' AND lend.timestamp > lstart.timestamp
        WHERE lstart.action = (SELECT target FROM targetqueue)
        AND (lend.action LIKE 'Deferred to %' OR lend.action LIKE 'Closed %')
        AND (lend.action <> (SELECT target FROM targetqueue))
    ) cudeferrals
    WHERE selector = 1
    GROUP BY id, name, date
),
activemonths AS (
    -- Pull all values from two generated sequences (values 2008 to 2050, and values 1 to 12) to generate dates.
    -- Filter the resulting set down to months between the earliest and latest log entries.
    -- This gives us a complete list of all months when the tool has been active, even if there was no activity on the
    -- tool for a specific month
    SELECT (y.seq * 100) + m.seq AS sortkey
    FROM seq_2008_to_2050 y
    CROSS JOIN seq_1_to_12 m
    WHERE (y.seq * 100) + m.seq >= (SELECT MIN(EXTRACT(YEAR_MONTH FROM timestamp)) FROM log WHERE timestamp > 0 AND action = (SELECT target FROM targetqueue))
      AND (y.seq * 100) + m.seq <= (SELECT MAX(EXTRACT(YEAR_MONTH FROM timestamp)) FROM log WHERE timestamp > 0)
)
SELECT data.*
FROM activemonths
LEFT JOIN (
    SELECT EXTRACT(YEAR_MONTH FROM date) month, AVG(duration) average, STDDEV(duration) stddev, COUNT(duration) requests, SUM(deferrals) deferrals
    FROM queuetime
    GROUP BY EXTRACT(YEAR_MONTH FROM date)
) data ON data.month = activemonths.sortkey;

The ask in this issue is to wrap this into a stats page, allowing the user to choose the queue (except the default queue) to retrieve the stats for (or just iterate over all non-default queues). The results can then be shown in a table and/or graphed to make it look pretty.

Possible extensions:

• Add a month-to-month delta for each line)
• Produce something similar for the default queue. This will need fancier logic though.
• Consider the edge case of requests being deferred into a queue before email confirmation