Find the users which are more likely to become TL3

data & reporting
,
6

I have the beginnings of this progress towards level 3 report which allows admins to view the progress of users so far, which I want to then use to email out messages of encouragement to users who are close (We like to promote TL3 user who share our tone of voice to moderators)

Someone kindly sent me the the trust level 3 requirements rb file which has helped a lot. however my limited knowledge and understanding how to convert the fields within the document into sql is limited, Maybe someone can help finish it off?

This is what I have so far.

image
image894×784 42.3 KB

Data Explorer Query

-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 100

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

),

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

-- Visits (all time)
vi as (
    select user_id, 
        count(1) as visits
    from user_visits, t
    group by user_id
),

-- 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
tva as (
    select user_id,
           count(topic_id) as topic_id
    from posts
    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
)



SELECT  pr.user_id,
        coalesce(pr.visits,0) as "Visits",
        coalesce(trt.topic_id,0) as "Topic replied to",
        coalesce(tva.topic_id,0) as "Topic viewed (AT)",
        coalesce(pra.posts_read,0) as "Posts Read",
        coalesce(prat.posts_read,0) as "Posts Read (AT)"
    

FROM pr
left join vi using (user_id)
left join trt using (user_id)
left join tva using (user_id)
left join pra using (user_id)
left join prat using (user_id)





ORDER BY
  pr.visits DESC
8 Likes