Estou procurando fornecer à minha equipe estatísticas sobre quantos usuários ativos temos atualmente, com a seguinte definição de “Usuário Ativo”:
Usuários que leram enquanto estavam logados ou publicaram em nosso fórum no último ano.
Temos muitos usuários no Modo de Lista de Correio que respondem por e-mail, então perderemos muitos se simplesmente coletarmos o número de leitores conforme esta consulta:
Também consigo obter o número de quem publicou aqui:
Sou apenas muito burro para combiná-los com um OR para que capturemos ambos os grupos. Você pode ajudar?
Isso deve retornar todos os usuários que leram algum conteúdo enquanto estavam logados ou publicaram algo no último ano. O explorador de dados também exibirá a quantidade total.
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
Provavelmente existe uma maneira mais eficiente de fazer isso, mas funciona . Se você quiser um período específico, pode alterar > CURRENT_TIMESTAMP - INTERVAL '365 days' (ambas as ocorrências) para algo como: BETWEEN '20200101'::date AND '20210101'::date.
Você forneceu ótimas informações e referências na sua pergunta! Só precisei selecionar o que era relevante e combiná-las.
Obrigado! Acredito em facilitar a ajuda dos outros sempre que possível.
UNION - esse era o ponto fraco no meu conhecimento de SQL. Tudo resolvido agora, obrigado! Vou postar minha consulta final aqui depois de refiná-la mais.
Acabei fazendo algo um pouco diferente, pois queria mais informações e o UNION mostrou-se um pouco restritivo. Também queria dividir por grupos. Consegui ter sucesso (acho que sim) ao adaptar a consulta incluída de Lurkers Ativos com outra que tenho para extrair informações específicas de grupos:
-- [params]
-- int :number_of_days = 365
-- string :group_name = trust_level_0
Com 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
As consultas de distintivos são bem complicadas - e exigem apenas um ‘user_id’ e ‘granted_at’ para serem retornadas. Então você precisará hackear isso um pouco.
Eu recomendo pesquisar os tópicos sobre consultas de distintivos (leia-os com atenção) e tentar. Eu também só executaria uma vez por dia, pois pode ser um pouco pesado caso contrário.
. Se você quiser um período específico, pode alterar