Find the users which are more likely to become TL3

البيانات والتقارير
,
17

هذا أكثر كفاءة إلى حد ما. إذا لم ينجح الأمر معك بعد، يمكنك محاولة إزالة بعض الأعمدة، مع عمليات الربط والاستعلامات المرتبطة بها.

تعديل حسنًا، لقد استقمت أخيرًا مع أنواع الربط (لقد مر وقت طويل). هذا الاستعلام المحدث أكثر كفاءة بكثير.

with
t as (
  select
    CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
    CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
),

-- عدد الموضوعات في آخر 100 يوم 25%
-- أقل من 25% من الموضوعات التي تم إنشاؤها في آخر 100 يوم
-- أو 500، الحد الأقصى الافتراضي للنظام المطلوب لـ TL3
tclhd AS (
    SELECT LEAST(floor(count(id)*.25)::REAL,500) as all_topics
    FROM topics, t
    WHERE created_at > t.start
        AND archetype = 'regular'
        AND deleted_at is null
),

-- عدد المشاركات في آخر 100 يوم 25%
-- أقل من 25% من المشاركات التي تم إنشاؤها في آخر 100 يوم
-- أو 20 ألف، الحد الأقصى الافتراضي للنظام المطلوب لـ TL3
pclhd AS (
SELECT LEAST(FLOOR(count(id)*.25)::REAL,20000) AS all_posts
FROM t, posts
WHERE posts.created_at > start
    AND posts.deleted_at is null
    AND posts.hidden_at is null
    AND posts.last_editor_id >0  -- استبعاد Discobot والنظام
    AND (action_code is null OR action_code != 'assigned')
),

-- مستخدمو المستوى 2 للثقة
tl AS (
SELECT id as user_id, trust_level
FROM users
WHERE trust_level = 2
),

-- المستخدمون، الزيارات والمشاركات المقروءة في آخر 100 يوم
pr AS (
    SELECT user_id,
        count(1) as visits,
        sum(posts_read) as posts_read
    FROM t, user_visits
    INNER JOIN tl using (user_id)
    WHERE visited_at > t.start
      AND visited_at < t.end
    GROUP BY user_id
    ORDER BY visits DESC
),

-- المشاركات المقروءة طوال الوقت
prat as (
    select user_id,
        sum(posts_read) as posts_read
    from t, user_visits
    INNER JOIN tl using (user_id)
    group by user_id
),

-- الموضوعات التي تم الرد عليها
trt as (
    select user_id,
           count(distinct topic_id) as topic_id
    from t, posts
    INNER JOIN tl using (user_id)
    where posts.created_at > t.start
      and posts.created_at < t.end
    group by user_id
),

-- الموضوعات التي تم عرضها طوال الوقت
tvat as (
    select tv.user_id,
        COUNT(distinct tv.topic_id) AS topic_id
    FROM topic_views tv
    LEFT JOIN topics t on tv.topic_id=t.id
    INNER JOIN tl on tv.user_id=tl.user_id
    WHERE
        t.archetype = 'regular'
        AND t.deleted_at is null
    group by tv.user_id
),

-- الموضوعات التي تم عرضها
tva AS (
SELECT tv.user_id,
    COUNT(distinct tv.topic_id) AS topic_id
FROM t, topic_views tv
    LEFT JOIN topics on topic_id=topics.id
    INNER JOIN tl on tv.user_id=tl.user_id
    WHERE
        topics.archetype = 'regular'
        AND topics.deleted_at is null
        AND viewed_at > t.start
        AND viewed_at < t.end
GROUP BY tv.user_id
),

likes AS (
    SELECT user_id,
        likes_given, likes_received
    from user_stats
    INNER JOIN tl using (user_id)
)


SELECT  pr.user_id,
        greatest(50-coalesce(pr.visits,0),0) as "فجوة أيام الزيارة",
        greatest(10-coalesce(trt.topic_id,0), 0)  as "فجوة الرد على الموضوعات",
        greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "فجوة الموضوعات التي تم عرضها",
        greatest(200-coalesce(tvat.topic_id,0),0) as "فجوة الموضوعات التي تم عرضها (طوال الوقت)",
        greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "فجوة المشاركات المقروءة",
        greatest(500-coalesce(prat.posts_read,0),0) as "فجوة المشاركات المقروءة (طوال الوقت)",
        greatest(30-likes.likes_given,0) as "فجوة الإعجابات المقدمة",
        greatest(20-likes.likes_received,0) as "فجوة الإعجابات المستلمة"

FROM pclhd, tclhd, pr
left join trt using (user_id)
LEFT JOIN tva USING (user_id)
left join tvat using (user_id)
left join prat using (user_id)
LEFT JOIN likes using (user_id)


ORDER BY
  pr.visits DESC

LIMIT 50
8 إعجابات