Find the users which are more likely to become TL3

This is slightly more efficient. If it still doesn’t run for you, then you can try stripping out some of the columns, with their associated joins and queries.

EDIT Okay, I finally got my join types straight (it’s been a while). This updated query is much more efficient

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%
-- lesser of 25% topics created in last 100 days 
-- OR 500, the system default max requirement for 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
),

-- Post count last 100 days 25%
-- lesser of 25% posts created in last 100 days 
-- OR 20k, the system default max requirement for 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  -- Omit Discobot & System
    AND (action_code is null OR action_code != 'assigned')
),

-- Trust Level 2 users
tl AS (
SELECT id as user_id, trust_level
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 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
),

-- 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 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 "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(pr.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 prat using (user_id)
LEFT JOIN likes using (user_id)


ORDER BY
  pr.visits DESC
  
LIMIT 50
8 Likes