Aqui estĂŁo algumas consultas do Data Explorer 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 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
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
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
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
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
--[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
--[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