특정 사용자의 활동에 대한 폴링

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

안녕하세요,

특정 날짜 범위 내에서 날짜별로 구분하여, 특정 사용자들의 좋아요, 받은 좋아요, 게시글, 해결한 항목을 조회하고 싶습니다. 이러한 유형의 보고서를 작성하는 데 도움이 될 만한 조언이 있을까요? 데이터 익스플로러 플러그인을 활용해야 할 것 같은데, 이 작업을 위해 이미 작성된 쿼리를 가지고 계신 분이 있을까요? 여러분의 피드백을 기다리고 있습니다!

2

로컬 개발 사이트에서 질문하신 내용과 매우 유사한 쿼리를 작성한 적이 있습니다. 요구사항에 맞게 약간 수정했습니다. 이 쿼리를 통해 일반 주제(regular topics)에서 발생한 활동 또는 개인 메시지(PM)에서 발생한 활동 중 _어느 쪽_에 대해서도 실행할 수 있도록 하려는 시도를 했습니다. 그렇게 한 이유 중 하나는 특정 기간 동안 내 자신의 활동을 확인하고 싶었기 때문입니다. 일반 주제나 개인 메시지 중 어느 쪽으로든 쿼리를 실행할 수 있는 것은 제 경우에도 합리적입니다. personal_messages 체크박스가 선택된 경우 쿼리가 솔루션을 처리하는 방식은 이상적이지 않습니다. 해당 경우 solutions_provided 컬럼은 단순히 NULL을 반환합니다. 아마도 쿼리에서 일반 주제/개인 메시지 전환 기능을 제거하는 것이 더 나을 수도 있습니다.

이 쿼리는 지나치게 복잡하지는 않지만 꽤 길습니다. 결과에 문제가 발견되면 알려주세요.

이 쿼리를 실행하려면 세 가지 파라미터를 제공해야 합니다: username, start_date, end_date. 날짜 파라미터는 ‘yyyy-mm-dd’ 형식이어야 합니다. 예를 들어 2020-03-13과 같습니다. personal_messages 파라미터의 기본값은 false입니다. 개인 메시지(PM)에서 발생한 활동 결과를 보려면 쿼리의 personal_messages 체크박스를 선택하세요.

--[params]
-- string :username
-- date :start_date
-- date :end_date
-- boolean :personal_messages = false

WITH target_user AS (SELECT id FROM users WHERE username = :username),
days AS (
SELECT day::date
FROM GENERATE_SERIES(:start_date, :end_date, INTERVAL '1 day') AS day
),
topics_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS topics
FROM topics t
JOIN target_user tu
ON tu.id = t.user_id
JOIN days
ON t.created_at::date = day
WHERE t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
AND t.deleted_at IS NULL
GROUP BY day, tu.id
),
posts_created AS(
SELECT
tu.id,
day,
COUNT(tu.id) AS posts
FROM posts p
JOIN target_user tu
ON tu.id = p.user_id
JOIN topics t
ON t.id = p.topic_id
JOIN days
ON p.created_at::date = day
WHERE p.post_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
AND p.deleted_at IS NULL
AND p.post_number > 1
GROUP BY day, tu.id
),
likes_received AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_received_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 2
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
likes_given AS (
SELECT tu.id,
day,
COUNT(tu.id) AS likes_given_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN topics t
ON t.id = ua.target_topic_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 1
AND t.archetype = CASE WHEN :personal_messages THEN  'private_message' ELSE 'regular' END
GROUP BY day, tu.id
),
solutions_provided AS (
SELECT tu.id,
day,
COUNT(tu.id) AS solutions_provided_count
FROM user_actions ua
JOIN target_user tu
ON tu.id = ua.user_id
JOIN days
ON ua.created_at::date = day
WHERE ua.action_type = 15
GROUP BY day, tu.id
)

SELECT
d.day,
COALESCE(topics, 0) AS topics,
COALESCE(posts, 0) AS posts,
COALESCE(likes_received_count, 0) AS likes_received,
COALESCE(likes_given_count, 0) AS likes_given,
(SELECT CASE WHEN NOT :personal_messages THEN COALESCE(solutions_provided_count, 0) END) AS solutions_provided
FROM days d
LEFT JOIN topics_created tc
ON tc.day = d.day
LEFT JOIN posts_created pc
ON pc.day = d.day
LEFT JOIN likes_received lc
ON lc.day = d.day
LEFT JOIN likes_given lg
ON lg.day = d.day
LEFT JOIN solutions_provided sp
ON sp.day = d.day
ORDER BY d.day DESC
3

완벽해요! @simon님 감사합니다!