Is there a method to get a sorted list of users who might become Trust Level 3?
Maybe using the Data Explorer plugin and a query (?)
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
You could do that, write a query to track the key fields and limit it to current TL2 users
There are loads of great query ideas for Data Explorer in another thread, and it seems that you could do a version of the User Directory, with the TL2 limit, to answer your query.
Still interested to know what you are trying to achieve in trying to ‘predict’ TL3 before it happens. Sounds like 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
Отличное начало, спасибо!
Я внес несколько правок и исправлений:
- Добавил условие
posts_read > 0для более точного расчёта посещений пользователей - Убрал
visits (all time), так как он, похоже, был не нужен - Исправил расчёт
topics viewed, который использовал неверную таблицу - Добавил текущий уровень доверия (чтобы получать только пользователей tl2)
- Добавил условия WHERE для других релевантных параметров, установленные на 50% от текущего порога
Также я параметризовал множество значений, чтобы вы могли задать свои собственные для каждого требуемого показателя (поскольку они могут различаться от форума к форуму), а также задать процент порога для отображения только тех пользователей, которые соответствуют как минимум этому % от ВСЕХ показателей.
Например, ниже по умолчанию отображаются только пользователи tl2, которые соответствуют 50% или более от всех требований по посещениям, темам, на которые они ответили, просмотренным темам, прочитанным сообщениям… вы можете установить это значение на 30% или 85% или любое другое, если кажется, что возвращается слишком много или слишком мало результатов.
Я не добавил требования по количеству полученных/отправленных лайков, а также по флагам/замолчаниям/блокировкам. По крайней мере, для нас последние встречаются крайне редко, а я полагаю, что лайки — это один из самых простых барьеров, который пользователи могут преодолеть, если знают о нём (некоторые люди просто редко ставят лайки). Поэтому это решение хорошо работает для нас. Однако остальные требования можно добавить, если вы захотите.
Для справки: на нашем форуме около 1000 пользователей TL2, около 10 пользователей TL3, и этот запрос с порогом 50% идентифицирует около 30 «потенциальных/почти TL3» пользователей.
-- [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
-- NOTES
-- trust_level показывать только текущих пользователей TL2
-- threshold показывать только пользователей, соответствующих >= этому проценту от всех вышеуказанных показателей
-- topics_viewed зависит от общего количества тем (по умолчанию 25%)
-- posts_read зависит от общего количества сообщений (по умолчанию 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
),
-- Посещения пользователей
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
),
-- Темы, на которые ответили
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
),
-- Просмотренные темы
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
),
-- Просмотренные темы (всё время)
tvat AS (
SELECT user_id,
COUNT(distinct topic_id) AS topic_id
FROM topic_views
GROUP BY user_id
),
-- Прочитанные сообщения
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
),
-- Прочитанные сообщения (всё время)
prat AS (
SELECT user_id,
SUM(posts_read) AS posts_read
FROM user_visits, t
GROUP BY user_id
),
-- Текущий уровень доверия
tl AS (
SELECT id,
trust_level
FROM users
)
SELECT pr.user_id,
-- tl.trust_level AS "Trust Level",
coalesce(pr.visits,0) AS "Visits",
coalesce(trt.topic_id,0) AS "Topic Replied To",
coalesce(tva.topic_id,0) AS "Topics Viewed",
coalesce(tvat.topic_id,0) AS "Topics Viewed (AT)",
coalesce(pra.posts_read,0) AS "Posts Read",
coalesce(prat.posts_read,0) AS "Posts Read (AT)"
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
Кажется, это именно то, что я искал, однако при выполнении запроса возникает следующая ошибка:
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
Есть ли идеи, как это исправить?
Здравствуйте,
В этом отчете возвращаются:
- Общее количество тем
- Общее количество тем в AT
но требования для TL3 включают:
- Общее количество тем без учета личных сообщений.
- Общее количество тем в AT без учета личных сообщений.
Кто-нибудь знает, как адаптировать запрос, чтобы исключить личные сообщения?
Заранее спасибо
Да, пробовал: 60, 80, 95, 99 — безрезультатно, всегда одно и то же сообщение об ошибке.
Вот модифицированная версия без параметров: я лениво (эффективно?) зашил требования по умолчанию для уровня доверия TL3 прямо в запрос. Я немного расширил набор данных, включив количество отданных и полученных лайков, однако лайки за уникальные дни или лайки за уникальных пользователей не включены. Флаги, муты и приостановки также по-прежнему отсутствуют.
Это отчет о пробелах, поэтому он выполняет вычитание, чтобы показать, чего не хватает каждому пользователю.
В нескольких местах результат не совсем совпадает с тем, что отображается на странице администрирования пользователя:
- отданные лайки (мой подсчет каким-то образом выше)
- посты за последние 100 дней (мой подсчет ниже)
В моем подсчете постов за последние 100 дней есть значительная доля предположений.
Но на случай, если это окажется полезным:
with
t as (
select
CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
),
-- Количество тем за последние 100 дней 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
),
-- Количество постов за последние 100 дней 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 -- Исключаем Discobot и Систему
AND (action_code is null OR action_code != 'assigned')
),
-- Пользователи
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
),
-- Темы, на которые были даны ответы
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
),
-- Темы просмотренные за все время
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
),
-- Темы просмотренные
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
),
-- Прочитанные посты
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
),
-- Прочитанные посты за все время
prat as (
select user_id,
sum(posts_read) as posts_read
from user_visits, t
group by user_id
),
-- Текущий уровень доверия
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 "Пробел по дням посещения",
greatest(10-coalesce(trt.topic_id,0), 0) as "Пробел по ответам в темах",
greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Пробел по просмотренным темам",
greatest(200-coalesce(tvat.topic_id,0),0) as "Пробел по просмотренным темам (за все время)",
greatest(pclhd.all_posts - coalesce(pra.posts_read,0),0) as "Пробел по прочитанным постам",
greatest(500-coalesce(prat.posts_read,0),0) as "Пробел по прочитанным постам (за все время)",
greatest(30-likes.likes_given,0) as "Пробел по отданным лайкам",
greatest(20-likes.likes_received,0) as "Пробел по полученным лайкам"
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
Спасибо за вашу версию!
Однако я получаю ту же ошибку
PG::QueryCanceled: ERROR: отмена оператора из-за истечения времени ожидания оператора
Похоже, у нас проблема с установкой.
Для меня сейчас это выполняется за 8379,4 мс, так что, полагаю, близко к лимиту. У вас, наверное, больше участников сообщества.
Добавление LIMIT 50 в самом конце сокращает время на 1000 мс для меня. Возможно, вам стоит поэкспериментировать с этим, пока не получите приемлемый результат.
Это немного эффективнее. Если всё ещё не работает, попробуйте удалить некоторые столбцы вместе с соответствующими соединениями и запросами.
РЕДАКТИРОВАНИЕ Хорошо, я наконец-то разобрался с типами соединений (прошло уже много времени). Этот обновлённый запрос намного эффективнее
with
t as (
select
CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
),
-- Количество тем за последние 100 дней, 25%
-- Меньшее из: 25% тем, созданных за последние 100 дней
-- ИЛИ 500, системное максимальное требование по умолчанию для 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
),
-- Количество постов за последние 100 дней, 25%
-- Меньшее из: 25% постов, созданных за последние 100 дней
-- ИЛИ 20k, системное максимальное требование по умолчанию для 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 -- Исключить Discobot и Систему
AND (action_code is null OR action_code != 'assigned')
),
-- Пользователи с уровнем доверия 2
tl AS (
SELECT id as user_id, trust_level
FROM users
WHERE trust_level = 2
),
-- Пользователи, посещения и прочитанные посты за последние 100 дней
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
),
-- Прочитанные посты за всё время
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
),
-- Темы, на которые были даны ответы
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
),
-- Просмотренные темы за всё время
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
),
-- Просмотренные темы
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 "Days visited gap",
greatest(10-coalesce(trt.topic_id,0), 0) as "Topic reply gap",
greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Topics Viewed gap",
greatest(200-coalesce(tvat.topic_id,0),0) as "Topic viewed (AT) gap",
greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "Posts Read gap",
greatest(500-coalesce(prat.posts_read,0),0) as "Posts Read (AT) gap",
greatest(30-likes.likes_given,0) as "Likes given gap",
greatest(20-likes.likes_received,0) as "Likes received gap"
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
Итак… вот отчёт о разрыве для TL2:
with
-- Пользователи с уровнем доверия 1
tl AS (
SELECT id as user_id, trust_level, last_seen_at
FROM users
WHERE trust_level = 1
),
-- Пользователи, активные за последние 3 месяца: визиты, прочитанные посты, время чтения
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
),
-- Темы, на которые были даны ответы
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
),
-- Просмотренные темы за всё время
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 "Последний визит",
-- дней посещений: 15
greatest(15-coalesce(pr.visits,0),0) as "Разрыв по дням посещений",
-- ответов в темах: 3
greatest(3-coalesce(trt.replied_count,0), 0) as "Разрыв по ответам в темах",
-- просмотренных тем: 20
greatest(20-coalesce(tvat.topic_id,0),0) as "Разрыв по просмотренным темам",
-- прочитанных постов: 100
greatest(100-coalesce(pr.posts_read,0),0) as "Разрыв по прочитанным постам",
-- время чтения постов: 60 мин
greatest(60-pr.minutes_reading_time,0) as "Разрыв по времени чтения",
-- отданных лайков: 1
greatest(1-likes.likes_given,0) as "Разрыв по отданным лайкам",
-- полученных лайков: 1
greatest(1-likes.likes_received,0) as "Разрыв по полученным лайкам"
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
Ещё раз!
Я только что понял, что лайки для TL3 считаются за последние 100 дней! 
Исправлено с учётом этого:
WITH
t as (
select
CURRENT_TIMESTAMP - ((0 + 100) * (INTERVAL '1 days')) as start,
CURRENT_TIMESTAMP - (0 * (INTERVAL '1 days')) as end
),
-- Количество тем за последние 100 дней: 25%
-- Меньшее из: 25% тем, созданных за последние 100 дней
-- ИЛИ 500 — максимальное требование по умолчанию системы для 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
),
-- Количество постов за последние 100 дней: 25%
-- Меньшее из: 25% постов, созданных за последние 100 дней
-- ИЛИ 20 000 — максимальное требование по умолчанию системы для 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 -- Исключаем Discobot и Систему
AND (action_code is null OR action_code != 'assigned')
),
-- Пользователи с уровнем доверия 2
tl AS (
SELECT id as user_id
FROM users
WHERE trust_level = 2
),
-- Пользователи + посещения и прочитанные посты за последние 100 дней
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
),
-- Прочитанные посты за всё время
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
),
-- Темы, на которые были даны ответы
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
),
-- Просмотренные темы за всё время
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
),
-- Просмотренные темы
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 "Days visited lhd gap",
greatest(10-coalesce(trt.replied_count,0), 0) as "Topic reply gap",
greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "Topics Viewed lhd gap of 150",
greatest(200-coalesce(tvat.topic_id,0),0) as "Topic viewed (AT) gap",
greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "Posts Read lhd gap of 250",
greatest(500-coalesce(prat.posts_read,0),0) as "Posts Read (AT) gap",
GREATEST(30-COALESCE(likes_given_lhd,0),0) as "Likes given lhd gap",
GREATEST(20-COALESCE(likes_received_lhd,0),0) as "Likes received lhd gap"
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 это отчёт о пробелах. Когда столбцы пусты, у пользователя нет пробелов по этому требованию. Таким образом, ваш первый пользователь почти соответствует требованиям для TL3, и его единственный пробел — это 25 лайков и 15 полученных лайков.
Понятно?
Да! Спасибо. Я настраиваю свои параметры tl3
Я изучаю, имеют ли смысл настройки уровня доверия 3 (TL3) здесь, на Meta (и в конечном итоге какие значения должны быть по умолчанию).
Я использовал ваш запрос как отправную точку и обновил его, чтобы он ссылался на настройки сайта, так что вы можете выполнить его, если измените свои настройки:
Посмотрите на новый 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
),
-- Темы, просмотренные за период времени
-- меньшее из: x% (по умолчанию 25) тем, созданных за период времени (по умолчанию 100 дней)
-- ИЛИ лимит (по умолчанию 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
),
-- Прочитанные посты за период времени
-- меньшее из: x% (по умолчанию 25) тем, созданных за период времени (по умолчанию 100 дней)
-- ИЛИ лимит (по умолчанию 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 -- Исключаем Discobot и Систему
AND (action_code IS NULL OR action_code != 'assigned')
),
-- Пользователи уровня доверия 2
tl AS (
SELECT id AS user_id, username
FROM users
WHERE trust_level = 2
),
-- Пользователи + визиты и прочитанные посты за последние 100 дней
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
),
-- Прочитанные посты за всё время
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
),
-- Темы, на которые были даны ответы
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
),
-- Темы, просмотренные за всё время
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
),
-- Темы, просмотренные за период времени
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 "Разрыв по дням посещения за период",
GREATEST(tl3_requires_topics_replied_to-COALESCE(trt.replied_count,0), 0) as "Разрыв по ответам на темы",
GREATEST(tvtp.all_topics-COALESCE(tva.topic_id,0),0) AS "Разрыв по просмотренным темам за период (150)",
GREATEST(tl3_requires_topic_viewed_all_time-COALESCE(tvat.topic_id,0),0) as "Разрыв по просмотренным темам (за всё время)",
GREATEST(prtp.all_posts - COALESCE(pr.posts_read,0),0) as "Разрыв по прочитанным постам за период (250)",
GREATEST(tl3_requires_posts_read_all_time-COALESCE(prat.posts_read,0),0) as "Разрыв по прочитанным постам (за всё время)",
GREATEST(tl3_promotion_likes_given-COALESCE(likes_given_lhd,0),0) as "Разрыв по отданным лайкам за период",
GREATEST(tl3_promotion_likes_received-COALESCE(likes_received_lhd,0),0) as "Разрыв по полученным лайкам за период"
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