Sto cercando di fornire al nostro team statistiche sul numero di utenti attivi attuali, con la seguente definizione di “Utente Attivo”:
Utenti che hanno letto mentre erano connessi oppure hanno pubblicato sul nostro forum nell’ultimo anno
Abbiamo molti utenti in modalità “Lista di distribuzione” che rispondono via email, quindi ne perderemmo molti se raccogliessimo semplicemente il numero di lettori come in questa query:
Posso anche ottenere il numero di chi ha pubblicato qui:
Sono solo troppo limitato per combinarli con un OR in modo da includere entrambi i gruppi. Puoi aiutarmi?
Questo dovrebbe restituirti tutti gli utenti che hanno letto qualcosa mentre erano connessi o che hanno pubblicato nell’ultimo anno. L’esploratore di dati mostrerà anche il numero totale di tali utenti.
SELECT p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL '365 days'
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.closed = FALSE
AND t.archived = FALSE
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
UNION
SELECT u.user_id
FROM user_visits u
WHERE u.posts_read > 0
AND u.visited_at > CURRENT_TIMESTAMP - INTERVAL '365 days'
ORDER BY user_id
Probabilmente esiste un modo più efficiente per farlo, ma questo funziona . Se desideri un periodo specifico, puoi sostituire > CURRENT_TIMESTAMP - INTERVAL '365 days' (in entrambi i casi) con qualcosa del genere: BETWEEN '20200101'::date AND '20210101'::date.
Hai fornito ottime informazioni e riferimenti nella tua domanda! Ho dovuto solo selezionare gli elementi giusti e combinarli.
Alla fine ho optato per qualcosa di leggermente diverso, poiché volevo più informazioni e UNION si è rivelato un po’ restrittivo. Volevo anche suddividere i dati per gruppi. Ho raggiunto il successo (credo) modificando la query inclusa per gli utenti attivi con un’altra query che ho per estrarre informazioni specifiche del gruppo:
-- [params]
-- int :number_of_days = 365
-- string :group_name = trust_level_0
With included_users AS (
SELECT
gu.user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
),
posts_by_user AS (
SELECT COUNT(*) AS posts, p.user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.closed = FALSE
AND t.archived = FALSE
AND t.archetype = 'regular'
AND p.deleted_at IS NULL
GROUP BY p.user_id
),
posts_read_by_user AS (
SELECT SUM(posts_read) AS posts_read, uv.user_id
FROM user_visits uv
WHERE uv.posts_read > 0
AND uv.visited_at > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day
GROUP BY uv.user_id
)
SELECT
u.id AS "user_id",
u.username_lower AS "username",
u.last_seen_at,
COALESCE(pbu.posts, 0) AS "posts_created",
COALESCE(prbu.posts_read, 0) AS "posts_read"
FROM users u
LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id
LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id
WHERE u.active = true
AND u.id > 0
AND u.id IN (SELECT user_id FROM included_users)
AND (COALESCE(pbu.posts, 0) > 0 OR COALESCE(prbu.posts_read, 0) > 0)
ORDER BY u.id
Le query per i badge sono piuttosto complicate e richiedono solo ‘user_id’ e ‘granted_at’ per essere restituite. Quindi dovrai fare un po’ di hacking.
Ti consiglio di approfondire gli argomenti relativi alle query per i badge (leggili attentamente) e di fare un tentativo. Ti consiglio anche di eseguirla solo una volta al giorno, altrimenti potrebbe essere un po’ pesante.
. Se desideri un periodo specifico, puoi sostituire