Estoy buscando proporcionar a nuestro equipo estadísticas sobre la cantidad de usuarios activos actuales que tenemos, con la siguiente definición de ‘Usuario Activo’:
Usuarios que hayan leído mientras iniciaron sesión o que hayan publicado en nuestro foro durante el último año.
Tenemos muchos usuarios en el modo de lista de correo que responden por correo electrónico, por lo que perderemos a muchos si simplemente recopilamos el número de lectores según esta consulta:
También puedo obtener el número de quienes han publicado aquí:
Simplemente soy demasiado tonto para combinarlos con un OR para que capturemos ambos grupos. ¿Puedes ayudarme?
Esto debería devolverte todos los usuarios que hayan leído algo mientras iniciaron sesión o que hayan publicado en el último año. El explorador de datos también mostrará cuántos son.
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
Probablemente exista una forma más eficiente de hacerlo, pero funciona . Si deseas un período específico, podrías cambiar > CURRENT_TIMESTAMP - INTERVAL '365 days' (ambas ocurrencias) por algo como esto: BETWEEN '20200101'::date AND '20210101'::date.
¡Proporcionaste información y referencias excelentes en tu pregunta! Solo tuve que seleccionar lo adecuado y combinarlo.
Al final, terminé haciendo algo un poco diferente, ya que necesitaba más información y UNION resultó ser un poco restrictivo. También quería desglosarlo por grupos. Creo que logré el éxito “hackeando” la consulta de Lurkers Activos incluida con otra que tengo para extraer información específica de grupos:
-- [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
Las consultas de insignias son bastante complejas y solo requieren que se devuelvan un ‘user_id’ y ‘granted_at’. Así que necesitarás hackear esto un poco.
Te recomiendo que investigues los temas sobre consultas de insignias (léelos atentamente) y que lo intentes. También te recomiendo que solo lo ejecutes una vez al día, ya que de lo contrario podría ser un poco pesado.
. Si deseas un período específico, podrías cambiar