Acho que as entradas que você listou não correspondem à saída que você espera obter. Se você deseja obter dados de um único usuário, algo como a consulta abaixo pode funcionar como ponto de partida. Ela está usando Expressões de Tabela Comum (CTE) para tornar a consulta mais fácil de ler e escrever. O mesmo padrão pode ser seguido para adicionar mais dados aos resultados.
Uma coisa sobre a qual não tinha certeza é se você quer a soma dos dados para um determinado dia ou o número de ações que ocorreram em cada dia. Por exemplo, você quer saber que em 2019-10-31 um usuário criou um total de 20 posts em todos os dias em que esteve no site, ou você quer saber que em 2019-10-31 o usuário criou exatamente 3 posts? Como a consulta está estruturada atualmente, ela está retornando o último resultado.
--[params]
-- string :username
-- date :start_date
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, NOW()::date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = 'regular'
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts,
SUM(array_length(regexp_split_to_array(raw, '\s'), 1)) AS word_count
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND p.deleted_at IS NULL
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(word_count, 0) AS word_count,
COALESCE(likes_received_count, 0) AS likes_received
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
ORDER BY d.day
Para aumentar a atividade dos usuários, planejamos realizar um desafio para identificar o usuário mais ativo em nosso fórum, que será premiado.
Por isso, gostaríamos de obter alguns dados adicionais para tornar o desafio ainda mais interessante.
Os dados que temos, usando a consulta de @simon, são: Tópicos, Postagens, Contagem de Palavras e Curtidas Recebidas
Alguns dados adicionais incluem:
Curtidas Dadas: Quantas curtidas foram concedidas
Visitas: Quantas vezes o usuário abriu o fórum
Tempo de Leitura: Quanto tempo o usuário permaneceu no fórum
Seguidos e Seguidores
Visualizações de Tópicos: Quantas visualizações os tópicos criados pelo usuário receberam
Respostas nos Tópicos: Quantas respostas (postagens) os tópicos criados pelo usuário receberam
Por favor, ajudem-nos a obter esses dados adicionais.
Para a ponderação de cada item, planejamos usar o Excel. Há alguma sugestão sobre como ponderar cada item para escolher o vencedor?
Queremos tornar este desafio sério, mas divertido. Os melhores usuários são a combinação dos mais ativos, mais úteis e mais populares.
Mais uma vez, muito obrigado pela ajuda.
Observação: Podemos obter os dados em grupos, de modo que precisemos inserir apenas o ID do grupo? Nosso plano é colocar todos os participantes em um grupo. Desta vez, temos que inserir o ID de cada participante individualmente.
Aqui é outro manchesteriano
Minha empresa está pensando em realizar um concurso semelhante internamente para aumentar o engajamento da nossa comunidade com os colaboradores.
Você conseguiu realizar o concurso? Como você acabou extraindo os dados? E como você ponderou as diferentes métricas de engajamento no final?
