Month-by-month requests submitted

Automatically updated version with more detail

Month	Year	Requests Submitted
1	2020	364
2	2020	349
3	2020	379
4	2020	541
5	2020	667
6	2020	578
7	2020	574
8	2020	489
9	2020	494
10	2020	549
11	2020	544
12	2020	522
1	2021	621
2	2021	522
3	2021	545
4	2021	557
5	2021	470
6	2021	430
7	2021	416
8	2021	408
9	2021	474
10	2021	339
11	2021	272
12	2021	262


SELECT EXTRACT(MONTH from timestamp) as "Month",  EXTRACT(YEAR from timestamp) as "Year", COUNT(*) as "Requests Submitted"
FROM log l
WHERE 1=1
    AND action = 'Email Confirmed'
    AND l.objecttype = 'Request'
    AND l.timestamp BETWEEN '2019-01-01 00:00:00' AND '2021-01-01 00:00:00'
GROUP BY EXTRACT(Month from timestamp), EXTRACT(YEAR from timestamp)
Order by year, month;

Users who have closed requests in 2021

Automatically updated version

Username	Requests closed
ElHef	1508
Blablubbs	1024
JJMC89	945
Jack Frost	479
LuK3	254
JavaHurricane	220
Alex Noble	212
Stwalkerster	111
Rich Smith	106
urbanecm	70
FlightTime	69
Oshwah	40
Operator873	39
TNT	32
DannyS712	18
AfroThundr3007730	16
AmandaNP	16
CodeLyoko	13
1997kB	12
QueerEcofeminist	11
Dreamy Jazz	11
GeneralNotability	9
Waggie	8
AntiCompositeNumber	7
Praxidi cae	5
Ivanvector	4
Alpha3031	4
Sunmist	3
Callanecc	3
Dane	3
Tks4Fish	3
maurelio	2
TheSandDoctor	2
Clarkcj12	2
TBloemink	2
Matthewrbowker	2
Jon Kolbert	1

select u.username as "Username", count(*) as "Requests closed"
from log l
inner join user u on l.user = u.id
where 1=1
and l.action like 'Closed %'
AND l.timestamp BETWEEN '2020-01-01 00:00:00' AND '2021-01-01 00:00:00'
group by u.username
order by 2 desc

Average response time

The average time from a request's email confirmation until the first response sent to the user. Times are hours.

This is split between requests which were deferred to a different queue, and requests which were not deferred to a different queue

Automatically updated version with more detail

Month	Non-deferred requests	Deferred requests
2020-01	943.31	1636.99
2020-02	797.52	1258.78
2020-03	547.88	654.80
2020-04	61.31	88.50
2020-05	1.07	73.88
2020-06	1.87	81.21
2020-07	2.75	184.65
2020-08	3.64	472.44
2020-09	3.53	393.79
2020-10	3.96	204.94
2020-11	4.28	66.13
2020-12	3.63	132.60
2021-01	3.16	182.32
2021-02	4.39	212.18
2021-03	3.37	875.27
2021-04	5.05	1126.37
2021-05	5.68	982.71
2021-06	8.11	808.18
2021-07	6.08	218.67
2021-08	7.15	148.53
2021-09	8.57	164.21
2021-10	11.12	192.34
2021-11	4.46	173.54
2021-12	6.99	158.81


with requeststats as (
    select distinct
        r.id,
        r.date as submitdate,
        lec.timestamp as confirmdate,
        MIN(lc.timestamp) over (partition by r.id) as responsedate,
        case when ld.timestamp > 0 then 1 else 0 end as deferred
    from request r
    inner join log lec on lec.objecttype = 'Request' and lec.objectid = r.id and lec.action = 'Email Confirmed'
    left join (
        -- first close
        select logfc.objectid as "request", MIN(logfc.timestamp) as "timestamp"
        from log logfc
        where 1=1
            AND ( logfc.action LIKE 'Closed %' OR logfc.action = 'SentMail' )
            AND logfc.action <> 'Closed 0'
            AND logfc.objecttype = 'Request'
            AND logfc.timestamp > 0 -- I hate MySQL...
        GROUP BY logfc.objectid
        ) lc on lc.request = r.id
    left join (
        -- first deferral
        select logfd.objectid as "request", MIN(logfd.timestamp) as "timestamp"
        from log logfd
        where 1=1
            AND logfd.action LIKE 'Deferred to %'
            AND logfd.objecttype = 'Request'
            AND logfd.timestamp > 0
            AND logfd.user <> -1 -- skip system deferrals
        GROUP BY logfd.objectid
        ) ld on ld.request = r.id
    where 1=1
        and r.status = 'Closed'
        AND r.date BETWEEN '2019-01-01 00:00:00' AND '2021-01-01 00:00:00'
        AND lc.timestamp IS NOT NULL
)
SELECT concat(EXTRACT(YEAR from submitdate), '-', lpad(EXTRACT(Month from submitdate),2,'0')) as "Month",
       round(avg(case when deferred = 0 then unix_timestamp(responsedate) - unix_timestamp(confirmdate) end)/60/60, 2) "Non-deferred requests",
       round(avg(case when deferred = 1 then unix_timestamp(responsedate) - unix_timestamp(confirmdate) end)/60/60, 2) "Deferred requests"
FROM requeststats
group by EXTRACT(Month from submitdate), EXTRACT(YEAR from submitdate)
ORDER BY 1 asc