Help finding some monthly site stats about topics and user trust levels

DeanMarkTaylor · April 14, 2016, 9:58pm

Here is a “Topic Participation” query, someone else is going to want to check it though.

Data Explorer: topic-participation.dcquery (v4).json (7.9 KB)

EDIT #1 Updated to handle including rows for month when data doesn’t exist.
EDIT #2 Updated to check for responses within X days (default 15)
EDIT #3 Updated to:
include posts information
separated exclusions for “topics created by user from group” and “posts created by user from group”
impersonate a particular users view of topics - use an admin user to see everything.
optionally restrict results to that month (-1) for responses or check for replies within X days.
support output of response rate as percentage ready for Excel.
WARNING: You might want to look further down this thread to see if anybody has sanity checked the query.

Parameters

impersonate
Username to view of topics as - use an admin username like system user to see everything.
exclude_groups_topics
list of user groups (separated by a comma) to exclude topics from
enter a single comma (,) to not exclude any.
if the group doesn’t exist, no exclusion will occur (check your spelling)
exclude_groups_replies
list of user groups (separated by a comma) to exclude replies from
enter a single comma (,) to not exclude any.
if the group doesn’t exist, no exclusion will occur.
exclude_category_slugs
list of category slugs (separated by a comma) to exclude topics from
enter a single comma (,) to not exclude any.
reply_within_x_days
Set to -1 to restrict responses check to that month (default).
Set to 0 to restrict responses to the same day topic was created.
Set to 15 to count as response up to 15 day after initial topic creation even if 15 days is outside of that month.
pct_multiplier and pct_places
Set pct_multiplier to 1 and pct_places to 6 to get percent values ready for Excel.
Set pct_multiplier to 100 and pct_places to 1 to display as readable percentage values (default).

-- [params]
-- string :impersonate = system
-- string :exclude_groups_topics = staff
-- string :exclude_groups_replies = ,
-- string :exclude_category_slugs = staff,dev,simply-thank-you,was-it-you,wanted
-- int :reply_within_x_days = -1
-- int :pct_multiplier = 100
-- int :pct_places = 1



