create or replace view member_counts as select  members.id as id, 
              members.id as member_id,
              
              ( select COUNT(*) 
                  from postings 
                 where postings.member_id = members.id 
                   and postings.type = 'TradeNote'
              ) as trade_note_count,  
              
              ( select COUNT(*) 
                  from postings 
                 where postings.member_id = members.id 
                   and postings.type = 'Blog'
              ) as blog_count, 
              
              ( select count(distinct posting_id) 
                  from member_tags, member_taggings 
                 where member_tags.member_id = members.id 
                   and member_taggings.member_tag_id = member_tags.id
              ) as tagged_item_count, 
              
              ( select count(*) 
                  from groups_members
                 where member_id = members.id
              ) as group_count, 
              
              ( select count(*) 
                  from subscriptions 
                 where subscribed_id = members.id
              ) as subscription_count 
              
         from members;