[meta] Add more metrics

Internally, the Drupal Association is tracking more metrics this year. To start, we are running the queries below. In the future, these should be made into sampler metrics and put on https://drupal.org/metrics.

• Number of Active Accounts (logged in 1x per period of time)
• Number of Active Accounts (at least 1 activity per period of time)
• Number of Blocked Accounts (on this specific date)
• Number of Participants in the Issue Queues (who commented)
• Number of Participants in the Drupal Core Issue Queue (who commented)
• Number of Commits across All Projects
• Number of Commits to Drupal Core
• Number of Committers (at least 1 commit, all full projects, excluding Core)
• Number of Commits per User (excluding commits to Drupal Core)
• (overall commits / # of committers, both numbers excluding Drupal core and sandboxes)
• Number of Comments (created during the time period)
• Number of Comments on Issues (created during the time period)
• Number of Comments on Drupal Core Issues (created during the time period)
• Average Number of Comments per User (created during the time period)
• (overall comments / # of commenters)
• Average Number of Comments per User on Issues
• Average Number of Comments per User on Drupal Core Issues
• Number of Comments on Issues, which Updated the Node
• Number of Issues (created during the time period)
• Average Number of Issues per User (created during the time period)
• Number of Projects (created during the time period)
• Number of Sandbox Projects (created during the time period)
• Number of Full Projects (created during the time period)
• % issues about Drupal.org responded to in 48 hours
• (% of issues, created during the time period, which received first comment, not from the issue author, in less than
• Testbot (queries against qa.d.o)
• # of test requests sent
• # of Drupal core patches tested / Average core test queue time (min) / Average core test duration (min) / Average
• Same, D7 only
• Same, D8 only
• Number of Open Issues per Queue
• Content
• Webmasters
• Infrastructure
• Bluecheese ??
• Drupalorg nodes
• Drupalorg_crosssite nodes
• G.d.o queue
• A.d.o queue
• Average Response Time across All Queues (hours)
• (avg. time between issue published and 1st comment, not by issue author, created)
• Average Response Time in Drupal Core Issue Queue (hours)
• Average Response Time in Drupal.org Issue Queues (hours)
• (Content, Webmasters, Infrastructure, Bluecheese, Drupalorg, Drupalorg_crosssite)
• Number of Drupal core downloads

-- Number of Active Accounts (logged in 1x per period of time)
SELECT COUNT(*) FROM users WHERE status = 1 AND access > UNIX_TIMESTAMP('2014-01-01');
-- Number of Active Accounts (at least 1 activity per period of time)
SELECT count(1) 
FROM users u 
WHERE u.status = 1 AND u.uid IN (SELECT DISTINCT n.uid FROM node n WHERE n.status = 1 AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31') 
UNION SELECT DISTINCT c.uid FROM comment c WHERE c.status = 1 AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31') 
UNION SELECT DISTINCT nr.uid FROM node_revision nr WHERE nr.status = 1 AND nr.timestamp BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31') 
UNION SELECT DISTINCT o.author_uid 
FROM versioncontrol_operations o 
WHERE o.committer_date BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31'));
-- Number of Blocked Accounts (on this specific date)
SELECT COUNT(*) FROM users WHERE status=0;
-- Number of Participants in the Issue Queues (who commented)
SELECT COUNT(DISTINCT(c.uid))
FROM comment c
INNER JOIN node n ON c.nid = n.nid AND n.status = 1
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE n.type = 'project_issue' AND c.status = 1 
AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Participants in the Drupal Core Issue Queue (who commented)
SELECT COUNT(DISTINCT(c.uid))
FROM comment c
INNER JOIN field_data_field_project fdfp ON fdfp.entity_id = c.nid AND fdfp.field_project_target_id = 3060
INNER JOIN node n ON c.nid = n.nid AND n.type = 'project_issue' AND n.status = 1
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE c.status = 1 
AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Commits across All Projects
SELECT COUNT(DISTINCT vco.revision) AS commits 
FROM versioncontrol_operations vco 
WHERE vco.committer_date BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Commits to Drupal Core
SELECT COUNT(DISTINCT vco.revision) AS commits 
FROM versioncontrol_operations vco 
WHERE vco.repo_id=2 AND vco.committer_date BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Committers (at least 1 commit, all full projects, excluding Core)
SELECT COUNT(DISTINCT vco.committer) AS committers
FROM versioncontrol_operations vco
INNER JOIN versioncontrol_project_projects vp ON vp.repo_id = vco.repo_id AND vp.nid <> 3060
INNER JOIN field_data_field_project_type t ON t.entity_id = vp.nid AND t.field_project_type_value = 'full'
WHERE vco.committer_date BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Commits per User (excluding commits to Drupal Core)
-- (overall commits / # of committers, both numbers excluding Drupal core and sandboxes)
SELECT COUNT(DISTINCT vco.revision) / COUNT(DISTINCT vco.committer) AS commits_per_committer
FROM versioncontrol_operations vco
INNER JOIN versioncontrol_project_projects vp ON vp.repo_id = vco.repo_id AND vp.nid <> 3060
INNER JOIN field_data_field_project_type t ON t.entity_id = vp.nid AND t.field_project_type_value = 'full'
WHERE vco.committer_date BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Comments (created during the time period)
SELECT COUNT(c.cid) comments
FROM comment c
INNER JOIN node n ON c.nid = n.nid AND n.status = 1
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE c.status = 1 AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Comments on Issues (created during the time period)
SELECT COUNT(c.cid) comments 
FROM comment c
INNER JOIN node n ON n.nid = c.nid AND n.type = 'project_issue' AND n.status = 1
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE c.status = 1 AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Comments on Drupal Core Issues (created during the time period)
SELECT COUNT(c.cid) comments 
FROM comment c
INNER JOIN field_data_field_project fdfp ON fdfp.entity_id = c.nid AND fdfp.field_project_target_id = 3060
INNER JOIN node n ON n.nid = c.nid AND n.type = 'project_issue' AND n.status = 1
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE c.status = 1 AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Average Number of Comments per User (created during the time period)
-- (overall comments / # of commenters)
SELECT COUNT(c.cid) / COUNT(DISTINCT c.uid) AS comments_per_user
FROM comment c
INNER JOIN node n ON c.nid = n.nid AND n.status = 1
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE c.status = 1 AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Average Number of Comments per User on Issues 
SELECT COUNT(c.cid) / COUNT(DISTINCT c.uid) AS comments_per_user
FROM comment c
INNER JOIN node n ON c.nid = n.nid AND n.type = 'project_issue' AND n.status = 1
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE c.status = 1 AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Average Number of Comments per User on Drupal Core Issues
SELECT COUNT(c.cid) / COUNT(DISTINCT c.uid) AS comments_per_user
FROM comment c
INNER JOIN field_data_field_project fdfp ON fdfp.entity_id = c.nid AND fdfp.field_project_target_id = 3060
INNER JOIN node n ON c.nid = n.nid AND n.type = 'project_issue' AND n.status = 1
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE c.status = 1 AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Comments on Issues, which Updated the Node
SELECT COUNT(c.cid) comments 
FROM comment c
INNER JOIN node n ON n.nid = c.nid AND n.status = 1
INNER JOIN field_data_field_issue_changes ic ON ic.entity_id = c.cid
INNER JOIN users u ON u.uid = c.uid AND u.status = 1
WHERE c.status = 1 AND c.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Issues (created during the time period)
SELECT COUNT(n.nid)
FROM node n
INNER JOIN users u ON u.uid = n.uid AND u.status = 1
WHERE n.type = 'project_issue' AND n.status = 1 AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Average Number of Issues per User (created during the time period)
SELECT COUNT(n.nid) / COUNT(DISTINCT n.uid) AS issues_per_user
FROM node n
INNER JOIN users u ON u.uid = n.uid AND u.status = 1
WHERE n.type = 'project_issue' AND n.status = 1 AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Projects (created during the time period)
SELECT COUNT(n.nid)
FROM node n
INNER JOIN users u ON u.uid = n.uid AND u.status = 1
INNER JOIN field_data_field_project_type t ON t.entity_id = n.nid
WHERE n.status = 1 AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Sandbox Projects (created during the time period)
SELECT COUNT(n.nid)
FROM node n
INNER JOIN users u ON u.uid = n.uid AND u.status = 1
INNER JOIN field_data_field_project_type t ON t.entity_id = n.nid AND t.field_project_type_value = 'sandbox'
WHERE n.status = 1 AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Number of Full Projects (created during the time period)
SELECT COUNT(n.nid)
FROM node n
INNER JOIN users u ON u.uid = n.uid AND u.status = 1
INNER JOIN field_data_field_project_type t ON t.entity_id = n.nid AND t.field_project_type_value = 'full'
WHERE n.status = 1 AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- % issues about Drupal.org responded to in 48 hours
-- (% of issues, created during the time period, which received first comment, not from the issue author, in less than 48 hours after issue published, in the following queues: Content, Webmasters, Infrastructure, Bluecheese, Drupalorg, Drupalorg_crosssite)
SELECT sum(duration / 60 / 60 <= 48) / count(1) * 100 FROM (SELECT (min(c.created) - n.created) AS duration FROM node n INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id IN (1848824, 3202, 107028, 651778, 185188, 1540220) INNER JOIN comment c ON c.nid = n.nid AND c.uid <> n.uid WHERE n.type = 'project_issue' AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31') GROUP BY n.nid ORDER BY NULL) t;
-- Testbot (queries against qa.d.o)
-- # of test requests sent
SELECT COUNT(test_id) FROM pifr_test WHERE type = 3 AND status = 4 AND last_received BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- # of Drupal core patches tested / Average core test queue time (min) / Average core test duration (min) / Average core total wait time (min)
SELECT COUNT(pt.test_id), AVG((pt.last_requested - pt.last_received)/60) AS avg_queue_time, AVG((pt.last_tested - pt.last_requested)/60) AS avg_test_duration, AVG((pt.last_tested - pt.last_received)/60) AS avg_total_wait FROM pifr_test pt LEFT JOIN pifr_file pf ON pt.test_id = pf.test_id WHERE pt.type = 3 AND pt.status = 4 AND pf.branch_id IN (1,2) AND pt.last_requested != 0 AND last_received BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');
-- Same, D7 only
SELECT COUNT(pt.test_id), AVG((pt.last_requested - pt.last_received)/60) AS avg_queue_time, AVG((pt.last_tested - pt.last_requested)/60) AS avg_test_duration, AVG((pt.last_tested - pt.last_received)/60) AS avg_total_wait FROM pifr_test pt LEFT JOIN pifr_file pf ON pt.test_id = pf.test_id WHERE pt.type = 3 AND pt.status = 4 AND pf.branch_id = 1 AND pt.last_requested != 0 AND last_received BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');

-- Same, D8 only
SELECT COUNT(pt.test_id), AVG((pt.last_requested - pt.last_received)/60) AS avg_queue_time, AVG((pt.last_tested - pt.last_requested)/60) AS avg_test_duration, AVG((pt.last_tested - pt.last_received)/60) AS avg_total_wait FROM pifr_test pt LEFT JOIN pifr_file pf ON pt.test_id = pf.test_id WHERE pt.type = 3 AND pt.status = 4 AND pf.branch_id = 2 AND pt.last_requested != 0 AND last_received BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31');

-- Number of Open Issues per Queue
-- Content
SELECT COUNT(n.nid)
FROM node n
INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 1848824
INNER JOIN field_data_field_issue_status fis ON fis.entity_id = n.nid
WHERE fis.field_issue_status_value IN (1,13,8,14,15,4,16);
-- Webmasters
SELECT COUNT(n.nid)
FROM node n
INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 3202
INNER JOIN field_data_field_issue_status fis ON fis.entity_id = n.nid
WHERE fis.field_issue_status_value IN (1,13,8,14,15,4,16);
-- Infrastructure
SELECT COUNT(n.nid)
FROM node n
INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 107028
INNER JOIN field_data_field_issue_status fis ON fis.entity_id = n.nid
WHERE fis.field_issue_status_value IN (1,13,8,14,15,4,16);
-- Bluecheese
SELECT COUNT(n.nid)
FROM node n
INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 651778
INNER JOIN field_data_field_issue_status fis ON fis.entity_id = n.nid
WHERE fis.field_issue_status_value IN (1,13,8,14,15,4,16);
-- Drupalorg
SELECT COUNT(n.nid)
FROM node n
INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 185188
INNER JOIN field_data_field_issue_status fis ON fis.entity_id = n.nid
WHERE fis.field_issue_status_value IN (1,13,8,14,15,4,16);
-- Drupalorg_crosssite
SELECT COUNT(n.nid)
FROM node n
INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 1540220
INNER JOIN field_data_field_issue_status fis ON fis.entity_id = n.nid
WHERE fis.field_issue_status_value IN (1,13,8,14,15,4,16);
-- G.d.o queue
SELECT COUNT(n.nid)
FROM node n
INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 833750
INNER JOIN field_data_field_issue_status fis ON fis.entity_id = n.nid
WHERE fis.field_issue_status_value IN (1,13,8,14,15,4,16);
-- A.d.o queue
SELECT COUNT(n.nid)
FROM node n
INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 1369118
INNER JOIN field_data_field_issue_status fis ON fis.entity_id = n.nid
WHERE fis.field_issue_status_value IN (1,13,8,14,15,4,16);
-- Average Response Time across All Queues (hours)
-- (avg. time between issue published and 1st comment, not by issue author, created)
SELECT avg(duration) / 60 / 60 FROM (SELECT (min(c.created) - n.created) AS duration FROM node n INNER JOIN comment c ON c.nid = n.nid AND c.uid <> n.uid WHERE n.type = 'project_issue' AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31') GROUP BY n.nid ORDER BY NULL) t;
-- Average Response Time in Drupal Core Issue Queue (hours)
SELECT avg(duration) / 60 / 60 FROM (SELECT (min(c.created) - n.created) AS duration FROM node n INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id = 3060 INNER JOIN comment c ON c.nid = n.nid AND c.uid <> n.uid WHERE n.type = 'project_issue' AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31') GROUP BY n.nid ORDER BY NULL) t;
-- Average Response Time in Drupal.org Issue Queues (hours)
-- (Content, Webmasters, Infrastructure, Bluecheese, Drupalorg, Drupalorg_crosssite)
SELECT avg(duration) / 60 / 60 FROM (SELECT (min(c.created) - n.created) AS duration FROM node n INNER JOIN field_data_field_project fp ON fp.entity_id = n.nid AND fp.field_project_target_id IN (1848824, 3202, 107028, 651778, 185188, 1540220) INNER JOIN comment c ON c.nid = n.nid AND c.uid <> n.uid WHERE n.type = 'project_issue' AND n.created BETWEEN UNIX_TIMESTAMP('2014-01-01') AND UNIX_TIMESTAMP('2014-01-31') GROUP BY n.nid ORDER BY NULL) t;

-- Number of Drupal core downloads
SELECT td.name, sum(rfd.field_release_file_downloads_value)
FROM field_data_field_release_files rf 
INNER JOIN field_data_field_release_project rp ON rp.entity_id = rf.entity_id AND rp.field_release_project_target_id = 3060 
INNER JOIN field_data_taxonomy_vocabulary_6 api ON api.entity_id = rf.entity_id 
INNER JOIN taxonomy_term_data td ON td.tid = api.taxonomy_vocabulary_6_tid 
INNER JOIN field_data_field_release_file_downloads rfd ON rfd.entity_id = rf.field_release_files_value 
GROUP BY api.taxonomy_vocabulary_6_tid;