Necesito crear esta consulta SQL para Data-Explorer:
Necesito hacer la solicitud por año. Como entrada… por ejemplo, del 2019-01-01 al 2019-12-31
Número de respuestas por categoría (TEMA y PUBLICACIÓN)
Etiquetas principales en un año
Promedio de nuevos temas por mes (encontré esto en el panel de administración del informe de administrador)
Promedio de respuestas por mes
Promedio de nuevos usuarios por mes
¿Alguien puede echarme una mano con esto??? O darme alguna dirección
Saludos.
@michebs probablemente pueda ayudarte aquí.
Hola,
Permíteme confirmar si esto es lo que necesitas antes de configurar otras consultas:
WITH data AS (SELECT
id,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics)
SELECT
ROUND(AVG(qt_topic_month)) AS avg_topic,
year
FROM
(SELECT COUNT(id) AS qt_topic_month,
month,
year
FROM data
GROUP BY month, year) AS top_m
GROUP BY year
ORDER BY year DESC
| avg_topic | year |
|---|---|
| 694 | 2020 |
| 1011 | 2019 |
| 284 | 2018 |
| 79 | 2017 |
@michebs.. Sí, exactamente, lo captaste muy rápido
Si no es mucha molestia, sería algo así:
2020 01 número
2020 02 número
2020 03 número
etc…
Y gracias de antemano por esto.
Saludos.
No hay problema, creo que esta es la información que buscas:
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics)
SELECT
year,
month,
ROUND(AVG(qt_topic_day)) AS avg_topic
FROM
(SELECT COUNT(id) AS qt_topic_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC
| year | month | avg_topic |
|---|---|---|
| 2020 | 1 | 23 |
| 2020 | 2 | 26 |
| 2020 | 3 | 24 |
| 2020 | 4 | 35 |
| 2020 | 5 | 31 |
Configuraré otras consultas basadas en este paradigma. 
Hola,
Disculpa la demora en responder. A continuación, te envío las consultas solicitadas; dado que algunas son más complejas de lo habitual, quedo a tu disposición por si tienes alguna duda.
Michelle
WITH post AS (SELECT
id AS post_id,
topic_id,
EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
AND deleted_at IS NULL
AND post_number != 1)
SELECT
p.year,
t.category_id AS id,
c.name AS category,
COUNT(p.post_id) AS qt
FROM post p
INNER JOIN topics t ON t.id = p.topic_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
-- AND t.category_id IS NOT NULL --> *** Habilitar para eliminar publicaciones sin categoría ***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
| year | id | category | qt |
|---|---|---|---|
| 2020 | 13 | General | 14 |
| 2020 | 16 | Base de conocimientos | 3 |
| 2020 | 15 | Personal | 3 |
| 2020 | 1 | Sin categoría | 2 |
| 2020 | 17 | Ideas | 1 |
| 2019 | 18 | Construcciones | 10 |
| 2019 | 1 | Sin categoría | 8 |
| 2019 | 11 | CS001x: Introducción a la informática | 7 |
| 2019 | 13 | General | 5 |
WITH data AS (SELECT
tag_id,
EXTRACT(YEAR FROM created_at) AS year
FROM topic_tags)
SELECT year, rank, name, qt FROM (
SELECT
tag_id,
COUNT(tag_id) AS qt,
year,
rank() OVER (PARTITION BY year ORDER BY COUNT(tag_id) DESC) AS rank
FROM
data
GROUP BY year, tag_id) as rnk
INNER JOIN tags ON tags.id = rnk.tag_id
WHERE rank <= 5 -- *** Elegir el límite de clasificación ***
ORDER BY year DESC, qt DESC
| year | rank | tag_name | quantity |
|---|---|---|---|
| 2020 | 1 | destacado | 7 |
| 2020 | 2 | recursos-humanos | 3 |
| 2020 | 3 | demo | 1 |
| 2019 | 1 | demo | 12 |
| 2019 | 2 | recursos-humanos | 4 |
| 2019 | 3 | destacado | 3 |
| 2019 | 3 | cliente | 3 |
| 2019 | 3 | hitos-2019 | 3 |
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM topics
WHERE deleted_at IS NULL)
SELECT
year,
month,
ROUND(AVG(qt_topic_day)) AS avg_topic_by_day
FROM
(SELECT COUNT(id) AS qt_topic_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC
| year | month | avg_topic_by_day |
|---|---|---|
| 2020 | 1 | 1 |
| 2020 | 2 | 1 |
| 2020 | 3 | 2 |
| 2020 | 4 | 3 |
| 2020 | 5 | 2 |
| 2019 | 4 | 9 |
| 2019 | 5 | 4 |
| 2019 | 6 | 4 |
| 2019 | 7 | 1 |
| 2019 | 8 | 2 |
| 2019 | 9 | 3 |
| 2019 | 10 | 1 |
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
AND deleted_at IS NULL
AND post_number != 1)
SELECT
year,
month,
ROUND(AVG(qt_reply_day)) AS avg_reply_by_day
FROM
(SELECT COUNT(id) AS qt_reply_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_reply
GROUP BY month, year
ORDER BY year DESC, month ASC
| year | month | avg_reply_by_day |
|---|---|---|
| 2020 | 1 | 7 |
| 2020 | 3 | 2 |
| 2020 | 4 | 5 |
| 2020 | 5 | 6 |
| 2019 | 4 | 3 |
| 2019 | 5 | 2 |
| 2019 | 6 | 4 |
| 2019 | 7 | 2 |
| 2019 | 8 | 15 |
| 2019 | 9 | 3 |
| 2019 | 10 | 5 |
| 2019 | 12 | 2 |
WITH data AS (SELECT
id,
EXTRACT(DAY FROM created_at) AS day,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(YEAR FROM created_at) AS year
FROM users)
SELECT
year,
month,
ROUND(AVG(qt_new_user)) AS avg_new_user_by_day
FROM
(SELECT COUNT(id) AS qt_new_user,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_new_user
GROUP BY month, year
ORDER BY year DESC, month ASC
| year | month | avg_new_user_by_day |
|---|---|---|
| 2020 | 1 | 1 |
| 2020 | 2 | 1 |
| 2020 | 3 | 1 |
| 2020 | 4 | 3 |
| 2020 | 5 | 1 |
| 2019 | 4 | 4 |
| 2019 | 5 | 2 |
| 2019 | 6 | 2 |
| 2019 | 7 | 1 |
funciona como un hechizo…
me has salvado el día…
Saludos…