متوسط عدد التعديلات في اليوم حسب الشهر لآخر سنه

SQL

/*
SELECT COUNT(rev_id) AS edit_count, DATE(rev_timestamp) AS day
FROM revision
WHERE rev_actor IN (SELECT actor_id FROM actor WHERE actor_name LIKE 'LokasBot')
GROUP BY DATE(rev_timestamp);
*/
/*
SELECT COUNT(rev_id) / COUNT(DISTINCT DATE(rev_timestamp)) AS avg_edit_per_day
FROM revision
WHERE rev_actor IN (SELECT actor_id FROM actor WHERE actor_name LIKE 'LokasBot')
  #AND YEAR(rev_timestamp) = 2023;
    AND YEAR(rev_timestamp) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
  AND MONTH(rev_timestamp) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH);
*/

SELECT YEAR(rev_timestamp) AS year, MONTH(rev_timestamp) AS month, AVG(edit_count) AS avg_edit_count
FROM (
  SELECT COUNT(rev_id) AS edit_count, rev_timestamp
  FROM revision
  WHERE rev_actor IN (SELECT actor_id FROM actor WHERE actor_name LIKE 'JarBot')
  AND YEAR(rev_timestamp) >= YEAR(CURRENT_DATE - INTERVAL 3 YEAR)
  GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp), DAY(rev_timestamp)
) AS t
GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp)
order by YEAR(rev_timestamp), MONTH(rev_timestamp);
SQL

Checking query status...
