Best Practices on reply-by-email

Community
10

Per user:

select 
  username, 
  sum(case when (posts.via_email='t') then 100 else 0 end)/count(posts.id) as perc_by_mail, 
  sum(case when (posts.via_email='t') then 1 else 0 end) cnt_by_mail,
  sum(case when (posts.via_email<>'t') then 100 else 0 end)/count(posts.id) as perc_not_by_mail, 
  sum(case when (posts.via_email<>'t') then 1 else 0 end) cnt_not_by_mail
from posts
left join users on users.id = posts.user_id
where posts.created_at >= now() - interval '4 weeks'
group by username

All:

select 
  sum(case when (posts.via_email='t') then 100 else 0 end)/count(posts.id) as perc_by_mail, 
  sum(case when (posts.via_email='t') then 1 else 0 end) cnt_by_mail,
  sum(case when (posts.via_email<>'t') then 100 else 0 end)/count(posts.id) as perc_not_by_mail, 
  sum(case when (posts.via_email<>'t') then 1 else 0 end) cnt_not_by_mail
from posts
left join users on users.id = posts.user_id
where posts.created_at >= now() - interval '4 weeks'
11 Likes