Ich glaube nicht, dass die von Ihnen aufgeführten Eingaben mit dem gewünschten Ergebnis übereinstimmen. Wenn Sie Daten für einen einzelnen Benutzer erhalten möchten, könnte eine Abfrage wie die folgende als Ausgangspunkt dienen. Sie verwendet Common Table Expressions, um die Abfrage lesbarer und einfacher zu schreiben. Dasselbe Muster kann beibehalten werden, um weitere Daten in die Ergebnisse aufzunehmen.
Eine Sache, bei der ich mir nicht sicher war, ist, ob Sie die Summe der Daten für einen bestimmten Tag oder die Anzahl der Aktionen, die an jedem Tag stattgefunden haben, benötigen. Möchten Sie beispielsweise wissen, dass ein Benutzer am 2019-10-31 insgesamt 20 Beiträge erstellt hat, seit er auf der Website ist, oder möchten Sie wissen, dass der Benutzer am 2019-10-31 genau 3 Beiträge erstellt hat? Die aktuelle Struktur der Abfrage liefert das letztere Ergebnis.
--[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
Um die Benutzeraktivität zu steigern, planen wir eine Challenge durchzuführen, bei der der aktivste Benutzer in unserem Forum ermittelt und belohnt wird.
Daher möchten wir zusätzliche Daten sammeln, um die Challenge noch spannender zu gestalten.
Die Daten, die wir mit der Abfrage von @simon haben, sind: Themen, Beiträge, Wortanzahl und erhaltene Likes.
Zusätzliche Daten umfassen:
Gegebene Likes: Wie viele Likes wurden vergeben?
Besuche: Wie oft hat der Benutzer das Forum geöffnet?
Lesezeit: Wie lange war der Benutzer im Forum?
Folgende und Follower
Themenaufrufe: Wie viele Aufrufe haben die vom Benutzer erstellten Themen insgesamt erhalten?
Themenantworten: Wie viele Antworten (Beiträge) haben die vom Benutzer erstellten Themen insgesamt erhalten?
Bitte unterstützt uns dabei, diese zusätzlichen Daten zu erhalten.
Für die Gewichtung der einzelnen Punkte planen wir die Verwendung von Excel. Gibt es Ratschläge, wie man die einzelnen Punkte gewichten kann, um den Gewinner auszuwählen?
Wir möchten diese Challenge ernsthaft, aber auch unterhaltsam gestalten. Die besten Benutzer sind eine Kombination aus am aktivsten, am hilfreichsten und am beliebtesten.
Nochmals vielen Dank für eure Hilfe.
Hinweis: Können wir die Daten gruppiert abrufen, sodass wir nur die Gruppen-ID eingeben müssen? Unser Plan ist es, alle Teilnehmer in eine Gruppe zu setzen. Momentan müssen wir die Benutzer-ID jedes Teilnehmers einzeln eingeben.
hier spricht ein weiterer Mancunianer
Mein Unternehmen plant, einen ähnlichen Wettbewerb intern durchzuführen, um das Engagement unserer Mitarbeiter in der Community zu steigern.
Hast du den Wettbewerb erfolgreich durchgeführt? Wie hast du am Ende die Daten extrahiert? Und wie hast du die verschiedenen Engagement-Metriken letztendlich gewichtet?
Ich würde mich sehr freuen, mit dir darüber zu sprechen!
