# Participation Histograms

**URL:** https://meta.discourse.org/t/participation-histograms/275022
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [November 17, 2017, 2:42pm UTC](https://meta.discourse.org/t/participation-histograms/275022 "2017-11-17T14:42:55Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![mcwumbly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mcwumbly/32/103861_2.png) [@mcwumbly](https://meta.discourse.org/u/mcwumbly)
#### Post date: [November 17, 2017, 2:42pm UTC](https://meta.discourse.org/t/participation-histograms/275022/1 "2017-11-17T14:42:55Z")

</div>

## Participation Histograms

(Output from these used to make graphs shown in [this post](https://meta.discourse.org/t/what-would-you-like-to-see-on-your-discourse-admin-dashboard/67134/14)).

### Posts Read User Counts

Number of users who have read at least N posts over the specified time period.

```plaintext
-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 28

with
t as (
  select 
    current_date::timestamp - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
    current_date::timestamp - (:from_days_ago * (INTERVAL '1 days')) as end
),
read_visits as (
    select user_id, 
        count(1) as visits,
        sum(posts_read) as posts_read
    from user_visits, t
    where posts_read >= 1
    and visited_at > t.start
    and visited_at < t.end
    group by user_id
)
select
      case when posts_read >= 1 and posts_read <= 1 then '0001'
          when posts_read >= 2 and posts_read <= 3 then '0002 - 03'
          when posts_read >= 4 and posts_read <= 7 then '0004 - 07'
          when posts_read >= 8 and posts_read <= 15 then '0008 - 15'
          when posts_read >= 16 and posts_read <= 40 then '0016 - 31'
          when posts_read >= 32 and posts_read <= 63 then '0032 - 63'
          when posts_read >= 64 and posts_read <= 127 then '0064 - 127'
          when posts_read >= 128 and posts_read <= 255 then '0128 - 255'
          when posts_read >= 256 and posts_read <= 511 then '0256 - 511'
          when posts_read >= 512 and posts_read <= 1023 then '0512 - 1023'
          when posts_read >= 1024 and posts_read <= 2047 then '1024 - 2047'
          else '>= 2048'
      end as num_posts_read,
      count(*) as num_users
  from
      read_visits
  group by num_posts_read
  order by num_posts_read

```

### Posts Created User Counts

Number of users who have created at least N posts over the specified time period.

```plaintext
-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 28

with
t as (
  select 
    current_date::timestamp - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) as start,
    current_date::timestamp - (:from_days_ago * (INTERVAL '1 days')) as end
),
pc as (
  select user_id, 
      count(1) as posts_created
  from posts, t
  where created_at > t.start
  and created_at < t.end
  group by user_id
)
select
      case when posts_created >= 1 and posts_created <= 1 then '01'
          when posts_created >= 2 and posts_created <= 3 then '02 - 03'
          when posts_created >= 4 and posts_created <= 7 then '04 - 07'
          when posts_created >= 8 and posts_created <= 15 then '08 - 15'
          when posts_created >= 16 and posts_created <= 31 then '16 - 31'
          when posts_created >= 32 and posts_created <= 63 then '32 - 63'
          when posts_created >= 64 and posts_created <= 127 then '64 - 127'
          when posts_created >= 128 and posts_created <= 255 then '128 - 255'
          else '> 256'
      end as num_posts_created,
      count(*) as num_users
  from
      pc
  group by num_posts_created
  order by num_posts_created

```