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 (?)
8 إعجابات
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)
8 إعجابات
I thought about TL3 Requirements
Checking which users have the most number of 
Requirements and sort by that
4 إعجابات
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 
8 إعجابات
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.
إعجاب واحد (1)
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
8 إعجابات
Great start here, thanks!
I made a few tweaks / fixes:
- Added ‘posts_read > 0’ condition for more accurate user visits calculation
- Removed ‘visits (all time)’ which didn’t seem to be necessary
- Fixed ‘topics viewed’ calculations which was using the wrong table
- Added current trust level (to only get tl2 users)
- Added where clauses for other relevant conditions, set at 50% of current threshold
Also parameterized a bunch of things so you can set your own values for each of the required metrics (since they may vary forum by forum), and also set a threshold percentage to show only users who meet at least that % of ALL the metrics.
So for example the below by default lists only tl2 users who meet 50% or more of all the requirements for visits, topics replied to, topics viewed, posts read…you could set it to 30% or 85% or whatever if it seems to be returning too many or too few results.
I did not add the requirements for likes given/received, or for flags/silences/suspensions. For us at least, the latter are super rare anyway, and I figure likes is one of the easier barriers to get people over if they know about it (some people just barely ever give likes). So this works pretty well for us. But the rest of the requirements could be added if you wanted.
For reference, on our forum we have ~1,000 TL2 users, ~10 TL3 users, and this query identifies ~30 ‘potential/almost TL3’ users with the 50% threshold.
-- [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 show current TL2 users only
-- threshold show only at users >= this percentage of all above metrics
-- topics_viewed depends on total # of topics (default 25%)
-- posts_read depends on total # of posts (default 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
),
-- User Visits
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
),
-- 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
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
),
-- Topics Viewed (All Time)
tvat AS (
SELECT user_id,
COUNT(distinct topic_id) AS topic_id
FROM topic_views
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
),
-- Current Trust Level
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
8 إعجابات
This seems to be exactly what I am searching for, however, I get the following error when executing the query:
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
Any ideas how to make this work?
3 إعجابات
Hello,
This report returns
- Total topics
- Total topics in AT
but the requirements for TL3 use
- Total topics excluding private messages.
- Total topics in AT excluding private messages.
Anyone knows how to adapt the query to exclude the private messages?
Thanks in advance
إعجابَين (2)
Yes, tried that: 60, 80, 95, 99 → no effect at all, always the same error message.
إعجابَين (2)
هذه نسخة معدلة بدون معلمات: لقد قمت بتشفير متطلبات 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
5 إعجابات
شكرًا على نسختك!
ومع ذلك، ما زلت أتلقى نفس الخطأ
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
أعتقد أن لدينا مشكلة في تثبيتنا إذن.
4 إعجابات
بالنسبة لي، هذا يعمل حاليًا في 8,379.4 مللي ثانية، لذا فهو قريب من الحد، على ما أعتقد. يجب أن يكون لديك مجتمع أكبر.
إضافة LIMIT 50 في النهاية تقطع 1000 مللي ثانية بالنسبة لي. ربما يمكنك اللعب بهذا حتى تحصل على شيء ما.
5 إعجابات
هذا أكثر كفاءة إلى حد ما. إذا لم ينجح الأمر معك بعد، يمكنك محاولة إزالة بعض الأعمدة، مع عمليات الربط والاستعلامات المرتبطة بها.
تعديل حسنًا، لقد استقمت أخيرًا مع أنواع الربط (لقد مر وقت طويل). هذا الاستعلام المحدث أكثر كفاءة بكثير.
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 ألف، الحد الأقصى الافتراضي للنظام المطلوب لـ 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 "فجوة أيام الزيارة",
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(pr.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 prat using (user_id)
LEFT JOIN likes using (user_id)
ORDER BY
pr.visits DESC
LIMIT 50
8 إعجابات
وهنا… تقرير فجوة 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
3 إعجابات
مرة أخرى!
لقد أدركت للتو أن الإعجابات لـ 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 ألف، الحد الأقصى الافتراضي للنظام المطلوب لـ 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 "فجوة أيام الزيارة لآخر 100 يوم",
greatest(10-coalesce(trt.replied_count,0), 0) as "فجوة الرد على المواضيع",
greatest(tclhd.all_topics-coalesce(tva.topic_id,0),0) AS "فجوة عرض المواضيع لآخر 100 يوم من 150",
greatest(200-coalesce(tvat.topic_id,0),0) as "فجوة عرض المواضيع (طوال الوقت)",
greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "فجوة المشاركات المقروءة لآخر 100 يوم من 250",
greatest(500-coalesce(prat.posts_read,0),0) as "فجوة المشاركات المقروءة (طوال الوقت)",
GREATEST(30-COALESCE(likes_given_lhd,0),0) as "فجوة الإعجابات المقدمة لآخر 100 يوم",
GREATEST(20-COALESCE(likes_received_lhd,0),0) as "فجوة الإعجابات المستلمة لآخر 100 يوم"
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
6 إعجابات
@alefattorini إنه تقرير فجوة. عندما تكون الأعمدة فارغة، لا يمتلك المستخدم فجوة لهذا المتطلب. لذا فإن المستخدم الأول الخاص بك مؤهل تقريبًا لـ TL3 وفجواته الوحيدة هي إعطاء 25 إعجابًا وتلقي 15.
هل هذا منطقي؟
5 إعجابات
نعم! شكراً لك. أقوم بتعديل معلمات tl3 الخاصة بي
إعجابَين (2)