特定用户组的用户统计

社区 数据与报告
1

已借助 @pfaffman 的专业能力迅速解决了该问题，特此感谢。我将把脚本提交给社区。我们使用它的方式是：先允许约 100 名用户注册，然后限制公开访问权限，再利用该脚本识别潜在转化用户，并安排人员主动与他们打招呼。

通过这种方式，我们可以更准确地预测转化情况，更有效地利用工作人员时间，并根据融入我们论坛的需要，灵活调整公开注册的规模。

请根据您的实际情况修改群组 ID。

-- [参数]
-- int :from_days_ago = 0
-- int :duration_days = 30
-- int :group_id = 73
--
WITH t AS (
    SELECT CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) AS first,
        CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) AS last
),
group_members as (
    SELECT user_id FROM
    group_users gu
    WHERE gu.group_id= :group_id
    ),
pr AS (
    SELECT user_id, COUNT(1) AS visits,
        SUM(posts_read) AS posts_read
    FROM user_visits, t
    WHERE posts_read > 0
        AND visited_at > t.first
        AND visited_at < t.last
    GROUP BY
        user_id
),
pc AS (
    SELECT user_id, COUNT(1) AS posts_created
    FROM posts, t
    WHERE
        created_at > t.first
        AND created_at < t.last
    GROUP BY
        user_id
),
ttopics AS (
    SELECT user_id, posts_count
    FROM topics, t
    WHERE created_at > t.first
        AND created_at < t.last
),
tc AS (
    SELECT user_id, COUNT(1) AS topics_created
    FROM ttopics
    GROUP BY user_id
),
twr AS (
    SELECT user_id, COUNT(1) AS topics_with_replies
    FROM ttopics
    WHERE posts_count > 1
    GROUP BY user_id
),
tv AS (
    SELECT user_id,
        COUNT(DISTINCT(topic_id)) AS topics_viewed
    FROM topic_views, t
    WHERE viewed_at > t.first
        AND viewed_at < t.last
    GROUP BY user_id
),
likes AS (
    SELECT post_actions.user_id AS given_by_user_id,
        posts.user_id AS received_by_user_id
    FROM t,
        post_actions
        LEFT JOIN
        posts
        ON post_actions.post_id = posts.id
    WHERE
        post_actions.created_at > t.first
        AND post_actions.created_at < t.last
        AND post_action_type_id = 2
),
lg AS (
    SELECT given_by_user_id AS user_id,
        COUNT(1) AS likes_given
    FROM likes
    GROUP BY user_id
),
lr AS (
    SELECT received_by_user_id AS user_id,
        COUNT(1) AS likes_received
    FROM likes
    GROUP BY user_id
),
e AS (
    SELECT email, user_id
    FROM user_emails u
    WHERE u.PRIMARY = TRUE
)
SELECT
    pr.user_id,
    username,
    name,
    email,
    visits,
    COALESCE(topics_viewed, 0) AS topics_viewed,
    COALESCE(posts_read, 0) AS posts_read,
    COALESCE(posts_created, 0) AS posts_created,
    COALESCE(topics_created, 0) AS topics_created,
    COALESCE(topics_with_replies, 0) AS topics_with_replies,
    COALESCE(likes_given, 0) AS likes_given,
    COALESCE(likes_received, 0) AS likes_received
FROM group_members, pr
LEFT JOIN tv USING (user_id)
LEFT JOIN pc USING (user_id)
LEFT JOIN tc USING (user_id)
LEFT JOIN twr USING (user_id)
LEFT JOIN lg USING (user_id)
LEFT JOIN lr USING (user_id)
LEFT JOIN e USING (user_id)
LEFT JOIN users ON pr.user_id = users.id
WHERE group_members.user_id=pr.user_id
ORDER BY
    visits DESC,
    posts_read DESC,
    posts_created DESC
10 个赞