Month-by-month requests submitted

Note that the "ACC Wizard" which directs requesters to self-create instead of submitting requests was implemented in July 2019, explaining the sudden drop.

Month	Year	Requests Submitted
1	2019	2242
2	2019	1610
3	2019	1757
4	2019	2371
5	2019	2255
6	2019	1344
7	2019	1410
8	2019	381
9	2019	363
10	2019	358
11	2019	316
12	2019	250
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	521


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 2020

Username	Requests closed
ElHef	2198
JJMC89	1073
Stwalkerster	991
LuK3	939
mdaniels5757	587
Rich Smith	479
Jack Frost	165
Kostas20142	147
Operator873	146
Jon Kolbert	132
Alex Noble	115
Dreamy Jazz	111
Alpha3031	110
Riley	97
DannyS712	96
FlightTime	77
JavaHurricane	74
QueerEcofeminist	59
AmandaNP	52
1997kB	43
Praxidi cae	40
frood	39
OcarinaOfTime	36
Oshwah	30
TBloemink	25
Dane	21
Ivanvector	21
Callanecc	19
urbanecm	18
Josve05a	16
Tks4Fish	16
Waggie	14
QEDK	13
AfroThundr3007730	13
Lord Bolingbroke	13
Sunmist	13
OhKayeSierra	12
AntiCompositeNumber	11
Clarkcj12	5
Mz7	4
L235	3
maurelio	2
Matthewrbowker	2
FastLizard4	1
TheDragonFire	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

Month	Non-deferred requests	Deferred requests
2019-01	2130.01	2258.27
2019-02	2312.61	2088.21
2019-03	2568.63	2853.49
2019-04	2420.66	3144.49
2019-05	2997.63	3538.55
2019-06	2678.33	3678.89
2019-07	2286.18	3233.62
2019-08	2815.68	2488.74
2019-09	1999.24	1833.19
2019-10	1430.75	1465.76
2019-11	1062.17	957.58
2019-12	601.74	1434.07
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.64	132.90


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