AMO:Developers/Queries

Useful SQL Queries

Add-on Count

Retrieves the number of add-ons that support Firefox, are public, sandboxed, or nominated, and are active.

 select count(*) from addons inner join versions on versions.id=(select id from versions where versions.addon_id=addons.id order by id desc limit 1) inner join applications_versions on applications_versions.version_id=versions.id where addons.inactive=0 and addons.status in (1,3,4) and applications_versions.application_id=1;

Listing of all 3.0 Add-ons without 3.1 compatibility

Also used for a mail merge spreadsheet, hence the inclusion of developer contact info.

select a.addon_id, translations.localized_string, sum(count), compat.topver, users.firstname, users.lastname, users.email from update_counts a join (select a.id, a.supportemail, a.name, max(e.version) as topver from addons a, versions c, applications_versions d, appversions e where a.id = c.addon_id and c.id=d.version_id and d.max = e.id and a.addontype_id =1 and d.application_id = 1 and e.version >= 3.0 group by a.id) as compat on (compat.id = a.addon_id) join translations on (translations.id = compat.name) join addons_users on (addons_users.addon_id = a.addon_id) join users on (addons_users.user_id = users.id) where date = '2009-01-14' and compat.topver < 3.1 and translations.locale = 'en-us' group by 1 order by 3 desc