WITH
year_months AS (
  SELECT
    to_char(date(day),'YYYY-MM') as year_month
  FROM
    generate_series(
      (date_trunc('month', CURRENT_DATE) - INTERVAL '1 year' ),
      CURRENT_DATE,
      interval '1 month'
    ) AS day
),
impersonated_usernames AS (
 SELECT unnest(string_to_array( :impersonate, ',')) AS username
),
impersonated_users AS (
 SELECT id
    FROM users AS u
    JOIN impersonated_usernames iun ON iun.username = u.username
    WHERE iun.username = u.username
),
excluded_group_names_topics AS (
 SELECT unnest(string_to_array( :exclude_groups_topics, ',')) AS name
),
excluded_group_names_replies AS (
 SELECT unnest(string_to_array( :exclude_groups_replies, ',')) AS name
),
excluded_users_topics AS (
    SELECT user_id AS id
    FROM users AS u
    JOIN group_users AS gu ON gu.user_id = u.id
    JOIN groups AS g ON g.id = gu.group_id
    JOIN excluded_group_names_topics eg ON eg.name = g.name
    WHERE g.name = eg.name
),
excluded_users_replies AS (
    SELECT user_id AS id
    FROM users AS u
    JOIN group_users AS gu ON gu.user_id = u.id
    JOIN groups AS g ON g.id = gu.group_id
    JOIN excluded_group_names_replies eg ON eg.name = g.name
    WHERE g.name = eg.name
),
excluded_category_slugs AS (
 SELECT unnest(string_to_array( :exclude_category_slugs, ',')) AS slug
),
excluded_categories AS (
    SELECT id
    FROM categories AS c
    JOIN excluded_category_slugs ec ON ec.slug = c.slug
    WHERE ec.slug = c.slug
),
restricted_topics AS (
  SELECT
    t.id,
    t.created_at,
    t.user_id
  FROM
    topics AS t
  JOIN users u on u.id IN (SELECT id FROM impersonated_users)
  JOIN user_stats AS us ON us.user_id = u.id
  JOIN user_options AS uo ON uo.user_id = u.id
  JOIN categories c ON c.id = t.category_id
  LEFT JOIN topic_users tu ON tu.topic_id = t.id AND tu.user_id = u.id
  WHERE
    u.id IN (SELECT id FROM impersonated_users)
    /** test **
    AND t.created_at > (date_trunc('day', CURRENT_DATE) - INTERVAL '1 day' )
    AND t.created_at < (date_trunc('day', CURRENT_DATE) )
    ** test **/
    AND t.created_at > (date_trunc('month', CURRENT_DATE) - INTERVAL '1 year' )
    AND t.archetype <> 'private_message'
    AND t.deleted_at IS NULL
    AND (t.visible OR u.admin OR u.moderator)
    AND (
      NOT c.read_restricted OR u.admin OR category_id IN (
        SELECT c2.id FROM categories c2
        JOIN category_groups cg ON cg.category_id = c2.id
        JOIN group_users gu ON gu.user_id IN (SELECT id FROM impersonated_users)
          AND cg.group_id = gu.group_id
        WHERE c2.read_restricted
      )
    )
    AND t.user_id NOT IN (SELECT id FROM excluded_users_topics)
    AND t.category_id NOT IN (SELECT id from excluded_categories)
),
restricted_posts AS (
  SELECT
    p.id,
    p.created_at
  FROM
    posts AS p
  LEFT JOIN topics AS t ON p.topic_id = t.id
  JOIN users u on u.id IN (SELECT id FROM impersonated_users)
  JOIN user_stats AS us ON us.user_id = u.id
  JOIN user_options AS uo ON uo.user_id = u.id
  JOIN categories c ON c.id = t.category_id
  LEFT JOIN topic_users tu ON tu.topic_id = t.id AND tu.user_id = u.id
  WHERE
    u.id IN (SELECT id FROM impersonated_users)
    /** test **
    AND p.created_at > (date_trunc('day', CURRENT_DATE) - INTERVAL '1 day' )
    AND p.created_at < (date_trunc('day', CURRENT_DATE) )
    ** test **/
    AND p.created_at > (date_trunc('month', CURRENT_DATE) - INTERVAL '1 year' )
    AND t.archetype <> 'private_message'
    AND t.deleted_at IS NULL
    AND (t.visible OR u.admin OR u.moderator)
    AND (
      NOT c.read_restricted OR u.admin OR category_id IN (
        SELECT c2.id FROM categories c2
        JOIN category_groups cg ON cg.category_id = c2.id
        JOIN group_users gu ON gu.user_id IN (SELECT id FROM impersonated_users)
          AND cg.group_id = gu.group_id
        WHERE c2.read_restricted
      )
    )
    AND t.user_id NOT IN (SELECT id FROM excluded_users_topics)
    AND t.category_id NOT IN (SELECT id from excluded_categories)
),
qualifying_topics AS (
  SELECT
    rt.id,
    rt.created_at::date AS created_at,
    MIN(p.post_number) AS first_reply,
    COUNT(p) AS new_topic_posts
  FROM
    restricted_topics AS rt
  LEFT JOIN posts p ON
    p.topic_id = rt.id
    AND p.deleted_at IS NULL
    AND p.user_id != rt.user_id
    AND p.user_id NOT IN (SELECT id FROM excluded_users_replies)
    AND (
      ( :reply_within_x_days >= 0
        AND p.created_at < ( rt.created_at::date + ( :reply_within_x_days + 1 ) )
      )
      OR
      (
       :reply_within_x_days < 0
        AND to_char(date(p.created_at),'YYYY-MM') = to_char(date(rt.created_at),'YYYY-MM')
      )
    )
  WHERE
    rt.created_at > (date_trunc('month', CURRENT_DATE) - INTERVAL '1 year' )
  GROUP BY rt.id, rt.created_at
),
topic_year_month_rate AS (
    SELECT
        to_char(date(tt.created_at),'YYYY-MM') as year_month,
        COUNT(*) AS topics,
        SUM(new_topic_posts) AS new_topic_posts,
        SUM(CASE WHEN tt.first_reply IS NULL THEN 1 ELSE 0 END) AS no_response,
        SUM(CASE WHEN tt.first_reply IS NOT NULL THEN 1 ELSE 0 END) AS response
    FROM qualifying_topics AS tt
    GROUP BY
      year_month
    ORDER BY
      year_month
),
posts_year_month_rate AS (
    SELECT
        to_char(date(rp.created_at),'YYYY-MM') as year_month,
        COUNT(*) as posts
    FROM restricted_posts as rp
    GROUP BY
      year_month
    ORDER BY
      year_month
),
raw_year_month_rate AS (
  SELECT
    year_month,
    SUM(posts) AS posts,
    SUM(new_topic_posts) AS new_topic_posts,
    (SUM(posts) - SUM(new_topic_posts)) AS old_topic_posts,
    SUM(topics) AS topics,
    SUM(no_response) AS no_response,
    SUM(response) AS response
  FROM (
    SELECT year_month, topics, 0 AS posts, new_topic_posts, 0 AS old_topic_posts, no_response, response FROM topic_year_month_rate
    UNION ALL
    SELECT year_month, 0 AS topics, posts, 0 AS new_topic_posts, 0 AS old_topic_posts, 0 AS no_response, 0 AS response FROM posts_year_month_rate
  ) AS combined
  GROUP BY year_month
  ORDER BY year_month
),
year_month_rate AS (
    SELECT
      rymr.*,
      (CASE
        WHEN topics <= 0 THEN 0
        ELSE (
         ROUND( (:pct_multiplier + 0.0) * (
           (response * 1.0) / (topics * 1.0) 
          ), :pct_places)
        ) END
      ) AS response_rate
    FROM 
      raw_year_month_rate rymr
)

SELECT
 *
FROM
 year_months AS d
LEFT JOIN (
  SELECT * FROM year_month_rate
) AS t USING (year_month)
ORDER BY
  year_month

If you don’t want to use any exclusions just put a comma (,) in that input box.

So it should look a little like this:

So the columns are:

posts
all qualifying posts from topics accessible to impersonated user after exclusion of both users and categories.
new_topic_posts
posts made to topics created that month.
old_topic_posts
posts made to topics created before that month.
topics
all qualifying topics after exclusion of both users and categories.
no_response
count of topics that didn’t receive a reply from that type of user
e.g. a staff member might have replied, but if you excluded staff it would be counted as no_response if that topic wasn’t created itself by an excluded user.
a topic created by an excluded user is excluded anyway.
response
count of topics that did receive a reply the month the topic was created from a non-excluded user.
response_rate
percentage rate of response - higher the better

Topic		Replies	Views
Find the users which are more likely to become TL3 Data & reporting trust-levels , sql-query	18	3988	March 17, 2023
Please update 'Understanding Discourse Trust Levels' — or is there a different doc? Support	29	2935	April 23, 2022
Attempting to create Monthly Leaderboard query - head exploding Data & reporting sql-query	19	1698	June 8, 2024
Running a month-long contest to develop selected categories share its topics on the wider internet Community	15	3357	December 1, 2017
Trust Level Permissions Reference Using Discourse trust-levels , reference	23	20030	September 11, 2024

Help finding some monthly site stats about topics and user trust levels

Parameters

Related topics