Analytics and reporting

We have a report on our system that provides that type of data:

Here is the query to get a list of courses and the number of videos they have:

$sql = "SELECT DISTINCT 
 c.id AS course,
 c.shortname,
 COUNT(f.id) AS numvideos,
 SUM(f.filesize) AS size
 FROM {course} c
 JOIN {course_modules} cm ON (cm.course=c.id)
 JOIN {context} ct ON (ct.instanceid=cm.id AND ct.contextlevel=:contextlevel)
 JOIN {files} f ON (f.contextid=ct.id)
 JOIN {modules} m ON (m.id=cm.module)
 WHERE f.mimetype LIKE 'video/%'
 GROUP BY c.id
 ORDER BY numvideos";

Then, for a particular course, I use this query, passing in a courseid, to get the listing of videos.

$sql = "SELECT DISTINCT f.id AS idx,
 f.filename,
 m.name,
 cm.section,
 f.filesize AS size,
 cm.id
 FROM {course} c
 JOIN {course_modules} cm ON (cm.course=c.id)
 JOIN {context} ct ON (ct.instanceid=cm.id AND ct.contextlevel=:contextlevel)
 JOIN {files} f ON (f.contextid=ct.id)
 JOIN {modules} m ON (m.id=cm.module)
 WHERE c.id=:courseid AND
 f.mimetype LIKE 'video/%'
 ORDER BY f.filename";

As for getting the number of views, you can try joining on the mdl_log table, but I don't do that, because it is a very slow query.