Find the users which are more likely to become TL3

커뮤니티 구축 데이터 및 보고
,
17

이 방법은 조금 더 효율적입니다. 그래도 실행이 안 된다면, 관련 조인 및 쿼리와 함께 일부 열을 제거해 보실 수 있습니다.

수정 자, 마침내 조인 유형을 제대로 정리했습니다(오래전 일이었네요). 이 업데이트된 쿼리는 훨씬 더 효율적입니다

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 및 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 "토픽 조회 (전체) 간격",
        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