Find the users which are more likely to become TL3

Community Building Data & reporting
,
13

Here’s a modified version with no parameters: I have lazily (efficiently?) hard-coded the default TL3 requirements into the query. I have expanded the data set slightly to include likes given and received, although not likes/unique days or likes/unique users. Flags, silences and suspensions are also still missing.

This is a gap report, so it does the subtraction to show you what each user is missing.

In a couple places, it doesn’t exactly match what shows up on the user admin page:

  • likes given (my count is somehow higher)
  • posts last 100 days (my count is lower)

There’s a good bit of guesswork in my count of the last-100-days-posts

But in case it’s helpful:

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

),

-- Topic count last 100 days 25%
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
),

-- Post count last 100 days 25%
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  -- Omit Discobot & System
    AND (action_code is null OR action_code != 'assigned')

),

-- Users
pr AS (
    SELECT user_id, 
        count(1) as visits
    FROM user_visits, t
    WHERE visited_at > t.start
      AND visited_at < t.end
    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 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
    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
    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
),

-- 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
),

likes AS (
SELECT user_id,
    likes_given, likes_received
from user_stats
)


SELECT  pr.user_id, 
        greatest(50-coalesce(pr.visits,0),0) as "Days visited gap",
        greatest(10-coalesce(trt.topic_id,0), 0)  as "Topic reply gap",
        greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Topics Viewed gap",
        greatest(200-coalesce(tvat.topic_id,0),0) as "Topic viewed (AT) gap",
        greatest(pclhd.all_posts - coalesce(pra.posts_read,0),0) as "Posts Read gap",
        greatest(500-coalesce(prat.posts_read,0),0) as "Posts Read (AT) gap",
        greatest(30-likes.likes_given,0) as "Likes given gap",
        greatest(20-likes.likes_received,0) as "Likes received gap"

FROM pclhd, tclhd, 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 likes using (user_id)
LEFT JOIN tl ON (tl.id = pr.user_id)

WHERE tl.trust_level = 2

ORDER BY
  pr.visits DESC