I tried to recreate the “requirements for Trust Level 3” report that staff can see on a user’s admin profile page as a Data Explorer query.
The motivation for this is that, with the new feature allowing a query to be limited to the current user’s ID, TL2 users can check their own progress without needing to ask staff.
The query uses the values configured in site settings and compares them with the user’s statistics. I noticed some tiny differences between the admin view and the query results, likely caused by rounding. The logic for TL3 promotion is quite complex, so the SQL is necessarily a bit complicated, and there may still be mistakes. I would really appreciate any feedback so I can correct any errors.
Here is the current query:
Currently, this is not limited to current_user_id because, for testing, it’s easier to be able to select a user you run the query on. To change that you’d change the second line to -- current_user_id :user_id
-- [params]
-- user_id :user_id
WITH settings AS (
SELECT
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_time_period'), 100) AS time_period,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_days_visited'), 50) AS min_days_visited,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_replied_to'), 10) AS min_topics_replied_to,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed'), 25) AS topics_viewed_percent,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed_cap'), 500) AS topics_viewed_cap,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read'), 25) AS posts_read_percent,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read_cap'), 20000) AS posts_read_cap,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_topics_viewed_all_time'), 200) AS min_topics_viewed_all_time,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_posts_read_all_time'), 500) AS min_posts_read_all_time,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_likes_given'), 30) AS min_likes_given,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_likes_received'), 20) AS min_likes_received,
COALESCE((SELECT value::int FROM site_settings WHERE name = 'tl3_requires_max_flagged'), 5) AS max_flagged
),
period AS (
SELECT
NOW() - (time_period || ' days')::interval AS since,
time_period
FROM settings
),
global_counts AS (
SELECT
(SELECT COUNT(*) FROM topics t, period p
WHERE t.created_at > p.since
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_recent,
(SELECT COUNT(*)
FROM posts p
JOIN topics t ON t.id = p.topic_id, period pr
WHERE p.created_at > pr.since
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS posts_recent
),
user_stats AS (
SELECT
u.id AS user_id,
(SELECT COUNT(*) FROM user_visits uv, period p
WHERE uv.user_id = u.id
AND uv.visited_at > p.since
AND uv.posts_read > 0
) AS days_visited,
(SELECT COUNT(DISTINCT p.topic_id)
FROM posts p
JOIN topics t ON t.id = p.topic_id, period pr
WHERE p.user_id = u.id
AND p.created_at > pr.since
AND p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND t.user_id <> u.id
) AS topics_replied_to,
(SELECT COUNT(DISTINCT tv.topic_id)
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id, period pr
WHERE tv.user_id = u.id
AND tv.viewed_at > pr.since
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_viewed_recent,
(SELECT COUNT(DISTINCT tv.topic_id)
FROM topic_views tv
JOIN topics t ON t.id = tv.topic_id
WHERE tv.user_id = u.id
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
) AS topics_viewed_all_time,
(SELECT COALESCE(SUM(uv.posts_read),0)
FROM user_visits uv, period p
WHERE uv.user_id = u.id
AND uv.visited_at > p.since
) AS posts_read_recent,
(SELECT COALESCE(SUM(uv.posts_read),0)
FROM user_visits uv
WHERE uv.user_id = u.id
) AS posts_read_all_time,
(SELECT COUNT(*)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 1
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_given,
(SELECT COUNT(*)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received,
(SELECT COUNT(DISTINCT DATE(ua.created_at))
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received_days,
(SELECT COUNT(DISTINCT ua.acting_user_id)
FROM user_actions ua
JOIN topics t ON t.id = ua.target_topic_id, period p
WHERE ua.user_id = u.id
AND ua.action_type = 2
AND ua.created_at > p.since
AND t.archetype = 'regular'
) AS likes_received_users
FROM users u
WHERE u.id = :user_id
),
flags AS (
SELECT
COUNT(DISTINCT p.id) AS flagged_posts,
COUNT(DISTINCT pa.user_id) AS flagged_users
FROM posts p
JOIN post_actions pa ON pa.post_id = p.id
JOIN period pr ON true
WHERE p.user_id = :user_id
AND p.created_at > pr.since
AND pa.agreed_at IS NOT NULL
AND pa.user_id <> :user_id
AND (p.spam_count > 0 OR p.inappropriate_count > 0)
),
penalty_history AS (
SELECT
uh.target_user_id AS user_id,
SUM(
CASE
WHEN uh.action = 30 THEN 1
WHEN uh.action = 31 AND uh.acting_user_id <> -1 THEN -1
ELSE 0
END
) AS silenced_delta,
SUM(
CASE
WHEN uh.action = 10 THEN 1
WHEN uh.action = 11 AND uh.acting_user_id <> -1 THEN -1
ELSE 0
END
) AS suspended_delta
FROM user_histories uh
WHERE uh.created_at > NOW() - INTERVAL '6 months'
AND uh.target_user_id = :user_id
GROUP BY uh.target_user_id
),
penalties AS (
SELECT
u.id AS user_id,
COALESCE(ph.silenced_delta, 0)
+ CASE
WHEN u.silenced_till IS NOT NULL AND u.silenced_till > NOW()
THEN 1 ELSE 0
END AS silenced,
COALESCE(ph.suspended_delta, 0)
+ CASE
WHEN u.suspended_till IS NOT NULL AND u.suspended_till > NOW()
THEN 1 ELSE 0
END AS suspended
FROM users u
LEFT JOIN penalty_history ph ON ph.user_id = u.id
WHERE u.id = :user_id
)
SELECT
requirement,
required_value,
user_value,
fulfill
FROM (
SELECT
'Topics Replied To (last ' || s.time_period || ' days)' AS requirement,
s.min_topics_replied_to AS required_value,
us.topics_replied_to AS user_value,
CASE
WHEN us.topics_replied_to >= s.min_topics_replied_to
THEN '✔' ELSE '✘'
END AS fulfill
FROM user_stats us, settings s
UNION ALL
SELECT
'Topics Viewed (last ' || s.time_period || ' days)',
LEAST(CEIL(gc.topics_recent * s.topics_viewed_percent / 100.0), s.topics_viewed_cap)::int,
us.topics_viewed_recent,
CASE WHEN us.topics_viewed_recent >=
LEAST(gc.topics_recent * s.topics_viewed_percent / 100, s.topics_viewed_cap)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s, global_counts gc
UNION ALL
SELECT
'Topics Viewed (all time)',
s.min_topics_viewed_all_time,
us.topics_viewed_all_time,
CASE WHEN us.topics_viewed_all_time >= s.min_topics_viewed_all_time THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Read (last ' || s.time_period || ' days)',
LEAST( CEIL(gc.posts_recent * s.posts_read_percent / 100.0), s.posts_read_cap)::int,
us.posts_read_recent,
CASE WHEN us.posts_read_recent >=
LEAST(gc.posts_recent * s.posts_read_percent / 100, s.posts_read_cap)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s, global_counts gc
UNION ALL
SELECT
'Posts Read: unique days (last ' || s.time_period || ' days)',
s.min_days_visited,
us.days_visited,
CASE WHEN us.days_visited >= s.min_days_visited THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Posts Read (all time)',
s.min_posts_read_all_time,
us.posts_read_all_time,
CASE WHEN us.posts_read_all_time >= s.min_posts_read_all_time THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Flagged Posts (last ' || s.time_period || ' days)',
s.max_flagged,
f.flagged_posts,
CASE WHEN f.flagged_posts <= s.max_flagged THEN '✔' ELSE '✘' END
FROM flags f, settings s
UNION ALL
SELECT
'Users Who Flagged (last ' || s.time_period || ' days)',
s.max_flagged,
f.flagged_users,
CASE WHEN f.flagged_users <= s.max_flagged THEN '✔' ELSE '✘' END
FROM flags f, settings s
UNION ALL
SELECT
'Likes Given (last ' || s.time_period || ' days)',
s.min_likes_given,
us.likes_given,
CASE WHEN us.likes_given >= s.min_likes_given THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received (last ' || s.time_period || ' days)',
s.min_likes_received,
us.likes_received,
CASE WHEN us.likes_received >= s.min_likes_received THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received: unique days (last ' || s.time_period || ' days)',
LEAST(CEIL(s.min_likes_received / 3.0), CEIL(0.75 * s.time_period))::int,
us.likes_received_days,
CASE WHEN us.likes_received_days >=
LEAST(CEIL(s.min_likes_received / 3.0), CEIL(0.75 * s.time_period))::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Likes Received: unique users (last ' || s.time_period || ' days)',
CEIL(s.min_likes_received / 4.0)::int,
us.likes_received_users,
CASE WHEN us.likes_received_users >= CEIL(s.min_likes_received / 4.0)::int
THEN '✔' ELSE '✘' END
FROM user_stats us, settings s
UNION ALL
SELECT
'Silenced (last 6 months)',
0,
p.silenced,
CASE WHEN p.silenced = 0 THEN '✔' ELSE '✘' END
FROM penalties p
UNION ALL
SELECT
'Suspended (last 6 months)',
0,
p.suspended,
CASE WHEN p.suspended = 0 THEN '✔' ELSE '✘' END
FROM penalties p
) x
Example result:
