Statistics per group and per trust level?

I’m not clear as to exactly what you’re looking for. Maybe something like this using the Data Explorer plugin?


WITH trust_level_one AS ( SELECT to_char(user_badges.granted_at, 'YYYY-MM') AS tl1ym
  , COUNT(user_badges.user_id) as tl1uc 
  FROM user_badges
  WHERE user_badges.badge_id IN(
    SELECT id 
    FROM badges 
    WHERE badges.name LIKE 'Basic User')
    GROUP BY to_char(user_badges.granted_at, 'YYYY-MM')
    )
, trust_level_two AS ( SELECT to_char(user_badges.granted_at, 'YYYY-MM') AS tl2ym
  , COUNT(user_badges.user_id) as tl2uc 
  FROM user_badges
  WHERE user_badges.badge_id IN(
    SELECT id 
    FROM badges 
    WHERE badges.name LIKE 'Member')
    GROUP BY to_char(user_badges.granted_at, 'YYYY-MM')
    )
SELECT trust_level_one.tl1ym
 , trust_level_one.tl1uc
 , trust_level_two.tl2uc
FROM trust_level_one
FULL JOIN trust_level_two 
ON trust_level_two.tl2ym = trust_level_one.tl1ym
GROUP BY trust_level_one.tl1ym, trust_level_one.tl1uc, trust_level_two.tl2uc
ORDER BY trust_level_one.tl1ym

example results

tl1ym tl1uc tl2uc
2016-02 11 6
2016-03 19 4
2016-04 12 7
2016-05 5 13
2016-06 1 1
2016-12 1 NULL
2017-01 1 1
2017-11 1 1
2018-01 2 1