مرحبًا يا أصدقاء،
لدينا نظام داخل مجتمعنا نمنح بموجبه بعض الأعضاء الدائمين حالة PosBuddy. هؤلاء هم أعضاء منتظمون يتبنون نبرة صوت علامتنا التجارية وأظهروا كفاءة في الإجابة على الأسئلة، وتشجيع المحادثات، وترحيب الأعضاء الجدد داخل المجتمع.
يمنح هذا النظام هؤلاء المستخدمين مستوى ثقة 4 (TL4)، ولديهم مجموعة خاصة بهم داخل المجتمع، مع القدرة على إغلاق المواضيع، ودمجها، ونقلها: بشكل عام للحفاظ على نظافة المجتمع. كما نقدم لهؤلاء الأعضاء العديد من المزايا الرائعة مثل المنتجات المجانية، والبضائع، والدعوات الحصرية لعروضنا التطويرية. لذا، كما تتخيل، هناك حوافز كبيرة للعمل للوصول إلى مستوى ثقة 3 (TL3) والمساهمة في مجتمعنا.
في كثير من الأحيان، نرغب في معرفة المستخدمين الذين هم على وشك الوصول إلى TL4، لكنهم لم يصلوا بعد، ولتحقيق ذلك، نقوم بمراجعة مئات المستخدمين من مستوى ثقة 2 (TL2) كل شهر لمعرفة مدى قربهم من الوصول إلى TL4 والتحقق من متطلباتهم وفقًا لجدول مستوى الثقة 3.
نريد جعل هذه العملية أسهل، لذا لدينا استعلام لاستكشاف البيانات يعرض معظم المعلومات التي نحتاجها في عرض واحد، ومع ذلك لا أستطيع معرفة كيفية تضمين الحقول التالية في الاستعلام الموضح أدناه:
- المواضيع التي تم الرد عليها
- المنشورات المبلغ عنها
- المستخدمون الذين أبلغوا
- الإعجابات المستلمة: أيام فريدة
- الإعجابات المستلمة: مستخدمون فريدون
- صامت (طوال الوقت)
- معلق (طوال الوقت)
إذا كان بإمكان أي شخص المساعدة، سأكون ممتنًا إلى الأبد.
-- [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 "Visits",
coalesce(topics_viewed,0) as "Topics Viewed",
coalesce(posts_read,0) as "Posts Read",
coalesce(posts_created,0) as "Posts Created",
coalesce(topics_created,0) as "Topics Created",
coalesce(topics_with_replies,0) as "Topics with Replies",
coalesce(likes_given,0) as "Likes Given",
coalesce(likes_received,0) as "Likes Received"
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