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

data & reporting
8

So for #2 and #3:

Here is a 2nd version based on group_users table, I added “Trust Level 3” as well:

Data Explorer: count-new-tl1-or-tl2-users-past-12-months-v2.dcquery.json (1.3 KB)

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
),
qualifying_users AS (
  SELECT
    gu.user_id,
    to_char(date(gu.created_at),'YYYY-MM') as year_month,
    SUM(CASE WHEN g.name = 'trust_level_1' THEN 1 ELSE 0 END) AS tl1,
    SUM(CASE WHEN g.name = 'trust_level_2' THEN 1 ELSE 0 END) AS tl2,
    SUM(CASE WHEN g.name = 'trust_level_3' THEN 1 ELSE 0 END) AS tl3
  FROM group_users AS gu
  JOIN groups AS g ON g.id = gu.group_id
  WHERE
    gu.created_at > ( date_trunc('month', CURRENT_DATE) - INTERVAL '1 year' )
    AND ( g.name = 'trust_level_1' OR g.name = 'trust_level_2' OR g.name = 'trust_level_3' )
  GROUP BY
      gu.user_id,
      year_month
),
year_month_rate AS (
  SELECT
    year_month,
    SUM(tl1) AS tl1,
    SUM(tl2) AS tl2,
    SUM(tl3) AS tl3
  FROM qualifying_users q
  GROUP BY
      year_month
)



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