# List of all uploaded files

**URL:** <https://meta.discourse.org/t/list-of-all-uploaded-files/63714>\
**Category:** Feature\
**Tags:** planned\
**Created:** [Junho 1, 2017, 7:51am UTC](https://meta.discourse.org/t/list-of-all-uploaded-files/63714 "2017-06-01T07:51:00Z")\
**Posts on this page:** 1\
**Showing post:** 13

<div class="post-metadata">

**Author:** ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon)\
**Post date:** [Janeiro 8, 2020, 11:57pm UTC](https://meta.discourse.org/t/list-of-all-uploaded-files/63714/13 "2020-01-08T23:57:37Z")

</div>

Aqui estão algumas consultas do [Data Explorer](https://meta.discourse.org/t/32566?silent=true) para obter detalhes sobre uploads de posts. Algo semelhante poderia ser adicionado à seção de relatórios administrativos para sites que não têm o plugin [Data Explorer](https://meta.discourse.org/t/32566?silent=true) instalado. Avise-me se esses dados não forem o que você procura.

### Quais usuários têm mais uploads ou uploads maiores?

Retorna o usuário, a quantidade de uploads e o total de uploads em KB, arredondado para duas casas decimais. O join na tabela `users` serve para evitar que dados de usuários excluídos sejam retornados. Os resultados são ordenados por tamanho total de upload em ordem decrescente.

#### Incluindo imagens otimizadas

```sql
WITH uploads_with_optimized AS (
SELECT
ul.user_id,
ROUND((SUM(COALESCE(oi.filesize, 0)) + SUM(ul.filesize)) / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
LEFT JOIN optimized_images oi
ON ul.id = oi.upload_id
GROUP BY ul.user_id
)

SELECT
uwo.user_id,
COUNT(uploads.user_id) AS upload_count,
total_kb
FROM uploads_with_optimized uwo
JOIN uploads
ON uploads.user_id = uwo.user_id
GROUP BY uploads.user_id, uwo.user_id, total_kb
ORDER BY total_kb DESC
LIMIT 50

```

#### Excluindo imagens otimizadas

```sql
SELECT
ul.user_id,
COUNT(ul.user_id) AS upload_count,
ROUND(SUM(ul.filesize) / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
GROUP BY ul.user_id
ORDER BY total_kb DESC
LIMIT 50

```

### Quais são os 100 maiores uploads do meu fórum?

Retorna o usuário, o post com o upload e o tamanho do arquivo do upload em KB. Os resultados são ordenados por tamanho de upload em ordem decrescente.

#### Incluindo imagens otimizadas

```sql
SELECT
ul.user_id,
pul.post_id,
ROUND((SUM(oi.filesize) + ul.filesize) / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
JOIN optimized_images oi
ON ul.id = oi.upload_id
GROUP BY oi.upload_id, ul.user_id, pul.post_id, ul.filesize
ORDER BY total_kb DESC
LIMIT 100

```

#### Excluindo imagens otimizadas

```sql
SELECT
ul.user_id,
pul.post_id,
ROUND(ul.filesize / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
ORDER BY total_kb DESC
LIMIT 100

```

### Uploads dos últimos 30 dias

A consulta exige que você forneça um parâmetro `:end_date`. A data deve estar no formato ‘aaaa-mm-dd’. Por exemplo, ‘2020-01-08’. Ela retorna resultados para o período de 30 dias que _termina_ com o `end_date`. Retorna o dia, a quantidade de uploads e o total de uploads do dia em KB para todos os dias do período que possuem uploads de posts. Os resultados são ordenados por dia.

#### Incluindo imagens otimizadas

```sql
--[params]
-- date :end_date

SELECT
ul.created_at::date AS day,
COUNT(1) AS upload_count,
ROUND((SUM(COALESCE(oi.filesize, 0)) + SUM(ul.filesize)) / 1000.0, 2) AS total_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
LEFT JOIN optimized_images oi
ON ul.id = oi.upload_id
WHERE ul.created_at::date BETWEEN :end_date::date - INTERVAL '30 days' AND :end_date
GROUP BY ul.created_at::date
ORDER BY ul.created_at::date DESC

```

#### Excluindo imagens otimizadas

```sql
--[params]
-- date :end_date

SELECT
ul.created_at::date AS day,
COUNT(1) AS upload_count,
ROUND(SUM(ul.filesize) / 1000.0, 2) AS daily_upload_kb
FROM post_uploads pul
JOIN uploads ul
ON ul.id = pul.upload_id
WHERE ul.created_at::date BETWEEN :end_date::date - INTERVAL '30 days' AND :end_date
GROUP BY ul.created_at::date
ORDER BY ul.created_at::date DESC

```

---

_[View the full topic](https://meta.discourse.org/t/list-of-all-uploaded-files/63714)._