Member Uploads

[Mittineague]

Member Uploads

I put together this query to help find members that post a lot of uploads that potentially might lead to a problem.

User Upload Reporting & Quota

We had a recent need to find where our uploads size was increasing quickly, so this feature request breaks down into two ideas: (1) A way to see per user the file upload capacity used, i.e. a per user total uploads in mb. (2) A trust-based or per-user upload quota limit that would either warn staff/admin that a quota was exceeded or prevent further uploading until the quota was increased. Our support experience I am basing this request for feature discussion is here:

Ordered by total upload weight per member

WITH heavy_uploads AS ( SELECT 
    ( SUM(uploads.filesize) / 1024) AS sum_kb
  , COUNT(uploads.filesize) AS upload_count
  , ( (SUM(uploads.filesize) / COUNT(uploads.filesize)) / 1024) AS avg_weight_kb
  , uploads.user_id 
  FROM uploads
  GROUP BY uploads.user_id
)
SELECT heavy_uploads.sum_kb
 , heavy_uploads.upload_count
 , heavy_uploads.avg_weight_kb
 , heavy_uploads.user_id
FROM heavy_uploads 
WHERE heavy_uploads.sum_kb > 100
ORDER BY heavy_uploads.sum_kb DESC

(the three Grimm members uploaded animated woge gifs)