[meta] Add more metrics (#2193959) · Issues · project / drupalorg

[meta] Add more metrics

>>> [!note] Migrated issue   Reported by: [drumm](https://www.drupal.org/user/3064) >>> 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 <a href="https://drupal.org/metrics">https://drupal.org/metrics</a>. <ul> <li>Number of Active Accounts (logged in 1x per period of time)</li> <li>Number of Active Accounts (at least 1 activity per period of time)</li> <li>Number of Blocked Accounts (on this specific date)</li> <li>Number of Participants in the Issue Queues (who commented)</li> <li>Number of Participants in the Drupal Core Issue Queue (who commented)</li> <li>Number of Commits across All Projects</li> <li>Number of Commits to Drupal Core</li> <li>Number of Committers (at least 1 commit, all full projects, excluding Core)</li> <li>Number of Commits per User (excluding commits to Drupal Core)</li> <li>(overall commits / # of committers, both numbers excluding Drupal core and sandboxes)</li> <li>Number of Comments (created during the time period)</li> <li>Number of Comments on Issues (created during the time period)</li> <li>Number of Comments on Drupal Core Issues (created during the time period)</li> <li>Average Number of Comments per User (created during the time period)</li> <li>(overall comments / # of commenters)</li> <li>Average Number of Comments per User on Issues</li> <li>Average Number of Comments per User on Drupal Core Issues</li> <li>Number of Comments on Issues, which Updated the Node</li> <li>Number of Issues (created during the time period)</li> <li>Average Number of Issues per User (created during the time period)</li> <li>Number of Projects (created during the time period)</li> <li>Number of Sandbox Projects (created during the time period)</li> <li>Number of Full Projects (created during the time period)</li> <li>% issues about Drupal.org responded to in 48 hours</li> <li>(% of issues, created during the time period, which received first comment, not from the issue author, in less than</li> <li>Testbot (queries against qa.d.o)</li> <li># of test requests sent</li> <li># of Drupal core patches tested / Average core test queue time (min) / Average core test duration (min) / Average</li> <li>Same, D7 only</li> <li>Same, D8 only</li> <li>Number of Open Issues per Queue</li> <li>Content </li> <li>Webmasters</li> <li>Infrastructure</li> <li>Bluecheese ??</li> <li>Drupalorg nodes</li> <li>Drupalorg_crosssite nodes</li> <li>G.d.o queue</li> <li>A.d.o queue</li> <li>Average Response Time across All Queues (hours)</li> <li>(avg. time between issue published and 1st comment, not by issue author, created)</li> <li>Average Response Time in Drupal Core Issue Queue (hours)</li> <li>Average Response Time in Drupal.org Issue Queues (hours)</li> <li>(Content, Webmasters, Infrastructure, Bluecheese, Drupalorg, Drupalorg_crosssite)</li> <li>Number of Drupal core downloads</li> </ul> <pre>-- 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;</pre> > Related issue: [Issue #2231415](https://www.drupal.org/node/2231415)

issue