Participation Histograms

data & reporting
1

Participation Histograms

(Output from these used to make graphs shown in this post).

Posts Read User Counts

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

-- [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.

-- [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
13 Likes