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