Database

Handy views for cohort analysis

CREATE VIEW monthly_new_users AS
SELECT count(*) AS count,
  c.name AS community,
  date_trunc('month'::text, u.created_at)::date AS month
FROM users u,
  users_community uc,
  community c
WHERE u.id = uc.user_id AND uc.community_id = c.id
GROUP BY c.name, date_trunc('month'::text, u.created_at)::date
ORDER BY date_trunc('month'::text, u.created_at)::date DESC, count(*) DESC;

CREATE VIEW weekly_new_users AS
SELECT count(*) AS count,
  c.name AS community,
  date_trunc('week'::text, u.created_at)::date AS week
FROM users u,
  users_community uc,
  community c
WHERE u.id = uc.user_id AND uc.community_id = c.id
GROUP BY c.name, date_trunc('week'::text, u.created_at)::date
ORDER BY date_trunc('week'::text, u.created_at)::date DESC, count(*) DESC;

View for post analysis

create view post_analysis as select 
  p.created_at, 
  u.name as person, 
  t.name as tag, 
  p.type,
  c.name as community, 
  (select count(*) from users_community uc where uc.community_id = c.id) as community_size,
  (select count(*) from comment where post_id = p.id) as comments,
  (select count(distinct user_id) from comment where post_id = p.id) as unique_commenters,
  (select count(*) from vote where post_id = p.id) as likes,
  p.name as title, 
  p.description,
  p.id as post_id
from post p
  left join posts_tags pt on pt.post_id = p.id and pt.selected = true
  left join tags t on pt.tag_id = t.id
  join post_community pc on p.id = pc.post_id
  join community c on pc.community_id = c.id
  join users u on p.user_id = u.id
where u.id != 13986 -- axolotl
group by 1, 2, 3, 4, 5, 10, 11, 12, c.id
order by p.created_at desc;

Are new signups filling out their bio and skills?

select 
  u.created_at, email, 
  substring(bio, 0, 40) as bio, 
  substring(string_agg(t.name, ' '), 0, 40) as tags 
from users u 
left join tags_users tu on u.id = tu.user_id 
left join tags t on tu.tag_id = t.id 
where 
  u.id in (select user_id from communities_users where active=true) 
group by u.id order by 1 desc limit 100;