User Statistics for a particular group

data & reporting
1

Got this sorted promptly with the professional talent of @pfaffman, our thanks to him. The script I’ll turn over for the community. They way we will be using it is by letting in 100 registrants or so, locking public access down then using the script to identify potential conversions and direct someone to say hi to them.

In this manner we can better predict our conversions, make better use of staff time and adjust the public intake up or down to suit assimilation onto our board.

Change your group ID to suit.

-- [params]
-- 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 Likes