Existe-t-il une méthode pour obtenir une liste triée des utilisateurs susceptibles d’atteindre le niveau de confiance 3 ?
Peut-être en utilisant le plugin Data Explorer et une requête (?)
That’s an interesting question. There will be no specific query since each member ‘earns’ TL3 through interaction and engagement, but you could monitor the TL2 users to see who are visiting most regularly and engaging with more content I suppose.
Is there a reason to find this in advance rather than waiting to see who earns it?
Some third party measurement tools create ‘leaderboards’ that might point to members who have been particularly active. Is that what you need?
In the past I have also created queries in excel based on data exported from the Users table and custom Data Explorer queries. I didn’t look at what you are asking, but did create monitoring tools to look at different types of activity, such as reading and posting, to better segment my members.
Let us know what you are trying to achieve and maybe we could come up with cleverer suggestions.
(also, this should probably be moved to community where we can discuss these topics)
I thought about TL3 Requirements
Checking which users have the most number of 
Requirements and sort by that
Vous pourriez le faire, en écrivant une requête pour suivre les champs clés et la limiter aux utilisateurs TL2 actuels.
Il existe de nombreuses idées de requêtes excellentes pour Data Explorer dans un autre fil de discussion, et il semble que vous puissiez créer une version du Répertoire des utilisateurs, avec la limite TL2, pour répondre à votre question.
Je suis toujours curieux de savoir ce que vous essayez d’accomplir en essayant de « prédire » le niveau TL3 avant qu’il ne se produise. Cela ressemble à Minority Report 
Though I have no doubt that some form of query could be put together, I have serious doubt that the amount of work needed would justify the questionable value of the results.
It is one thing to do this per user from a members admin user page, a whole different story for many accounts all at once.
At best, there will be many “moving parts” to take into consideration and arbitrary values to be decided on.
Some criteria could be used to reduce the “haystack”. i.e. only TL2 accounts that are not already TL3, accounts that are activated and not suspended. That might help somewhat.
Because many of the requirements may have been tweaked from their default values, those values would be needed to base a members values against.
Even then, most member values are unpredictable and unstable. eg. Likes could be given / received at any time changing a “0 - requirements not met” to “requirements met” in a heartbeat. Similar with flags given / received.
And what constitutes an “almost TL3” state? How many of the 12 requirements are already met? A percentage? eg.
if (value < requirement)
&& ((value / requirement) > arbitrary_percent) {
The “all time” values should in theory stagnate or increase only. But the “100 days” could be a problem. Should an algorithm somehow “drop” values associated with older days when it is trying to predict values for future days?
Anyway, long story short, if you can put together exact detailed specifications for how such a feature could work it would make it easier for someone to come up with the code needed to meet those specs.
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.
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
Excellent début, merci !
J’ai apporté quelques ajustements / corrections :
- Ajout de la condition ‘posts_read > 0’ pour un calcul plus précis des visites utilisateurs
- Suppression de ‘visites (toutes périodes)’, qui ne semblait pas nécessaire
- Correction du calcul de ‘sujets consultés’, qui utilisait la mauvaise table
- Ajout du niveau de confiance actuel (pour ne sélectionner que les utilisateurs tl2)
- Ajout de clauses WHERE pour d’autres conditions pertinentes, fixées à 50 % du seuil actuel
J’ai également paramétré un certain nombre d’éléments afin que vous puissiez définir vos propres valeurs pour chacune des métriques requises (puisque celles-ci peuvent varier d’un forum à l’autre), ainsi qu’un pourcentage de seuil pour n’afficher que les utilisateurs répondant à au moins ce pourcentage de TOUTES les métriques.
Par exemple, ci-dessous, par défaut, la requête liste uniquement les utilisateurs tl2 qui répondent à 50 % ou plus de l’ensemble des exigences concernant les visites, les sujets auxquels ils ont répondu, les sujets consultés, les messages lus… Vous pouvez ajuster ce seuil à 30 %, 85 % ou autre, si les résultats semblent trop nombreux ou trop peu nombreux.
Je n’ai pas ajouté les exigences relatives aux likes envoyés/reçus, ni aux signalements/mises en silence/suspensions. Chez nous, du moins, ces derniers sont extrêmement rares, et je suppose que les likes constituent l’un des obstacles les plus faciles à franchir pour les utilisateurs qui en ont connaissance (certains n’envoient des likes que très rarement). Cela fonctionne donc très bien pour nous. Mais vous pouvez ajouter les autres exigences si vous le souhaitez.
À titre de référence, sur notre forum, nous avons environ 1 000 utilisateurs TL2, environ 10 utilisateurs TL3, et cette requête identifie environ 30 utilisateurs « potentiels / presque TL3 » avec un seuil de 50 %.
-- [paramètres]
-- 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
-- NOTES
-- trust_level afficher uniquement les utilisateurs TL2 actuels
-- threshold afficher uniquement les utilisateurs >= ce pourcentage de toutes les métriques ci-dessus
-- topics_viewed dépend du nombre total de sujets (défaut 25 %)
-- posts_read dépend du nombre total de messages (défaut 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
),
-- Visites utilisateurs
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
),
-- Sujets auxquels on a répondu
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
),
-- Sujets consultés
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
),
-- Sujets consultés (toutes périodes)
tvat AS (
SELECT user_id,
COUNT(distinct topic_id) AS topic_id
FROM topic_views
GROUP BY user_id
),
-- Messages lus
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
),
-- Messages lus (toutes périodes)
prat AS (
SELECT user_id,
SUM(posts_read) AS posts_read
FROM user_visits, t
GROUP BY user_id
),
-- Niveau de confiance actuel
tl AS (
SELECT id,
trust_level
FROM users
)
SELECT pr.user_id,
-- tl.trust_level AS "Niveau de confiance",
coalesce(pr.visits,0) AS "Visites",
coalesce(trt.topic_id,0) AS "Sujets auxquels on a répondu",
coalesce(tva.topic_id,0) AS "Sujets consultés",
coalesce(tvat.topic_id,0) AS "Sujets consultés (TP)",
coalesce(pra.posts_read,0) AS "Messages lus",
coalesce(prat.posts_read,0) AS "Messages lus (TP)"
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
Cela semble être exactement ce que je recherche, mais je rencontre l’erreur suivante lors de l’exécution de la requête :
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
Avez-vous des idées pour faire fonctionner cela ?
Bonjour,
Ce rapport renvoie :
- Le nombre total de sujets
- Le nombre total de sujets dans AT
mais les exigences pour TL3 utilisent :
- Le nombre total de sujets à l’exclusion des messages privés.
- Le nombre total de sujets dans AT à l’exclusion des messages privés.
Quelqu’un sait comment adapter la requête pour exclure les messages privés ?
Merci d’avance
Oui, j’ai essayé : 60, 80, 95, 99 → aucun effet, toujours le même message d’erreur.
Voici une version modifiée sans paramètres : J’ai codé en dur (avec paresse ou efficacité ?) les exigences par défaut du TL3 dans la requête. J’ai légèrement élargi l’ensemble de données pour inclure les likes donnés et reçus, bien que pas les likes/jours uniques ou les likes/utilisateurs uniques. Les drapeaux, silences et suspensions sont également toujours manquants.
Il s’agit d’un rapport d’écart, il effectue donc la soustraction pour vous montrer ce qui manque à chaque utilisateur.
Dans quelques endroits, il ne correspond pas exactement à ce qui apparaît sur la page d’administration de l’utilisateur :
- likes donnés (mon décompte est étrangement plus élevé)
- posts des 100 derniers jours (mon décompte est plus bas)
Il y a une bonne dose de conjectures dans mon décompte des posts des 100 derniers jours.
Mais au cas où ce serait utile :
with
t as (
select
CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
),
-- Nombre de sujets des 100 derniers jours 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
),
-- Nombre de posts des 100 derniers jours 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 -- Omettre Discobot & Système
AND (action_code is null OR action_code != 'assigned')
),
-- Utilisateurs
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
),
-- Sujets auxquels il a été répondu
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
),
-- Sujets consultés tout le temps
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
),
-- Sujets consultés
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 lus
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 lus tout le temps
prat as (
select user_id,
sum(posts_read) as posts_read
from user_visits, t
group by user_id
),
-- Niveau de confiance actuel
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 "Écart de jours visités",
greatest(10-coalesce(trt.topic_id,0), 0) as "Écart de réponses aux sujets",
greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Écart de sujets consultés",
greatest(200-coalesce(tvat.topic_id,0),0) as "Écart de sujets consultés (AT)",
greatest(pclhd.all_posts - coalesce(pra.posts_read,0),0) as "Écart de posts lus",
greatest(500-coalesce(prat.posts_read,0),0) as "Écart de posts lus (AT)",
greatest(30-likes.likes_given,0) as "Écart de likes donnés",
greatest(20-likes.likes_received,0) as "Écart de likes reçus"
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
Merci pour votre version !
Cependant, je reçois la même erreur
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
Je suppose que nous avons alors un problème avec notre installation.
Pour moi, cela s’exécute actuellement en 8 379,4 ms, donc proche de la limite, je suppose. Vous devez avoir une communauté plus importante.
L’ajout de LIMIT 50 tout à la fin me fait gagner 1 000 ms. Peut-être pourriez-vous jouer avec cela jusqu’à obtenir quelque chose en retour.
Ceci est légèrement plus efficace. Si cela ne fonctionne toujours pas pour vous, vous pouvez essayer de supprimer certaines colonnes, avec leurs jointures et requêtes associées.
EDIT Bon, j’ai enfin compris mes types de jointures (ça fait un moment). Cette requête mise à jour est beaucoup plus efficace.
with
t as (
select
CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
),
-- Nombre de sujets des 100 derniers jours 25%
-- le plus petit de 25% des sujets créés ces 100 derniers jours
-- OU 500, le nombre maximum par défaut du système requis pour TL3
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
),
-- Nombre de messages des 100 derniers jours 25%
-- le plus petit de 25% des messages créés ces 100 derniers jours
-- OU 20k, le nombre maximum par défaut du système requis pour TL3
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 -- Omettre Discobot et le Système
AND (action_code is null OR action_code != 'assigned')
),
-- Utilisateurs de niveau de confiance 2
tl AS (
SELECT id as user_id, trust_level
FROM users
WHERE trust_level = 2
),
-- Utilisateurs, visites et messages lus ces 100 derniers jours
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
),
-- Messages lus tout le temps
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
),
-- Sujets auxquels il a été répondu
trt as (
select user_id,
count(distinct topic_id) as topic_id
from t, posts
INNER JOIN tl using (user_id)
where posts.created_at > t.start
and posts.created_at < t.end
group by user_id
),
-- Sujets consultés tout le temps
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
),
-- Sujets consultés
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 AS (
SELECT user_id,
likes_given, likes_received
from user_stats
INNER JOIN tl using (user_id)
)
SELECT pr.user_id,
greatest(50-coalesce(pr.visits,0),0) as "Écart de visites",
greatest(10-coalesce(trt.topic_id,0), 0) as "Écart de réponses aux sujets",
greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Écart de sujets consultés",
greatest(200-coalesce(tvat.topic_id,0),0) as "Écart de sujets consultés (AT)",
greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "Écart de messages lus",
greatest(500-coalesce(prat.posts_read,0),0) as "Écart de messages lus (AT)",
greatest(30-likes.likes_given,0) as "Écart de likes donnés",
greatest(20-likes.likes_received,0) as "Écart de likes reçus"
FROM pclhd, tclhd, pr
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 using (user_id)
ORDER BY
pr.visits DESC
LIMIT 50
Et… voici le rapport d’écart TL2 :
with
-- Utilisateurs Trust Level 1
tl AS (
SELECT id as user_id, trust_level, last_seen_at
FROM users
WHERE trust_level = 1
),
-- Utilisateurs vus au cours des 3 derniers mois + visites, articles lus, temps de lecture
pr AS (
SELECT user_id,
count(1) as visits,
sum(posts_read) as posts_read,
SUM(time_read)/60 as minutes_reading_time,
DATE(last_seen_at) AS last_seen
FROM user_visits
INNER JOIN tl using (user_id)
WHERE DATE(last_seen_at) >= CURRENT_DATE - INTERVAL '3 month'
GROUP BY user_id, last_seen
ORDER BY visits, last_seen DESC
),
-- Sujets auxquels il a été répondu
trt as (
select posts.user_id,
count(distinct topic_id) as replied_count
from posts
INNER JOIN tl using (user_id)
INNER JOIN topics ON topics.id = posts.topic_id
WHERE topics.user_id <> posts.user_id
AND posts.deleted_at IS NULL AND topics.deleted_at IS NULL
-- AND topics.archetype <> 'private_message'
AND archetype = 'regular'
GROUP BY posts.user_id
ORDER BY replied_count DESC
),
-- Sujets vus tout le temps
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
),
likes AS (
SELECT user_id,
likes_given, likes_received
from user_stats
INNER JOIN tl using (user_id)
)
SELECT pr.user_id,
pr.last_seen as "Dernière connexion",
-- jours visités : 15
greatest(15-coalesce(pr.visits,0),0) as "Écart jours visités",
-- réponses aux sujets : 3
greatest(3-coalesce(trt.replied_count,0), 0) as "Écart réponses sujet",
-- sujets entrés : 20
greatest(20-coalesce(tvat.topic_id,0),0) as "Écart sujets vus",
-- articles lus : 100
greatest(100-coalesce(pr.posts_read,0),0) as "Écart articles lus",
-- temps passé à lire les articles : 60min
greatest(60-pr.minutes_reading_time,0) as "Écart temps de lecture",
-- likes donnés : 1
greatest(1-likes.likes_given,0) as "Écart likes donnés",
-- likes reçus : 1
greatest(1-likes.likes_received,0) as "Écart likes reçus"
FROM pr
left join trt using (user_id)
left join tvat using (user_id)
LEFT JOIN likes using (user_id)
ORDER BY
pr.visits DESC
LIMIT 500
Encore une fois !
Je viens de réaliser que les likes pour TL3 sont sur les 100 derniers jours ! 
Corrigé pour cela :
WITH
t as (
select
CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
),
-- Nombre de sujets des 100 derniers jours 25%
-- le moindre de 25% des sujets créés au cours des 100 derniers jours
-- OU 500, l'exigence maximale par défaut du système pour TL3
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
),
-- Nombre de messages des 100 derniers jours 25%
-- le moindre de 25% des messages créés au cours des 100 derniers jours
-- OU 20k, l'exigence maximale par défaut du système pour TL3
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 -- Omettre Discobot et le système
AND (action_code is null OR action_code != 'assigned')
),
-- Utilisateurs de niveau de confiance 2
tl AS (
SELECT id as user_id
FROM users
WHERE trust_level = 2
),
-- Utilisateurs + visites et messages lus au cours des 100 derniers jours
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
),
-- Messages lus (toutes périodes confondues)
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
),
-- Sujets auxquels il a été répondu
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
),
-- Sujets vus (toutes périodes confondues)
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
),
-- Sujets vus
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
)
SELECT pr.user_id,
greatest(50-coalesce(pr.visits,0),0) as "Jours de visite lhd écart",
greatest(10-coalesce(trt.replied_count,0), 0) as "Écart de réponse au sujet",
greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Écart de sujets vus lhd de 150",
greatest(200-coalesce(tvat.topic_id,0),0) as "Écart de sujets vus (AT)",
greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "Écart de messages lus lhd de 250",
greatest(500-coalesce(prat.posts_read,0),0) as "Écart de messages lus (AT)",
GREATEST(30-COALESCE(likes_given_lhd,0),0) as "Écart de likes donnés lhd",
GREATEST(20-COALESCE(likes_received_lhd,0),0) as "Écart de likes reçus lhd"
FROM pclhd, tclhd, pr
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)
ORDER BY pr.visits DESC
LIMIT 25
@alefattorini c’est un rapport d’écart. Lorsque les colonnes sont vides, l’utilisateur n’a pas d’écart pour cette exigence. Donc, votre premier utilisateur est presque qualifié pour TL3 et son seul écart est de donner 25 likes et de recevoir 15.
Est-ce que ça a du sens ?
Oui ! Merci. Je suis en train d’ajuster mes paramètres tl3.
J’explore si nos paramètres de niveau de confiance 3 ont un sens ici sur meta (et finalement quelles devraient être nos valeurs par défaut)
J’ai utilisé votre requête comme point de départ, et je l’ai mise à jour pour faire référence aux paramètres du site afin que vous puissiez l’exécuter si vous modifiez vos paramètres :
Voir la nouvelle CTE tl3_reqs :
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 users
WHERE trust_level = 2
),
-- 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
)
SELECT pr.user_id,
tl.username,
GREATEST(tl3_days_visited-COALESCE(pr.visits,0),0) as "Days visited time period gap",
GREATEST(tl3_requires_topics_replied_to-COALESCE(trt.replied_count,0), 0) as "Topic reply gap",
GREATEST(tvtp.all_topics-COALESCE(tva.topic_id,0),0) AS "Topics Viewed time period gap of 150",
GREATEST(tl3_requires_topic_viewed_all_time-COALESCE(tvat.topic_id,0),0) as "Topic viewed (AT) gap",
GREATEST(prtp.all_posts - COALESCE(pr.posts_read,0),0) as "Posts Read lhd gap of 250",
GREATEST(tl3_requires_posts_read_all_time-COALESCE(prat.posts_read,0),0) as "Posts Read (AT) gap",
GREATEST(tl3_promotion_likes_given-COALESCE(likes_given_lhd,0),0) as "Likes given time period gap",
GREATEST(tl3_promotion_likes_received-COALESCE(likes_received_lhd,0),0) as "Likes received lhd 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)
ORDER BY pr.visits DESC
LIMIT 25