Useful categories SQL

Aa Intro.md

These are some handy SQL queries for generating category based reports from an identity database. You'll need a version later than 20.04, with the members on ice table nightly generation turned on. Have fun!

Donor counts etc.sql

select
issue_categories.name,
count(distinct member_actions.member_id) as member_count,
count(distinct donations.member_id) as last_year_donors,
count(distinct donations.member_id) filter (where donations.created_at > current_date - interval '6 months') as last_six_months_donors,
count(distinct donations.member_id) filter (where donations.created_at > current_date - interval '3 months') as last_three_months_donors,
count(distinct donations.member_id) filter (where donations.created_at > current_date - interval '1 month') as last_month_donors,
count(distinct regular_donations.member_id) filter (where regular_donations.ended_at is null) as regular_donors,
count(distinct regular_donations.member_id) filter (where regular_donations.ended_at is not null) as lapsed_regular_donors
from issue_categories
left join issue_categories_issues on issue_categories_issues.issue_category_id = issue_categories.id
left join campaigns on campaigns.issue_id = issue_categories_issues.issue_id
left join actions on actions.campaign_id = campaigns.id
left join member_actions on member_actions.action_id = actions.id
inner join member_subscriptions on member_subscriptions.member_id = member_actions.member_id and member_subscriptions.subscription_id = 1 and member_subscriptions.unsubscribed_at is null
left join donations on donations.member_id = member_actions.member_id and donations.created_at > current_date - interval '1 year' and donations.regular_donation_id is null
left join regular_donations on regular_donations.member_id = member_actions.member_id
group by issue_categories.name

Donor_counts.png

Member counts subscribers actions ice.sql

select
issue_categories.name,
count(distinct member_actions.member_id) filter (where member_subscriptions.unsubscribed_at is null) as subscribed_member_count,
count(distinct member_actions.member_id) as total_member_count,
count(distinct actions.id) as actions_count,
count(distinct member_actions.id) as member_actions_count,
count(distinct member_actions.member_id) filter (where exists (select 1 from members_on_ice where member_actions.member_id = members_on_ice.member_id)) as members_on_ice_count,
count(distinct member_actions.member_id) filter (where member_subscriptions.unsubscribed_at is null and not exists (select 1 from members_on_ice where member_actions.member_id = members_on_ice.member_id)) as members_not_on_ice_count
from issue_categories
left join issue_categories_issues on issue_categories_issues.issue_category_id = issue_categories.id
left join campaigns on campaigns.issue_id = issue_categories_issues.issue_id
left join actions on actions.campaign_id = campaigns.id
left join member_actions on member_actions.action_id = actions.id
inner join member_subscriptions on member_subscriptions.member_id = member_actions.member_id and member_subscriptions.subscription_id = 1
group by issue_categories.name

Member_counts.png