TL3 になる可能性が高いユーザーを見つける

[barreeeiroo (Diego Barreiro)]

Trust Level 3 に到達する可能性があるユーザーのソート済みリストを取得する方法はありますか？
Data Explorer プラグインとクエリ (?) を使用する方法かもしれません。

[robmc (Robert McIntosh)]

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)

[barreeeiroo (Diego Barreiro)]

I thought about TL3 Requirements
Checking which users have the most number of Requirements and sort by that

[robmc (Robert McIntosh)]

その方法も可能です。主要なフィールドを追跡するクエリを作成し、現在の TL2 ユーザーに限定することもできます。

別のスレッドには、Data Explorer 向けの素晴らしいクエリ案が多数あります。TL2 に制限をかけたユーザーディレクトリのようなバージョンを作成すれば、ご質問への回答が得られるかもしれません。

Recreate the Discourse user’s directory

Recreate the Discourse user’s directory OK, here is the query to recreate the user’s directory. -- [params] -- null int :period SELECT users.username AS "Username", directory_items.likes_received AS "Likes Received", directory_items.likes_given AS "Likes Given", directory_items.topic_count AS "Topics Created", directory_items.post_count AS "Replied", directory_items.days_visited AS "Vists", directory_items.topics_entered AS "Viewed", directory_items.posts_read AS "Read" FROM users JOIN direct…

TL3 に到達する前に「予測」しようとして、何を達成しようとしているのか、まだ興味があります。『マイノリティ・リポート』のようですしね

[Mittineague]

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.

[Kyle_Risi]

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?

@zogstrip

It’s all in the trust_level_3_requirements.rb file. It’s a lot of queries though…

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

[bts (Brendan Schlagel)]

素晴らしいスタートですね、ありがとうございます！

いくつかの調整と修正を行いました：

• より正確なユーザー訪問数の計算のために ‘posts_read > 0’ の条件を追加
• 不要だった ‘visits (all time)’ を削除
• 誤ったテーブルを使用していた ‘topics viewed’ の計算を修正
• 現在の信頼レベル（tl2 ユーザーのみを取得するため）を追加
• 他の関連条件に対して WHERE 句を追加し、現在の閾値の 50% に設定

また、必要な各指標の値を独自に設定できるようパラメータ化しました（フォーラムごとに異なる可能性があるため）。さらに、すべての指標の少なくとも指定された割合を満たすユーザーのみを表示するための閾値パーセンテージも設定可能です。

例えば、以下はデフォルトで、訪問数、返信したトピック数、閲覧したトピック数、読んだ投稿数などのすべての要件の 50% 以上を満たす tl2 ユーザーのみをリストします。結果が多すぎる、あるいは少なすぎる場合は、30% や 85% など、任意の値に設定することもできます。

いいね（受け取り/送信）やフラグ/沈黙/停止に関する要件は追加していません。少なくとも私たちの場合、後者は非常に稀です。また、いいねは、知られていれば乗り越えやすいハードルの一つだと考えています（いいねをほとんど与えない人もいます）。そのため、この方法は私たちの場合非常にうまく機能しています。ただし、必要に応じて他の要件も追加可能です。

参考までに、私たちのフォーラムには約 1,000 人の 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

-- 注釈
-- 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

[tmomas]

これはまさに探していたもののように思えますが、クエリを実行すると以下のエラーが発生します：

PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

これを動作させるためのアイデアはありますか？

[Cazadividendos (Luis álvarez)]

こんにちは、

このレポートは以下の値を返します。

• トピックの総数
• AT のトピック総数

しかし、TL3 の要件では以下の値が必要です。

• プライベートメッセージを除いたトピックの総数。
• プライベートメッセージを除いたAT のトピック総数。

クエリを調整してプライベートメッセージを除外する方法をご存知の方はいらっしゃいますか？

あらかじめありがとうございます。

[tmomas]

@anon54446237

しきい値パラメータを上げましたか？

はい、試しました：60、80、95、99 → 全く効果はなく、常に同じエラーメッセージが表示されます。

[ganncamp (G Ann Campbell)]

パラメータなしの修正版です。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 & System を除外
    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

