For each of the players in the following {}-delimited list, I intend
(with 2 Agoran Consent) to award em the Patent Title of Employee of the
Year 2021 (which will be allowed by Herald regulation HR4 if enacted):
{ais523, Aspen, ATMunn, Falsifian, G., Jason, Murphy, nix, R. Lee,
Telna, Trigon}
These are the players who published at least 13 weekly reports and/or
3 monthly reports during 2021. (Does not include revisions. Jason's
year-long service as Assessor is also not included below, as none of eir
duties involve a report; in particular, while the Promotor has a weekly
report even if the pool was already empty, if there are no proposals to
assess in a given week then the rules don't require the Assessor to
announce that fact.)
adop=> select
p.player_name, o.office_name, f.frequency_name,
count(distinct date_trunc('week', e.event_timestamp))
from events e
join offices o on o.office_id = e.office_id
join players p on p.player_id = e.acting_player_id
join event_types t on t.event_type_id = e.event_type_id
join report_frequencies f on f.frequency_id = e.frequency_id
where e.event_timestamp >= '2021-01-01'
and e.event_timestamp < '2022-01-01'
and t.event_type_name = 'Report published'
group by p.player_name, o.office_name, f.frequency_name
order by p.player_name, o.office_name, f.frequency_name;
player_name | office_name | frequency_name | count
--------------+-----------------------+----------------+-------
ais523 | Mad Engineer | Weekly | 14
Aspen | Promotor | Weekly | 30
Aspen | Registrar | Monthly | 2
Aspen | Registrar | Weekly | 11
ATMunn | Notary | Weekly | 27
cuddlybanana | Webmastor | Monthly | 1
Falsifian | Registrar | Monthly | 3
Falsifian | Registrar | Weekly | 12
G. | Arbitor | Weekly | 21
G. | Mad Engineer | Weekly | 3
G. | Ministor | Monthly | 4
G. | Referee | Weekly | 11
G. | Tracker of the Device | Weekly | 11
Gaelan | Referee | Weekly | 8
Jason | Referee | Weekly | 1
Jason | Rulekeepor | Monthly | 10
Jason | Rulekeepor | Weekly | 23
Jason | Stonemason | Weekly | 30
Jason | Webmastor | Monthly | 1
JTAC | Referee | Weekly | 5
Murphy | ADoP | Weekly | 48
Murphy | Tailor | Monthly | 12
Murphy | Tailor | Weekly | 41
nix | Herald | Monthly | 9
nix | Herald | Weekly | 2
nix | Ministor | Monthly | 7
nix | Ministor | Weekly | 1
nix | Webmastor | Monthly | 2
R. Lee | Ministor | Monthly | 1
R. Lee | Referee | Weekly | 13
R. Lee | Registrar | Monthly | 1
R. Lee | Registrar | Weekly | 5
Shy Owl | Registrar | Monthly | 1
Shy Owl | Registrar | Weekly | 4
Telna | Arbitor | Weekly | 13
Trigon | Treasuror | Monthly | 5
Trigon | Treasuror | Weekly | 45
Ubercrow | Tailor | Weekly | 1
(38 rows)
