# Member Uploads

**URL:** https://meta.discourse.org/t/member-uploads/275001
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [June 20, 2016, 6:14pm UTC](https://meta.discourse.org/t/member-uploads/275001 "2016-06-20T18:14:23Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [June 20, 2016, 6:14pm UTC](https://meta.discourse.org/t/member-uploads/275001/1 "2016-06-20T18:14:23Z")

</div>

## 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](https://meta.discourse.org/t/user-upload-reporting-quota/45976):
>
> 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

```plaintext
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

```

 ![](https://global.discourse-cdn.com/meta/original/3X/e/7/e7d0539e3c870e1a344fa4b94089bfaff21faa8a.png)

(the three Grimm members uploaded animated woge gifs)