[tmomas]

バージョンありがとうございます！
しかし、同じエラーが返ってきます。

PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

インストールに問題があるようですね。

[ganncamp (G Ann Campbell)]

私の場合、これは現在 8,379.4 ミリ秒で実行されており、制限に近い値だと思います。コミュニティが大きいのでしょう。

最後に LIMIT 50 を追加すると、私の場合は 1k ミリ秒短縮されます。何か結果が得られるまで、それで調整してみてはいかがでしょうか。

[ganncamp (G Ann Campbell)]

これで わずかに 効率が向上しました。それでも実行できない場合は、一部の列を削除し、関連する結合とクエリを削除してみてください。

編集
JOIN の種類をようやく理解しました（しばらくぶりです）。この更新されたクエリは はるかに 効率的です。

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

-- 過去100日間のトピック数 25%
-- 過去100日間に作成されたトピックの25%の小さい方
-- または 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%
-- 過去100日間に作成された投稿の25%の小さい方
-- または 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 & System を除く
    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 "閲覧トピック(AT)ギャップ",
        greatest(pclhd.all_posts - coalesce(pr.posts_read,0),0) as "投稿閲覧ギャップ",
        greatest(500-coalesce(prat.posts_read,0),0) as "投稿閲覧(AT)ギャップ",
        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

[ganncamp (G Ann Campbell)]

そして… TL2ギャップレポートです。

with

-- Trust Level 1 users
tl AS (
    SELECT id as user_id, trust_level, last_seen_at
    FROM users
    WHERE trust_level = 1
),

-- Users seen in last 3mo + visits, posts read, reading time
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
),

-- Topics replied to
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
),

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

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 "最終ログイン",
        -- days visited: 15
        greatest(15-coalesce(pr.visits,0),0) as "訪問日数ギャップ",
        -- topic replies: 3
        greatest(3-coalesce(trt.replied_count,0), 0)  as "トピック返信ギャップ",
        -- topics entered: 20
        greatest(20-coalesce(tvat.topic_id,0),0) as "トピック閲覧ギャップ",
        -- posts read: 100
        greatest(100-coalesce(pr.posts_read,0),0) as "投稿閲覧ギャップ",
        -- time spent reading posts: 60min
        greatest(60-pr.minutes_reading_time,0) as "読書時間ギャップ",
        -- likes given: 1
        greatest(1-likes.likes_given,0) as "いいね送信ギャップ",
        -- likes received: 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

[ganncamp (G Ann Campbell)]

もう一度！

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%
-- 過去100日間に作成されたトピックの25%の少ない方
-- または、TL3のシステムデフォルト最大要件である500
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%
-- 過去100日間に作成された投稿の25%の少ない方
-- または、TL3のシステムデフォルト最大要件である20k
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

[alefattorini (Alessio Fattorini)]

@ganncamp

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

-- Topic count last 100 days 25%
-- lesser of 25% topics created in last 100 days
-- OR 500, the system default max requirement for 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
),

-- Post count last 100 days 25%
-- lesser of 25% posts created in last 100 days
-- OR 20k, the system default max requirement for 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  -- Omit Discobot & System
        AND (action_code is null OR action_code != 'assigned')
),

-- Trust Level 2 users
tl AS (
    SELECT id as user_id
    FROM users
    WHERE trust_level = 2
),

-- Users + visits and 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,
        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

ありがとうございます！データがいくつか不足しているようです。

[ganncamp (G Ann Campbell)]

@alefattorini これはギャップレポートです。列が空の場合、ユーザーはその要件にギャップがありません。したがって、最初のユーザーはTL3の資格がほぼあり、ギャップは25件の「いいね！」を付け、15件を受け取ることだけです。

意味が通じますか？

[alefattorini (Alessio Fattorini)]

はい、ありがとうございます。tl3パラメータを調整しています。

[mcwumbly (Dave McClure)]

メタでのトラストレベル3の設定が妥当かどうか（そして最終的にデフォルト値をどうするか）を検討しています。

あなたのクエリを基点として、設定を変更すれば実行できるようにサイト設定を参照するように更新しました。

新しい tl3_reqs CTE を参照してください。

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