أستكشف ما إذا كانت إعدادات مستوى الثقة 3 لدينا منطقية هنا في ميتا (وفي النهاية ما هي الإعدادات الافتراضية التي يجب أن تكون لدينا)
لقد استخدمت استعلامك كنقطة انطلاق، وقمت بتحديثه للإشارة إلى إعدادات الموقع حتى تتمكن من تشغيله إذا قمت بتغيير إعداداتك:
شاهد CTE الجديد tl3_reqs:
WITH
tl3_reqs as (
SELECT
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_time_period'), 100) as tl3_time_period,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_days_visited'), 50) as tl3_days_visited,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_topics_replied_to'), 10) as tl3_requires_topics_replied_to,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_topics_viewed'), 25) as tl3_requires_topics_viewed,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_topics_viewed_cap'), 500) as tl3_requires_topics_viewed_cap,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_posts_read'), 25) as tl3_requires_posts_read,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_posts_read_cap'), 20000) as tl3_requires_posts_read_cap,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_topic_viewed_all_time'), 200) as tl3_requires_topic_viewed_all_time,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_posts_read_all_time'), 500) as tl3_requires_posts_read_all_time,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_requires_max_flagged'), 5) as tl3_requires_max_flagged,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_promotion_min_duration'), 14) as tl3_promotion_min_duration,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_promotion_likes_given'), 30) as tl3_promotion_likes_given,
COALESCE ((SELECT CAST(value AS INT) FROM site_settings WHERE name = 'tl3_promotion_likes_received'), 20) as tl3_promotion_likes_received
),
t as (
SELECT
CURRENT_TIMESTAMP - ((0 + MIN(tl3_time_period)) * (INTERVAL '1 days')) AS start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) AS end
FROM tl3_reqs
),
-- Topics viewed in time period
-- lesser of x% (default 25) topics created in time period (default 100 days)
-- OR cap (default 500)
tvtp AS (
SELECT LEAST(floor(count(id)*(MIN(tl3_requires_topics_viewed)/100.0))::REAL,MIN(tl3_requires_topics_viewed_cap)) as all_topics
FROM topics, t, tl3_reqs
WHERE created_at > t.start
AND archetype = 'regular'
AND deleted_at is null
),
-- Posts read in time period
-- lesser of x% (default 25) topics created in time period (default 100 days)
-- OR cap (default 20K)
prtp AS (
SELECT LEAST(FLOOR(count(id)*(MIN(tl3_requires_posts_read)/100.0))::REAL,MIN(tl3_requires_posts_read_cap)) AS all_posts
FROM t, posts, tl3_reqs
WHERE posts.created_at > start
AND posts.deleted_at IS NULL
AND posts.hidden_at IS NULL
AND posts.last_editor_id >0 -- Omit Discobot & System
AND (action_code IS NULL OR action_code != 'assigned')
),
-- Trust Level 2 users
tl AS (
SELECT id AS user_id, username
FROM users
WHERE trust_level = 2
),
-- Users + visits & posts read last 100 days
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
),
-- Posts Read All Time
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
),
-- Topics replied to
trt AS (
SELECT posts.user_id,
count(distinct topic_id) as replied_count
FROM t, posts
INNER JOIN tl USING (user_id)
INNER JOIN topics ON topics.id = posts.topic_id
WHERE posts.created_at > t.start
AND posts.created_at < t.end
AND topics.user_id <> posts.user_id
AND posts.deleted_at IS NULL AND topics.deleted_at IS NULL
AND archetype = 'regular'
GROUP BY posts.user_id
),
-- Topics Viewed All Time
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
),
-- Topics Viewed
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_received_lhd AS (
SELECT ua.user_id
, count(*) as likes_received_lhd
FROM t, user_actions ua
JOIN posts p on p.id=ua.target_post_id
JOIN tl on ua.user_id=tl.user_id
WHERE ua.action_type=1
AND ua.created_at > t.start
AND ua.created_at < t.end
GROUP BY ua.user_id
),
likes_given_lhd AS (
SELECT user_id, count(*) as likes_given_lhd
FROM t, given_daily_likes
INNER JOIN tl using (user_id)
WHERE given_date > t.start
AND given_date < t.end
GROUP BY user_id
)
SELECT pr.user_id,
tl.username,
GREATEST(tl3_days_visited-COALESCE(pr.visits,0),0) as "Days visited time period gap",
GREATEST(tl3_requires_topics_replied_to-COALESCE(trt.replied_count,0), 0) as "Topic reply gap",
GREATEST(tvtp.all_topics-COALESCE(tva.topic_id,0),0) AS "Topics Viewed time period gap of 150",
GREATEST(tl3_requires_topic_viewed_all_time-COALESCE(tvat.topic_id,0),0) as "Topic viewed (AT) gap",
GREATEST(prtp.all_posts - COALESCE(pr.posts_read,0),0) as "Posts Read lhd gap of 250",
GREATEST(tl3_requires_posts_read_all_time-COALESCE(prat.posts_read,0),0) as "Posts Read (AT) gap",
GREATEST(tl3_promotion_likes_given-COALESCE(likes_given_lhd,0),0) as "Likes given time period gap",
GREATEST(tl3_promotion_likes_received-COALESCE(likes_received_lhd,0),0) as "Likes received lhd gap"
FROM prtp, tvtp, tl3_reqs, pr
LEFT JOIN tl USING (user_id)
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_received_lhd USING (user_id)
LEFT JOIN likes_given_lhd USING (user_id)
ORDER BY pr.visits DESC
LIMIT 25