# Trova gli utenti più propensi a diventare 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:** [3 Luglio 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:** 7

<div class="post-metadata">

**Author:** ![bts](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/bts/32/184556_2.png) [@bts](https://meta.discourse.org/u/bts)\
**Post date:** [17 Settembre 2019, 9:29pm UTC](https://meta.discourse.org/t/find-the-users-which-are-more-likely-to-become-tl3/91495/7 "2019-09-17T21:29:55Z")

</div>

Ottimo inizio, grazie!

Ho apportato alcune modifiche/fix:

- Aggiunta la condizione ‘posts\_read \> 0’ per un calcolo più preciso delle visite degli utenti
- Rimossa ‘visits (all time)’, che non sembrava necessaria
- Corretto il calcolo di ‘topics viewed’, che utilizzava la tabella sbagliata
- Aggiunto il livello di fiducia corrente (per ottenere solo utenti tl2)
- Aggiunte clausole WHERE per altre condizioni rilevanti, impostate al 50% della soglia attuale

Ho anche parametrizzato diverse cose in modo che tu possa impostare i tuoi valori per ciascuna delle metriche richieste (poiché possono variare da forum a forum) e impostare una percentuale di soglia per mostrare solo gli utenti che soddisfano almeno quella % di TUTTE le metriche.

Ad esempio, di seguito vengono elencati per impostazione predefinita solo gli utenti tl2 che soddisfano il 50% o più di tutti i requisiti per visite, argomenti a cui hanno risposto, argomenti visualizzati, post letti… puoi impostarlo al 30% o all’85% o a qualsiasi altro valore se sembra restituire troppi o troppi pochi risultati.

Non ho aggiunto i requisiti per i like ricevuti/concessi, né per flag/silenzia/sospensioni. Per noi, almeno, queste ultime sono comunque super rare, e ritengo che i like siano una delle barriere più semplici da superare se gli utenti ne sono a conoscenza (alcuni danno like molto raramente). Quindi questo funziona molto bene per noi. Ma il resto dei requisiti potrebbe essere aggiunto se lo desideri.

A titolo di riferimento, sul nostro forum abbiamo circa 1.000 utenti TL2, circa 10 utenti TL3, e questa query identifica circa 30 utenti ‘potenziali/quasi TL3’ con la soglia del 50%.

```sql
-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 100
-- int :trust_level = 2
-- int :threshold = 50
-- int :visits = 50
-- int :topics_replied_to = 10
-- int :topics_viewed = 76
-- int :topics_viewed_all_time = 200
-- int :posts_read = 755
-- int :posts_read_all_time = 500

-- NOTE
-- trust_level mostra solo gli utenti TL2 attuali
-- threshold mostra solo gli utenti >= questa percentuale di tutte le metriche sopra
-- topics_viewed dipende dal numero totale di argomenti (predefinito 25%)
-- posts_read dipende dal numero totale di post (predefinito 25%)

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

-- Visite utente
pr AS (
SELECT user_id, 
    count(1) as visits
FROM user_visits, t
WHERE visited_at > t.start
    AND visited_at < t.end
    AND posts_read > 0
GROUP BY user_id
ORDER BY visits DESC
),

-- Argomenti a cui si è risposto
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
),

-- Argomenti visualizzati
tva AS (
SELECT user_id,
    COUNT(distinct topic_id) AS topic_id
FROM topic_views, t
WHERE viewed_at > t.start
    AND viewed_at < t.end
GROUP BY user_id
),

-- Argomenti visualizzati (tutto il tempo)
tvat AS (
SELECT user_id,
    COUNT(distinct topic_id) AS topic_id
FROM topic_views
GROUP BY user_id
),

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

-- Post letti (tutto il tempo)
prat AS (
SELECT user_id, 
    SUM(posts_read) AS posts_read
FROM user_visits, t
GROUP BY user_id
),

-- Livello di fiducia corrente
tl AS (
SELECT id,
    trust_level
FROM users
)

SELECT pr.user_id,
    -- tl.trust_level AS "Livello di fiducia",
    coalesce(pr.visits,0) AS "Visite",
    coalesce(trt.topic_id,0) AS "Argomenti a cui si è risposto",
    coalesce(tva.topic_id,0) AS "Argomenti visualizzati",
    coalesce(tvat.topic_id,0) AS "Argomenti visualizzati (TT)",
    coalesce(pra.posts_read,0) AS "Post letti",
    coalesce(prat.posts_read,0) AS "Post letti (TT)"
FROM 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 tl ON (tl.id = pr.user_id)

WHERE
tl.trust_level = :trust_level
AND pr.visits >= :visits * :threshold / 100
AND trt.topic_id >= :topics_replied_to * :threshold / 100
AND tva.topic_id >= :topics_viewed * :threshold / 100
AND tvat.topic_id >= :topics_viewed_all_time * :threshold / 100
AND pra.posts_read >= :posts_read * :threshold / 100
AND prat.posts_read >= :posts_read_all_time * :threshold / 100

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)._