Course file system space usage query

The following query provides a summary of the file space usage (in bytes) and number of files used by each course in the system from the mdl_files meta table for the data directory. This is just for the space used in the data directory (not the database).

select 
    f.contextid,
    x.instanceid, 
    c.fullname as course_full_name,
    c.shortname as course_short_name,
    sum(f.filesize) as size_in_bytes,
    sum(case when (f.filesize > 0) then 1 else 0 end) as number_of_files
from
    mdl_files f inner join mdl_context x
    on f.contextid = x.id
    and x.contextlevel = 50
    inner join mdl_course c
    on c.id = x.instanceid
group by 
    f.contextid, x.instanceid
order by 
    sum(filesize) desc
;

It may need some refinement. Context level 50 refers to courses.