Boa tarde,
Gostaria de consultar usuários específicos sobre seus curtidas, curtidas recebidas, posts e soluções, em um intervalo de datas específico, dividido por dias. Alguma sugestão sobre esse tipo de relatório? Suspeito que precisarei utilizar o plugin Data Explorer; alguém tem uma consulta pronta para esse tipo de trabalho? Qualquer feedback é bem-vindo!
Eu tinha uma consulta muito semelhante à que você está procurando no meu site de desenvolvimento local. Fiz algumas modificações para atender aos seus requisitos. Uma coisa que tentei fazer foi permitir que a consulta fosse executada contra qualquer atividade que tenha ocorrido em tópicos regulares ou em atividades que tenham ocorrido em MPs. Parte da minha motivação para fazer isso foi que eu queria analisar minha própria atividade ao longo de um período. Poder consultar por tópicos regulares ou MPs faz sentido para o meu caso. A forma como as soluções são tratadas pela consulta quando a caixa de seleção personal_messages está marcada não é ideal — a coluna solutions_provided retornará apenas NULL para esse caso. Possivelmente, a alternância entre tópicos regulares e MPs deveria ser removida da consulta.
Essa consulta não é excessivamente complexa, mas é bastante longa. Se você notar algum problema com seus resultados, por favor, me avise.
A consulta exige que você forneça três parâmetros antes de poder ser executada: username, start_date e end_date. Os parâmetros de data devem estar no formato ‘aaaa-mm-dd’. Por exemplo, 2020-03-13. O parâmetro personal_messages tem como padrão false. Marque a caixa de seleção personal_messages da consulta se quiser ver resultados para atividades que ocorreram em MPs.
--[params]
-- string :username
-- date :start_date
-- date :end_date
-- boolean :personal_messages = false
WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, :end_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 = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN topics t
ON t.id = p.topic_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
AND p.deleted_at IS NULL
AND p.post_number > 1
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 topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
likes_given AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_given_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
solutions_provided AS (
SELECT tu.id,
day,
COUNT(tu.id) AS solutions_provided_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 = 15
GROUP BY day, tu.id
)
SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(likes_received_count, 0) AS likes_received,
COALESCE(likes_given_count, 0) AS likes_given,
(SELECT CASE WHEN NOT :personal_messages THEN COALESCE(solutions_provided_count, 0) END) AS solutions_provided
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
LEFT JOIN likes_given lg
ON lg.day = d.day
LEFT JOIN solutions_provided sp
ON sp.day = d.day
ORDER BY d.day DESC
Isso é perfeito! Obrigado @simon!