Drill down to see disk usage by module and/or user

[Fragonite]

A client wanted a breakdown of where files are being stored and more details on the "other" mimetype. The below queries were used to help answer these questions.

I think this type of information is worth adding to the plugin.

Modules by data usage (all mimetypes):

SELECT 
    c.id AS course_id,
    c.fullname AS course_name,
    cm.id AS module_id,
    m.name AS module_name,
    CONCAT('<a href="/mod/', m.name, '/view.php?id=', cm.id, '">Module Link</a>') AS module_link,
    COUNT(f.id) AS file_count,
    ROUND(SUM(f.filesize) / 1024 / 1024 / 1024, 2) AS total_storage_gb
FROM 
    (SELECT DISTINCT ON (contenthash) * FROM {files}) f
JOIN 
    {context} ctx ON f.contextid = ctx.id
JOIN 
    {course_modules} cm ON ctx.instanceid = cm.id
JOIN 
    {modules} m ON cm.module = m.id
JOIN 
    {course} c ON cm.course = c.id
WHERE
    ctx.contextlevel = 70
GROUP BY 
    c.id, c.fullname, cm.id, m.name
ORDER BY 
    total_storage_gb DESC

Modules by data usage (video):

SELECT 
    c.id AS course_id,
    c.fullname AS course_name,
    cm.id AS module_id,
    m.name AS module_name,
    CONCAT('<a href="/mod/', m.name, '/view.php?id=', cm.id, '">Module Link</a>') AS module_link,
    COUNT(f.id) AS video_file_count,
    ROUND(SUM(f.filesize) / 1024 / 1024 / 1024, 2) AS total_storage_gb
FROM 
    (SELECT DISTINCT ON (contenthash) * FROM {files}) f
JOIN 
    {context} ctx ON f.contextid = ctx.id
JOIN 
    {course_modules} cm ON ctx.instanceid = cm.id
JOIN 
    {modules} m ON cm.module = m.id
JOIN 
    {course} c ON cm.course = c.id
WHERE 
    f.mimetype LIKE 'video/%'
    AND ctx.contextlevel = 70
GROUP BY 
    c.id, c.fullname, cm.id, m.name
ORDER BY 
    total_storage_gb DESC

Users by data usage (all mimetypes):

SELECT 
    u.id AS user_id,
    u.username,
    CONCAT('<a href="/user/profile.php?id=', u.id, '">Profile Link</a>') AS user_profile_link,
    COUNT(f.id) AS file_count,
    ROUND(SUM(f.filesize) / 1024 / 1024 / 1024, 2) AS total_storage_gb
FROM 
    (SELECT DISTINCT ON (contenthash) * FROM {files}) f
JOIN 
    {user} u ON f.userid = u.id
GROUP BY 
    u.id, u.username
ORDER BY 
    total_storage_gb DESC

Users by data usage (video):

SELECT 
    u.id AS user_id,
    u.username,
    CONCAT('<a href="/user/profile.php?id=', u.id, '">Profile Link</a>') AS user_profile_link,
    COUNT(f.id) AS video_file_count,
    ROUND(SUM(f.filesize) / 1024 / 1024 / 1024, 2) AS total_storage_gb
FROM 
    (SELECT DISTINCT ON (contenthash) * FROM {files} WHERE mimetype LIKE 'video/%') f
JOIN 
    {user} u ON f.userid = u.id
GROUP BY 
    u.id, u.username
ORDER BY 
    total_storage_gb DESC

Data usage by mimetypes:

SELECT 
    f.mimetype,
    COUNT(f.id) AS file_count,
    ROUND(SUM(f.filesize) / 1024 / 1024 / 1024, 2) AS total_storage_gb
FROM 
    (SELECT DISTINCT ON (contenthash) * FROM {files}) f
GROUP BY 
    f.mimetype
ORDER BY 
    total_storage_gb DESC