사용자 통계 및 이메일

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

여기에 읽은 시간, 읽은 게시물 수, 사용자 방문 수 열을 추가하려면 어떻게 해야 하나요?

결국, 제가 공유한 다음 쿼리를 여기에 게시하신 쿼리와 결합하고, 가능하면 읽은 시간까지 추가하고 싶습니다:

select user_id,
    count(1) as visits,
    sum(posts_read) as posts_read
from user_visits
where posts_read > 0
and visited_at > CURRENT_TIMESTAMP - INTERVAL '7 days'
group by user_id
order by visits desc, posts_read desc

참고: 저는 쿼리에서 간격(interval) 매개변수를 정의할 수 있는 방식을 선호하며, 제가 게시한 쿼리의 사용자는 이메일 도메인만 아니라 전체 이메일 주소로 표시되기를 원합니다.

저는 쿼리 작성에 익숙하지 않은 초보자로, 매개변수들을 이해하고 있으며, 이메일 주소 전체로 수정하는 것까지는 할 수 있었습니다. 다만, 제가 공유한 쿼리의 방문(visits)과 posts_read 열을 섞어 넣는 것, 그리고 읽은 시간을 추가하는 것에는 아직 성공하지 못하고 있습니다.

2

결과를 이메일 도메인으로 그룹화하는 대신 사용자별로 표시하는 것이므로, 이 쿼리는 약간 다른 성격이라고 생각합니다. 이 부분은 별도의 주제로 분리해 보겠습니다. :+1:

3

이런 방식으로 하면 될 것 같습니다:

-- [params]
-- string :interval = 7 days

WITH post_data AS (

    SELECT
        p.user_id,
        COUNT(p.id) AS total_posts,
        COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS topics,
        COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS posts,
        COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS pms
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval 
      AND p.user_id > 0 
      AND p.deleted_at ISNULL
      AND t.deleted_at ISNULL
      AND p.post_type <> 3
GROUP BY p.user_id

    ),
    
user_data AS (
    
    SELECT 
        uv.user_id,
        COUNT(*) AS visits,
        SUM(posts_read) AS posts_read,
        SUM(time_read)/60 AS time_read
    FROM user_visits uv
    WHERE uv.visited_at >= CURRENT_DATE - INTERVAL :interval
    GROUP BY uv.user_id
    )

SELECT 
    ud.user_id,
    ue.email,
    ud.visits,
    ud.posts_read,
    ud.time_read,
    pd.total_posts,
    pd.topics,
    pd.posts,
    pd.pms
FROM user_data ud 
  LEFT JOIN post_data pd ON pd.user_id = ud.user_id
  JOIN user_emails ue ON ue.user_id = ud.user_id AND ue.primary = TRUE
ORDER BY ud.posts_read DESC
4

완벽하네요!! 정말 정말 감사합니다.