Encuesta a usuarios específicos sobre su actividad

[Jim.Morrison]

Buenas tardes,

Me gustaría consultar datos específicos de ciertos usuarios: sus likes, los likes recibidos, publicaciones y soluciones, para un rango de fechas concreto, desglosado por días. ¿Tienen alguna sugerencia para este tipo de informes? Sospecho que necesitaría aprovechar el plugin de explorador de datos; ¿alguien tiene una consulta ya elaborada para este tipo de trabajo? ¡Agradeceré cualquier comentario!

[simon]

Tuve una consulta muy similar a la que buscas en mi sitio de desarrollo local. La he modificado un poco para cumplir con tus requisitos. Uno de los objetivos que intenté lograr fue permitir que la consulta se ejecute contra ya sea actividad ocurrida en temas regulares o actividad ocurrida en mensajes privados (PM). Parte de mi motivación para hacerlo fue que quería analizar mi propia actividad durante un período de tiempo. Poder consultar por temas regulares o por PM tiene sentido en mi caso. La forma en que la consulta maneja las soluciones cuando la casilla personal_messages está marcada no es ideal: la columna solutions_provided simplemente devolverá NULL en ese caso. Posiblemente, el interruptor entre temas regulares y PM debería eliminarse de la consulta.

Esta consulta no es excesivamente compleja, pero es bastante larga. Si notas algún problema con sus resultados, por favor házmelo saber.

La consulta requiere que proporciones tres parámetros antes de poder ejecutarla: username, start_date y end_date. Los parámetros de fecha deben estar en el formato ‘yyyy-mm-dd’. Por ejemplo: 2020-03-13. El parámetro personal_messages tiene como valor predeterminado false. Selecciona la casilla personal_messages de la consulta si deseas ver resultados para la actividad ocurrida en mensajes privados.

--[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

[Jim.Morrison]

¡Esto es perfecto! Gracias @simon!