Dear Moodlers,

I would like to find a way to retrieve the below statistics (knowing that I have consult the ad-hoc webpage and i didn't find anything related to what I need)

1. A query that will retrieve the total number of created activities (per course and per activity) during a certain period in the past (for example between January and February 2018) the exact state of Moodle at that time.

2. A query that will retrieve the total number of resources (per course) during a certain period in the past (for example between January and February 2018) the exact state of Moodle at that time.

I managed to execute this query but for the current stat of Moodle, which means for example in query 1, if I run the query right now to retrieve the data between January and February 2018 it will be different if I run the same query but at that time, because a lot of activities will be deleted

Below are the queries 

Thank you

=====================================================================

Query 1: total number of activities per course per activity

(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____forum') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_forum AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____assignment') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_assign AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____quiz') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_quiz AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____workshop') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_workshop AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____turnitintooltwo') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_turnitintooltwo AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____survey') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_survey AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____glossary') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_glossary AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____feedback') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_feedback AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____data') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_data AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____choice') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_choice AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____chat') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_chat AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____attendance') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_attendance AS act
INNER JOIN prefix_course AS c ON act.course=c.id)
UNION
(SELECT c.fullname AS "Course fullname", c.idnumber AS "Course idnumber", (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '____lesson') AS ThisType, to_timestamp(act.timemodified) as DateModified, act.name AS Title
FROM prefix_lesson AS act
INNER JOIN prefix_course AS c ON act.course=c.id)

Query 2: total number of resources per course

SELECT f.contextid, c.idnumber AS "Course idnumber", c.fullname AS "Course fullname", f.filesize/1024 AS "filesize in KB", f.filename AS "filename", f.mimetype AS "filetype", to_timestamp(f.timecreated) AS "timecreated", to_timestamp(f.timemodified) AS "timemodified"
FROM prefix_files f
INNER JOIN prefix_context ctx ON f.contextid=ctx.id
INNER JOIN prefix_course_modules cm ON ctx.instanceid=cm.id
INNER JOIN prefix_course c ON cm.course=c.id
WHERE (f.component='mod_folder' OR f.component='mod_resource') AND f.filesize>0