Find the users which are more likely to become TL3

データとレポート
,
19

もう一度！

TL3のいいねは過去100日間のものだと気づいたばかりです！:sadpanda:

それを修正しました：

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
「いいね！」 6