Hallo zusammen,
in unserer Community haben wir ein Programm, bei dem wir bestimmte regelmäßige Nutzer zum „PosBuddy" befördern. Das sind Nutzer, die unseren Marken-Tonfall treffen und sich durch ihre Fähigkeit auszeichnen, Fragen zu beantworten, Gespräche anzukurbeln und neue Mitglieder in der Community willkommen zu heißen.
Dieses Programm gewährt diesen Nutzern die Vertrauensebene 4 (TL4). Sie haben ihre eigene private Gruppe innerhalb der Community und können Beiträge schließen, zusammenführen und verschieben – im Allgemeinen sorgen sie so für Ordnung in der Community. Außerdem bieten wir diesen Mitgliedern zahlreiche tolle Vorteile wie kostenlose Produkte, Merchandise und exklusive Einladungen zu unseren Entwicklungsvorführungen. Wie Sie sich vorstellen können, gibt es also starke Anreize, sich bis zur Vertrauensebene 3 (TL3) hochzuarbeiten und einen Beitrag zu unserer Community zu leisten.
Oft möchten wir wissen, welche Nutzer fast auf TL3 sind, es aber noch nicht ganz geschafft haben. Dazu prüfen wir jeden Monat Hunderte von TL2-Nutzern, um zu sehen, wie weit sie noch von TL3 entfernt sind, und überprüfen ihre Anforderungen gemäß der Tabelle für Vertrauensebene 3.
Wir möchten diesen Prozess erleichtern und haben eine Data Explorer-Abfrage, die den Großteil der benötigten Informationen in einer einzigen Ansicht darstellt. Allerdings scheint es mir nicht möglich zu sein, folgende Felder in die unten aufgeführte Abfrage einzubeziehen:
- Themen, auf die geantwortet wurde
- Gemeldete Beiträge
- Nutzer, die gemeldet haben
- Erhaltene Likes: eindeutige Tage
- Erhaltene Likes: eindeutige Nutzer
- Stummgeschaltet (gesamte Zeit)
- Ausgesetzt (gesamte Zeit)
Falls jemand helfen könnte, wäre ich unendlich dankbar.
-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 100
with
t as (
select
CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) as end
),
pr as (
select user_id,
count(1) as visits,
sum(posts_read) as posts_read
from user_visits, t
where posts_read > 0
and visited_at > t.start
and visited_at < t.end
group by user_id
),
pc as (
select user_id,
count(1) as posts_created
from posts, t
where created_at > t.start
and created_at < t.end
group by user_id
),
ttopics as (
select user_id, posts_count
from topics, t
where created_at > t.start
and created_at < t.end
),
tc as (
select user_id,
count(1) as topics_created
from ttopics
group by user_id
),
twr as (
select user_id,
count(1) as topics_with_replies
from ttopics
where posts_count > 1
group by user_id
),
tv as (
select user_id,
count(distinct(topic_id)) as topics_viewed
from topic_views, t
where viewed_at > t.start
and viewed_at < t.end
group by user_id
),
likes as (
select
post_actions.user_id as given_by_user_id,
posts.user_id as received_by_user_id
from t, post_actions
left join posts
on post_actions.post_id = posts.id
where post_actions.created_at > t.start
and post_actions.created_at < t.end
and post_action_type_id = 2
),
lg as (
select given_by_user_id as user_id,
count(1) as likes_given
from likes
group by user_id
),
lr as (
select received_by_user_id as user_id,
count(1) as likes_received
from likes
group by user_id
),
e as (
select email, user_id
from user_emails u
where u.primary = true
)
select pr.user_id,
coalesce(visits,0) as "Besuche",
coalesce(topics_viewed,0) as "Angesehene Themen",
coalesce(posts_read,0) as "Gelesene Beiträge",
coalesce(posts_created,0) as "Erstellte Beiträge",
coalesce(topics_created,0) as "Erstellte Themen",
coalesce(topics_with_replies,0) as "Themen mit Antworten",
coalesce(likes_given,0) as "Gegebene Likes",
coalesce(likes_received,0) as "Erhaltene Likes"
from pr
left join tv using (user_id)
left join pc using (user_id)
left join tc using (user_id)
left join twr using (user_id)
left join lg using (user_id)
left join lr using (user_id)
left join e using (user_id)
left join users on pr.user_id = users.id
order by
visits desc,
posts_read desc,
posts_created desc