Sondage auprès d'utilisateurs spécifiques sur leur activité

Communauté Données et rapports
1

Bonjour,

Je souhaiterais interroger des noms d’utilisateurs spécifiques pour obtenir leurs likes, les likes reçus, les publications et les solutions, sur une plage de dates donnée, découpée par jour. Avez-vous des suggestions pour ce type de rapport ? Je soupçonne qu’il faudrait utiliser le plugin Data Explorer. Quelqu’un a-t-il une requête déjà construite pour ce genre de travail ? Toute remarque est la bienvenue !

2

J’avais une requête très similaire à ce que vous recherchez sur mon site de développement local. Je l’ai un peu modifiée pour répondre à vos besoins. L’un des objectifs que j’ai poursuivis était de permettre l’exécution de la requête sur soit l’activité survenue dans les sujets réguliers, soit l’activité survenue dans les messages privés. Une partie de ma motivation pour cela était de pouvoir examiner ma propre activité sur une période donnée. Pouvoir interroger soit les sujets réguliers, soit les messages privés a du sens dans mon cas. La manière dont les solutions sont gérées par la requête lorsque la case à cocher personal_messages est activée n’est pas idéale : la colonne solutions_provided renverra simplement NULL dans ce cas. Il est possible que l’option de bascule entre sujets réguliers et messages privés doive être retirée de la requête.

Cette requête n’est pas excessivement complexe, mais elle est assez longue. Si vous remarquez des problèmes dans ses résultats, n’hésitez pas à me le faire savoir.

La requête nécessite que vous fournissiez trois paramètres avant de pouvoir l’exécuter : username, start_date et end_date. Les paramètres de date doivent être au format ‘yyyy-mm-dd’. Par exemple 2020-03-13. Le paramètre personal_messages a pour valeur par défaut false. Cochez la case personal_messages de la requête si vous souhaitez voir les résultats pour l’activité survenue dans les messages privés.

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

C’est parfait ! Merci @simon !