Bug#1060201: qa.debian.org: [udd] carnivore_emails is lacking lots of entries

[Andreas Tille <tille@debian.org>]

Package: qa.debian.org
Severity: normal

Hi,

I tried to analyse closed bugs using done_email via carnivore_emails but realised
that this table is lacking lots of entries where I could easily add several from
my own memory:

SELECT done_email, COUNT(*) FROM (
SELECT done_email FROM archived_bugs WHERE id IN (SELECT id FROM (SELECT ab.id, ce.id AS ce_id
          FROM archived_bugs ab
          LEFT JOIN carnivore_emails ce ON ce.email = ab.done_email
) noid WHERE ce_id IS NULL ) AND done_email NOT IN ('ftpmaster@ftp-master.debian.org','noreply@salsa.debian.org','unknown')
) miss GROUP BY done_email
ORDER BY count DESC
;

                       done_email                       | count 
--------------------------------------------------------+-------
 gothicx@sapo.pt                                        |  5221
 bartm@quantz.debian.org                                |  2665
 doko@cs.tu-berlin.de                                   |  2555
 kitame@northeye.org                                    |  2371
 md@Linux.IT                                            |  2056
 herbert@gondor.apana.org.au                            |  1900
 damog@merkel.debian.org                                |  1788
 daniel.baumann@progress-technologies.net               |  1393
 max@stro.at                                            |  1327
 bunk@fs.tum.de                                         |  1278
 debian-bts@adam-barratt.org.uk                         |  1155
 ccheney@cheney.cx                                      |  1031
 sramacher@respighi.debian.org                          |   992
...
 zweistein12@gmx.de                                     |     1
(9075 rows)

I wonder how the carnivore_* tables are filled and whether you want me
to draft some INSERT statements filling up the most relevant emails
where I would volunteer to sort the according IDs.

Kind regards
   Andreas.