# Encontre os usuários mais propensos a se tornarem TL3

**URL:** https://meta.discourse.org/t/find-the-users-which-are-more-likely-to-become-tl3/91495
**Category:** Data & reporting
**Tags:** trust-levels, sql-query
**Created:** [Julho 3, 2018, 4:17pm UTC](https://meta.discourse.org/t/find-the-users-which-are-more-likely-to-become-tl3/91495 "2018-07-03T16:17:14Z")
**Posts on this page:** 1
**Showing post:** 6

<div class="post-metadata">

### Author: ![Kyle\_Risi](https://avatars.discourse-cdn.com/v4/letter/k/b5ac83/32.png) [@Kyle\_Risi](https://meta.discourse.org/u/Kyle_Risi)
#### Post date: [Abril 10, 2019, 2:37pm UTC](https://meta.discourse.org/t/find-the-users-which-are-more-likely-to-become-tl3/91495/6 "2019-04-10T14:37:10Z")

</div>

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?**

> [@Trust Level 3 Requirements Data Explorer](https://meta.discourse.org/t/trust-level-3-requirements-data-explorer/113838/2):
>
> It’s all in the [`trust_level_3_requirements.rb`](https://github.com/discourse/discourse/blob/ad5edc8bb1e8d6f156f40118d36e1a154e03fad8/app/models/trust_level3_requirements.rb) file. It’s a lot of queries though…

### This is what I have so far.

 ![image](https://global.discourse-cdn.com/meta/original/3X/e/1/e19d0aca475fd790d31ad2218b071f5baee29f25.png)

### [Data Explorer](https://meta.discourse.org/t/32566?silent=true) Query

```sql
-- [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

```

---

_[View the full topic](https://meta.discourse.org/t/find-the-users-which-are-more-likely-to-become-tl3/91495)._