# 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:** 13

<div class="post-metadata">

**Author:** ![ganncamp](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/ganncamp/32/106199_2.png) [@ganncamp](https://meta.discourse.org/u/ganncamp)\
**Post date:** [Março 10, 2022, 7:15pm UTC](https://meta.discourse.org/t/find-the-users-which-are-more-likely-to-become-tl3/91495/13 "2022-03-10T19:15:14Z")

</div>

Aqui está uma versão modificada sem parâmetros: Codifiquei preguiçosamente (eficientemente?) os requisitos padrão do TL3 na consulta. Expandi o conjunto de dados ligeiramente para incluir curtidas dadas e recebidas, embora não curtidas/dias únicos ou curtidas/usuários únicos. Bandeiras, silêncios e suspensões também ainda estão faltando.

Este é um relatório de lacunas, portanto, ele faz a subtração para mostrar o que cada usuário está perdendo.  
Em alguns lugares, ele não corresponde exatamente ao que aparece na página de administração do usuário:

- curtidas dadas (minha contagem é de alguma forma maior)
- posts nos últimos 100 dias (minha contagem é menor)

Há uma boa dose de suposição na minha contagem de posts dos últimos 100 dias.

Mas, caso seja útil:

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

),

-- Contagem de tópicos nos últimos 100 dias 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
),

-- Contagem de posts nos últimos 100 dias 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 -- Omitir Discobot & Sistema
    AND (action_code is null OR action_code != 'assigned')

),

-- Usuários
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
),

-- Tópicos respondidos
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
),

-- Tópicos Visualizados Todos os Tempos
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
),

-- Tópicos Visualizados
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 Lidos
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 Lidos Todos os Tempos
prat as (
    select user_id,
        sum(posts_read) as posts_read
    from user_visits, t
    group by user_id
),

-- Nível de Confiança Atual
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 "Lacuna de dias visitados",
        greatest(10-coalesce(trt.topic_id,0), 0) as "Lacuna de respostas em tópicos",
        greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Lacuna de tópicos visualizados",
        greatest(200-coalesce(tvat.topic_id,0),0) as "Lacuna de tópicos visualizados (AT)",
        greatest(pclhd.all_posts - coalesce(pra.posts_read,0),0) as "Lacuna de posts lidos",
        greatest(500-coalesce(prat.posts_read,0),0) as "Lacuna de posts lidos (AT)",
        greatest(30-likes.likes_given,0) as "Lacuna de curtidas dadas",
        greatest(20-likes.likes_received,0) as "Lacuna de curtidas recebidas"

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

```

---

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