Find the users which are more likely to become TL3

Data & reporting
,
26

You part about settings looks like what I shared here

2 Likes
27

Another tweak to order by “number of gaps”

Also, filter users early based on last_seen_at to optimize query a bit

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 t, users
    WHERE trust_level = 2
    AND last_seen_at > t.start
),

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

gaps AS (
    SELECT  pr.user_id, 
            tl.username,
            GREATEST(tl3_days_visited-COALESCE(pr.visits,0),0) as days_visited_gap,
            GREATEST(tl3_requires_topics_replied_to-COALESCE(trt.replied_count,0), 0)  as topics_replied_to_gap,
            GREATEST(tvtp.all_topics-COALESCE(tva.topic_id,0),0) AS topics_viewed_to_gap,
            GREATEST(prtp.all_posts - COALESCE(pr.posts_read,0),0) as posts_read_gap,
            GREATEST(tl3_promotion_likes_given-COALESCE(likes_given_lhd,0),0) as likes_given_gap,
            GREATEST(tl3_promotion_likes_received-COALESCE(likes_received_lhd,0),0) as likes_received_gap,
            GREATEST(tl3_requires_topic_viewed_all_time-COALESCE(tvat.topic_id,0),0) as topics_viewed_all_time_gap,
            GREATEST(tl3_requires_posts_read_all_time-COALESCE(prat.posts_read,0),0) as posts_read_all_time_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)
),

num_gaps AS (
  SELECT 
    user_id,
    CASE WHEN days_visited_gap > 0 THEN 1 ELSE 0 END
    + CASE WHEN topics_replied_to_gap > 0 THEN 1 ELSE 0 END
    + CASE WHEN topics_viewed_to_gap > 0 THEN 1 ELSE 0 END
    + CASE WHEN posts_read_gap > 0 THEN 1 ELSE 0 END
    + CASE WHEN likes_given_gap > 0 THEN 1 ELSE 0 END
    + CASE WHEN likes_received_gap > 0 THEN 1 ELSE 0 END
    + CASE WHEN topics_viewed_all_time_gap > 0 THEN 1 ELSE 0 END
    + CASE WHEN posts_read_all_time_gap > 0 THEN 1 ELSE 0 END AS num_gaps
  FROM gaps
)


SELECT 
  gaps.*,
  num_gaps.num_gaps
  FROM gaps
  LEFT JOIN num_gaps USING (user_id)
ORDER BY num_gaps
1 Like