Devo creare questa query SQL per Data-Explorer:
Devo effettuare la richiesta per anno. Come input… ad esempio dal 2019-01-01 al 2019-12-31
Numero di risposte per categoria (TOPIC e POST)
I tag più popolari in un anno
Numero medio di nuovi topic al mese (l'ho trovato nel pannello di amministrazione del rapporto amministrativo)
Numero medio di risposte al mese
Numero medio di nuovi utenti al mese
Qualcuno può darmi una mano con questo??? O darmi qualche indicazione
Cordiali saluti.
@michebs può probabilmente aiutarti qui.
Ciao,
Prima di impostare altre query, confermatemi se è questo che vi serve:
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ì, esattamente, l’hai colto molto in fretta
se non è troppo chiedere, sarebbe qualcosa del genere
2020 01 numero
2020 02 numero
2020 03 numero
ecc…
Grazie in anticipo per questo
Cordiali saluti.
Nessun problema, credo che queste siano le informazioni che stai cercando:
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 |
Imposterò altre query basate su questo paradigma. 
Ciao,
Scusa per il ritardo nella risposta. Di seguito le query richieste; poiché alcune sono più complesse del solito, resto a tua disposizione per eventuali chiarimenti.
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 --> *** Abilita per eliminare post senza categoria ***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
| year | id | category | qt |
|---|---|---|---|
| 2020 | 13 | Generale | 14 |
| 2020 | 16 | Knowledge Base | 3 |
| 2020 | 15 | Staff | 3 |
| 2020 | 1 | Non categorizzato | 2 |
| 2020 | 17 | Idee | 1 |
| 2019 | 18 | Builds | 10 |
| 2019 | 1 | Non categorizzato | 8 |
| 2019 | 11 | CS001x: Introduzione all’informatica | 7 |
| 2019 | 13 | Generale | 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 -- *** Scegli il limite di classificazione ***
ORDER BY year DESC, qt DESC
| year | rank | tag_name | quantity |
|---|---|---|---|
| 2020 | 1 | featured | 7 |
| 2020 | 2 | human-resources | 3 |
| 2020 | 3 | demo | 1 |
| 2019 | 1 | demo | 12 |
| 2019 | 2 | human-resources | 4 |
| 2019 | 3 | featured | 3 |
| 2019 | 3 | customer | 3 |
| 2019 | 3 | milestones-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 |
Funziona alla perfezione…
Mi hai salvato la giornata…
Cordiali saluti…