Control: usertag -1 udd
Am Sun, Jan 07, 2024 at 01:38:35PM +0100 schrieb Andreas Tille:
> 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
> ('[email protected]','[email protected]','unknown')
> ) miss GROUP BY done_email
> ORDER BY count DESC
> ;
>
> done_email | count
> --------------------------------------------------------+-------
> [email protected] | 5221
> [email protected] | 2665
> [email protected] | 2555
> [email protected] | 2371
> [email protected] | 2056
> [email protected] | 1900
> [email protected] | 1788
> [email protected] | 1393
> [email protected] | 1327
> [email protected] | 1278
> [email protected] | 1155
> [email protected] | 1031
> [email protected] | 992
> ...
> [email protected] | 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.
BTW, its probably pretty easy to resolve >900 of these missing e-mails:
CREATE TEMPORARY TABLE missing_in_carnivore_emails AS
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
('[email protected]','[email protected]','unknown')
) miss GROUP BY done_email
ORDER BY count DESC
;
SELECT DISTINCT done_name, done_email, cn.id FROM
(SELECT BTRIM(done_name, '"') AS done_name, done_email FROM archived_bugs) ab
LEFT JOIN carnivore_names cn ON cn.name = ab.done_name
WHERE done_email in (SELECT done_email FROM missing_in_carnivore_emails WHERE
count > 10)
AND done_name IS NOT NULL AND done_name != ''
AND id IS NOT null
;
done_name | done_email
| id
---------------------------------+-------------------------------------------------+------
Camm Maguire | [email protected]
| 6158
Ross Vandegrift | [email protected]
| 734
Michael Ablassmeier | [email protected]
| 2751
Neil McGovern | [email protected]
| 3708
Torsten Landschoff | [email protected]
| 6320
Agney Lopes Roth Ferraz | [email protected]
| 4000
Galen Hazelwood | [email protected]
| 1241
Anand Kumria | [email protected]
| 4175
Adam Rogoyski | [email protected]
| 1102
Christophe Barbe | [email protected]
| 2054
Yann Dirson | [email protected]
| 5804
Arjan Oosting | [email protected]
| 5366
Julian Gilbey | [email protected]
| 3875
Norman Jordan | [email protected]
| 3513
Michael Piefel | [email protected]
| 1111
Frederic Lepied | [email protected]
| 2460
...
Neil Williams | [email protected]
| 1552
Christopher Martin | [email protected]
| 2754
Andrew Lenharth | [email protected]
| 3085
(922 rows)
This statement could be easily turned into injects and would be a first
approach to enhance
the carnivore_emails table with more ids.
If you give some green light I could create such a statement and maybe more
enhancements
by looking into more tables.
Kind regards
Andreas.
--
http://fam-tille.de
