Umfragen bei bestimmten Nutzern zu ihrer Aktivität

Gemeinschaft Daten & Berichterstattung
1

Guten Nachmittag,

ich möchte für bestimmte Benutzernamen deren Likes, erhaltene Likes, Beiträge und Lösungen für einen bestimmten Zeitraum, aufgeteilt nach Tagen, abfragen. Haben Sie Vorschläge für eine solche Berichterstattung? Ich vermute, dass ich das Data Explorer-Plugin nutzen müsste. Hat jemand eine bereits erstellte Abfrage für diese Art von Arbeit? Jegliches Feedback wird geschätzt!

2

Ich hatte eine Abfrage auf meiner lokalen Entwicklungsumgebung, die deiner Anforderung sehr ähnlich war. Ich habe sie etwas angepasst, um deine Anforderungen zu erfüllen. Eines, was ich versucht habe, ist, die Abfrage so zu gestalten, dass sie entweder auf Aktivitäten in regulären Themen oder auf Aktivitäten in privaten Nachrichten (PMs) angewendet werden kann. Ein Teil meiner Motivation dafür war, dass ich meine eigenen Aktivitäten über einen bestimmten Zeitraum betrachten wollte. Die Möglichkeit, nach regulären Themen oder PMs abzufragen, ergibt in meinem Fall Sinn. Die Art und Weise, wie Lösungen von der Abfrage behandelt werden, wenn das Kontrollkästchen personal_messages aktiviert ist, ist nicht ideal – die Spalte solutions_provided gibt in diesem Fall einfach NULL zurück. Möglicherweise sollte der Umschalter zwischen regulären Themen und PMs aus der Abfrage entfernt werden.

Diese Abfrage ist nicht übermäßig komplex, aber recht lang. Falls du Probleme mit den Ergebnissen feststellst, lass es mich bitte wissen.

Die Abfrage erfordert, dass du drei Parameter angibst, bevor sie ausgeführt werden kann: username, start_date und end_date. Die Datumsparameter müssen im Format ‘jjjj-mm-tt’ vorliegen, z. B. 2020-03-13. Der Parameter personal_messages hat standardmäßig den Wert false. Aktiviere das Kontrollkästchen personal_messages der Abfrage, wenn du Ergebnisse für Aktivitäten in privaten Nachrichten sehen möchtest.

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

Das ist perfekt! Danke @simon!