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

**URL:** <https://meta.discourse.org/t/help-finding-some-monthly-site-stats-about-topics-and-user-trust-levels/42560>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2016年四月14日 15:04 UTC](https://meta.discourse.org/t/help-finding-some-monthly-site-stats-about-topics-and-user-trust-levels/42560 "2016-04-14T15:04:48Z")\
**Posts on this page:** 1\
**Showing post:** 8

<div class="post-metadata">

**Author:** ![DeanMarkTaylor](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/deanmarktaylor/32/102462_2.png) [@DeanMarkTaylor](https://meta.discourse.org/u/DeanMarkTaylor)\
**Post date:** [2016年四月14日 22:48 UTC](https://meta.discourse.org/t/help-finding-some-monthly-site-stats-about-topics-and-user-trust-levels/42560/8 "2016-04-14T22:48:30Z")

</div>

> [@tobiaseigen](#):
>
> TL badges were turned off on my site

> [@Mittineague](#):
>
> I don’t have the time to mess with the query now, but I think you should be able to get what you want from tables other than the badges table

So for #2 and #3:

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

[Data Explorer](https://meta.discourse.org/t/32566?silent=true): [count-new-tl1-or-tl2-users-past-12-months-v2.dcquery.json](https://global.discourse-cdn.com/meta/original/3X/a/8/a8995ca176bd54e244242b83d15512429b8fcbbb.json) (1.3 KB)

```SQL
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

```

---

_[View the full topic](https://meta.discourse.org/t/help-finding-some-monthly-site-stats-about-topics-and-user-trust-levels/42560)._