Great start here, thanks!
I made a few tweaks / fixes:
- Added ‘posts_read > 0’ condition for more accurate user visits calculation
- Removed ‘visits (all time)’ which didn’t seem to be necessary
- Fixed ‘topics viewed’ calculations which was using the wrong table
- Added current trust level (to only get tl2 users)
- Added where clauses for other relevant conditions, set at 50% of current threshold
Also parameterized a bunch of things so you can set your own values for each of the required metrics (since they may vary forum by forum), and also set a threshold percentage to show only users who meet at least that % of ALL the metrics.
So for example the below by default lists only tl2 users who meet 50% or more of all the requirements for visits, topics replied to, topics viewed, posts read…you could set it to 30% or 85% or whatever if it seems to be returning too many or too few results.
I did not add the requirements for likes given/received, or for flags/silences/suspensions. For us at least, the latter are super rare anyway, and I figure likes is one of the easier barriers to get people over if they know about it (some people just barely ever give likes). So this works pretty well for us. But the rest of the requirements could be added if you wanted.
For reference, on our forum we have ~1,000 TL2 users, ~10 TL3 users, and this query identifies ~30 ‘potential/almost TL3’ users with the 50% threshold.
-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 100
-- int :trust_level = 2
-- int :threshold = 50
-- int :visits = 50
-- int :topics_replied_to = 10
-- int :topics_viewed = 76
-- int :topics_viewed_all_time = 200
-- int :posts_read = 755
-- int :posts_read_all_time = 500
-- NOTES
-- trust_level show current TL2 users only
-- threshold show only at users >= this percentage of all above metrics
-- topics_viewed depends on total # of topics (default 25%)
-- posts_read depends on total # of posts (default 25%)
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
),
-- User Visits
pr AS (
SELECT user_id,
count(1) as visits
FROM user_visits, t
WHERE visited_at > t.start
AND visited_at < t.end
AND posts_read > 0
GROUP BY user_id
ORDER BY visits DESC
),
-- Topics Replied To
trt AS (
SELECT user_id,
COUNT(distinct topic_id) AS topic_id
FROM posts, t
WHERE created_at > t.start
AND created_at < t.end
GROUP BY user_id
),
-- Topics Viewed
tva AS (
SELECT user_id,
COUNT(distinct topic_id) AS topic_id
FROM topic_views, t
WHERE viewed_at > t.start
AND viewed_at < t.end
GROUP BY user_id
),
-- Topics Viewed (All Time)
tvat AS (
SELECT user_id,
COUNT(distinct topic_id) AS topic_id
FROM topic_views
GROUP BY user_id
),
-- Posts Read
pra AS (
SELECT user_id,
SUM(posts_read) AS posts_read
FROM user_visits, t
WHERE visited_at > t.start
AND visited_at < t.end
GROUP BY user_id
),
-- Posts Read (All Time)
prat AS (
SELECT user_id,
SUM(posts_read) AS posts_read
FROM user_visits, t
GROUP BY user_id
),
-- Current Trust Level
tl AS (
SELECT id,
trust_level
FROM users
)
SELECT pr.user_id,
-- tl.trust_level AS "Trust Level",
coalesce(pr.visits,0) AS "Visits",
coalesce(trt.topic_id,0) AS "Topic Replied To",
coalesce(tva.topic_id,0) AS "Topics Viewed",
coalesce(tvat.topic_id,0) AS "Topics Viewed (AT)",
coalesce(pra.posts_read,0) AS "Posts Read",
coalesce(prat.posts_read,0) AS "Posts Read (AT)"
FROM pr
LEFT JOIN trt USING (user_id)
LEFT JOIN tva USING (user_id)
LEFT JOIN tvat USING (user_id)
LEFT JOIN pra USING (user_id)
LEFT JOIN prat USING (user_id)
LEFT JOIN tl ON (tl.id = pr.user_id)
WHERE
tl.trust_level = :trust_level
AND pr.visits >= :visits * :threshold / 100
AND trt.topic_id >= :topics_replied_to * :threshold / 100
AND tva.topic_id >= :topics_viewed * :threshold / 100
AND tvat.topic_id >= :topics_viewed_all_time * :threshold / 100
AND pra.posts_read >= :posts_read * :threshold / 100
AND prat.posts_read >= :posts_read_all_time * :threshold / 100
ORDER BY
pr.visits DESC