small question about UDD (related to Tracker)

[Patrice Duroux <duroux.patrice@orange.fr>]

Dear Debian QA team,

For my curiosity, I am currently working to get a kind of QA webpage for the
list of packages that have not been «uploaded» since years (more than 3 by
default), for example: https://tracker.debian.org/pkg/beav, meaning also no
change across Debian releases.
Technically it is mostly a script doing a SQL query using one public mirror of
UDD to generate the data as a JSON file and a dynamic HTML to display it in a
«fancy» table.

But then I realise that the view 'bugs_count' in the UDD database does not give
the same result as the Tracker gives.
For instance: https://tracker.debian.org/pkg/liblocale-gettext-perl

all:    1
RC:     0
I&N:    0
M&W:    1
F&P:    0
patch:  0

whereas: udd=> select * from bugs_count where source='liblocale-gettext-perl';

 source | rc_bugs | all_bugs 
--------+---------+----------
(0 ligne)

Why not having the same definitions?
Looking at the 'bugs_count' view definition, it shows that there is a
restriction to 'pending' bugs. But I did not check what is done in Tracker.
Also does Tracker use UDD? and so could it be possible then to share the same
definition?
Do you think that it should be possible to get a view with also the other values
(I&N, etc.)?

Thanks,
Patrice

ps: by the way, my current SQL query

SELECT JSON_AGG(t.*) FROM (SELECT
source,version,date,COALESCE(all_bugs,0),COALESCE(rc_bugs,0),COALESCE(insts,0),C
OALESCE(vote,0)
FROM upload_history
JOIN (SELECT source,MAX(version) AS version FROM upload_history WHERE
distribution='unstable' GROUP BY source) AS s USING (source,version)
JOIN sources USING (source,version)
LEFT JOIN popcon_src USING (source)
LEFT JOIN bugs_count USING (source)
WHERE release='sid' AND date < current_date - INTERVAL '3 year') AS t;