Useful SQL queries for Gerrit Admin

changes_created_by_specific_user.sql

SELECT COUNT(c.change_id)
FROM changes c, accounts a
WHERE c.created_on >= (now() - interval '1 year')
AND c.owner_account_id = a.account_id
AND a.preferred_email similar to '%@gmail.com';

changes_created_each_hour.sql

SELECT date_part('hour', created_on), COUNT(date_part('hour', created_on))
FROM changes
WHERE created_on >= (now() - interval '1 year')
GROUP BY date_part('hour', created_on)
ORDER BY date_part('hour', created_on) ASC;

changes_status_count.sql

SELECT status, COUNT(*)
FROM changes
WHERE created_on >= (now() - interval '1 year')
GROUP BY status;

cherry_pick_count.sql

SELECT COUNT(*)
FROM change_messages
WHERE message LIKE '%Cherry Picked from%'
AND written_on >= (now() - interval '1 year');

find_outdated_gerrit_projects.sql

SELECT dest_project_name, MAX(last_updated_on)
FROM changes
GROUP BY dest_project_name
HAVING MAX(last_updated_on) < (now() - interval '180 days')
ORDER BY MAX(last_updated_on) ASC;

list_domians.sql

SELECT SUBSTRING(external_id FROM POSITION('@' IN external_id)+1) AS domain, count(*)
FROM account_external_ids
WHERE external_id LIKE 'mailto:%'
GROUP BY domain;

top_20_active_branch.sql

SELECT dest_branch_name, COUNT(dest_branch_name)
FROM changes
WHERE created_on >= (now() - interval '1 year')
GROUP BY dest_branch_name
ORDER BY COUNT(dest_branch_name) DESC
LIMIT 20;

top_20_active_projects.sql

SELECT dest_project_name, COUNT(dest_project_name)
FROM changes
WHERE created_on >= (now() - interval '1 year')
GROUP BY dest_project_name
ORDER BY COUNT(dest_project_name) DESC
LIMIT 20;

Some useful Gerrit database SQL query
Gerrit Administrator can use gerrit gsql command to access Gerrit database and query.

• top_20_active_projects.sql
• top_20_active_branch.sql
• changes_created_each_hour.sql
• cherry_pick_count.sql
• changes_created_by_specific_user.sql
• changes_status_count.sql
• find_outdated_gerrit_projects.sql: find projects whose lastest changes were updated 180 days ago.