Skip to main content
Parable
Open Parable

Planning Center People Report Examples

Production-ready People reports for church leadership: membership lists, new-visitor follow-up, and congregation demographics ready to run.

This guide provides complete, production-ready SQL reports for Planning Center People data. These reports are designed to be run regularly for leadership meetings, pastoral care, and strategic planning.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center People module live in the planning_center schema. Always prefix table names with planning_center. in your reports.

✅ CORRECT: SELECT * FROM planning_center.people_people ❌ INCORRECT: SELECT * FROM people_people

Row Level Security (RLS)

Row Level Security automatically filters results for:

  • tenant_organization_id – only your organization's data
  • system_status – active records returned by default

Do not add these filters manually—RLS already enforces them and redundant predicates can hide data or slow execution:

  • ❌ WHERE tenant_organization_id = 1
  • ❌ WHERE system_status = 'active'

Keep your WHERE clauses focused on ministry-specific segments (status, membership, demographics) while relying on RLS for tenancy and system status.

Executive Dashboard Report

Weekly Church Membership Summary

-- Executive summary report for church leadership
WITH current_stats AS (
    SELECT
        COUNT(DISTINCT CASE WHEN status = 'active' THEN person_id END) as active_members,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Member' THEN person_id END) as full_members,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Regular Attender' THEN person_id END) as regular_attenders,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Visitor' THEN person_id END) as visitors,
        COUNT(DISTINCT CASE WHEN created_at >= DATE_TRUNC('week', CURRENT_DATE) THEN person_id END) as new_this_week,
        COUNT(DISTINCT CASE WHEN child = true AND status = 'active' THEN person_id END) as children,
        COUNT(DISTINCT CASE WHEN graduation_year IS NOT NULL AND status = 'active' THEN person_id END) as students,
        COUNT(DISTINCT CASE WHEN birthdate IS NOT NULL AND EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) >= 65 AND status = 'active' THEN person_id END) as seniors
    FROM planning_center.people_people
),
previous_week AS (
    SELECT
        COUNT(DISTINCT CASE WHEN status = 'active' THEN person_id END) as active_members
    FROM planning_center.people_people
    WHERE created_at < DATE_TRUNC('week', CURRENT_DATE)
),
household_stats AS (
    SELECT
        COUNT(DISTINCT h.household_id) as total_households,
        AVG(h.member_count) as avg_household_size
    FROM planning_center.people_households h
)
SELECT
    '=== WEEKLY MEMBERSHIP EXECUTIVE SUMMARY ===' as report_header,
    TO_CHAR(DATE_TRUNC('week', CURRENT_DATE), 'FMMonth DD, YYYY') as week_beginning,
    '' as blank1,
    '--- MEMBERSHIP METRICS ---' as section1,
    cs.active_members as total_active_members,
    pw.active_members as active_members_last_week,
    cs.active_members - pw.active_members as net_change,
    cs.new_this_week as new_people_this_week,
    '' as blank2,
    '--- MEMBERSHIP BREAKDOWN ---' as section2,
    cs.full_members as members,
    cs.regular_attenders as regular_attenders,
    cs.visitors as visitors,
    ROUND((cs.full_members::NUMERIC / NULLIF(cs.active_members, 0)) * 100, 1) as member_percentage,
    '' as blank3,
    '--- DEMOGRAPHICS ---' as section3,
    cs.children as children_count,
    cs.students as student_count,
    cs.seniors as senior_count,
    cs.active_members - cs.children - cs.students as adult_count,
    '' as blank4,
    '--- HOUSEHOLD METRICS ---' as section4,
    hs.total_households as households,
    ROUND(hs.avg_household_size, 1) as avg_household_size
FROM current_stats cs, previous_week pw, household_stats hs;

Monthly Demographic Analysis Report

-- Comprehensive demographic breakdown for strategic planning
WITH age_demographics AS (
    SELECT
        CASE 
            WHEN child = true THEN '0-12 (Children)'
            WHEN graduation_year IS NOT NULL AND graduation_year >= EXTRACT(YEAR FROM CURRENT_DATE) THEN '13-18 (Students)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 18 AND 24 THEN '18-24 (College)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 25 AND 34 THEN '25-34 (Young Adult)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 35 AND 44 THEN '35-44 (Young Family)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 45 AND 54 THEN '45-54 (Middle Age)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 55 AND 64 THEN '55-64 (Pre-Retirement)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) >= 65 THEN '65+ (Senior)'
            ELSE 'Age Unknown'
        END as age_group,
        gender,
        membership,
        COUNT(*) as count
    FROM planning_center.people_people
    WHERE status = 'active'
    GROUP BY age_group, gender, membership
),
demographic_summary AS (
    SELECT
        age_group,
        SUM(count) as total,
        SUM(CASE WHEN gender = 'Male' THEN count ELSE 0 END) as male_count,
        SUM(CASE WHEN gender = 'Female' THEN count ELSE 0 END) as female_count,
        SUM(CASE WHEN membership = 'Member' THEN count ELSE 0 END) as members,
        SUM(CASE WHEN membership = 'Regular Attender' THEN count ELSE 0 END) as regular_attenders,
        SUM(CASE WHEN membership = 'Visitor' THEN count ELSE 0 END) as visitors
    FROM age_demographics
    GROUP BY age_group
)
SELECT
    age_group,
    total,
    male_count,
    female_count,
    CASE 
        WHEN (male_count + female_count) > 0 
        THEN ROUND(male_count::NUMERIC / (male_count + female_count) * 100, 0)
        ELSE NULL
    END as male_percentage,
    members,
    regular_attenders,
    visitors,
    ROUND((total::NUMERIC / (SELECT SUM(total) FROM demographic_summary)) * 100, 1) as percent_of_church
FROM demographic_summary
ORDER BY 
    CASE 
        WHEN age_group LIKE '0-12%' THEN 1
        WHEN age_group LIKE '13-18%' THEN 2
        WHEN age_group LIKE '18-24%' THEN 3
        WHEN age_group LIKE '25-34%' THEN 4
        WHEN age_group LIKE '35-44%' THEN 5
        WHEN age_group LIKE '45-54%' THEN 6
        WHEN age_group LIKE '55-64%' THEN 7
        WHEN age_group LIKE '65+%' THEN 8
        ELSE 9
    END;

Pastoral Care Reports

Birthday and Anniversary Report

-- Upcoming birthdays and anniversaries for pastoral care
WITH upcoming_dates AS (
    SELECT
        person_id,
        first_name,
        last_name,
        birthdate,
        anniversary,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) + 1 as turning_age,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, anniversary)) + 1 as anniversary_years,
        -- Calculate next birthday
        CASE 
            WHEN DATE_PART('doy', birthdate) >= DATE_PART('doy', CURRENT_DATE)
            THEN DATE_TRUNC('year', CURRENT_DATE) + (birthdate - DATE_TRUNC('year', birthdate))
            ELSE DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year' + (birthdate - DATE_TRUNC('year', birthdate))
        END as next_birthday,
        -- Calculate next anniversary
        CASE 
            WHEN anniversary IS NOT NULL AND DATE_PART('doy', anniversary) >= DATE_PART('doy', CURRENT_DATE)
            THEN DATE_TRUNC('year', CURRENT_DATE) + (anniversary - DATE_TRUNC('year', anniversary))
            ELSE DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year' + (anniversary - DATE_TRUNC('year', anniversary))
        END as next_anniversary
    FROM planning_center.people_people
    WHERE status = 'active'
        AND (birthdate IS NOT NULL OR anniversary IS NOT NULL)
),
combined_events AS (
    SELECT
        person_id,
        first_name,
        last_name,
        'Birthday' as event_type,
        next_birthday as event_date,
        turning_age::TEXT || ' years old' as detail
    FROM upcoming_dates
    WHERE birthdate IS NOT NULL
    
    UNION ALL
    
    SELECT
        person_id,
        first_name,
        last_name,
        'Anniversary' as event_type,
        next_anniversary as event_date,
        anniversary_years::TEXT || ' years' as detail
    FROM upcoming_dates
    WHERE anniversary IS NOT NULL
)
SELECT
    TO_CHAR(event_date, 'MM/DD') as date,
    TO_CHAR(event_date, 'FMDay') as day_of_week,
    first_name || ' ' || last_name as person,
    event_type,
    detail,
    event_date - CURRENT_DATE as days_until
FROM combined_events
WHERE event_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
ORDER BY event_date, last_name;

Follow-Up Required Report

-- People requiring pastoral follow-up based on various criteria
WITH follow_up_needs AS (
    -- Recently inactive
    SELECT
        p.person_id,
        p.first_name || ' ' || p.last_name as name,
        'Recently Inactive' as reason,
        p.inactivated_at as trigger_date,
        ir.value as details,
        1 as priority
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_people_relationships pr
        ON p.person_id = pr.person_id
        AND pr.relationship_type = 'InactiveReason'
    LEFT JOIN planning_center.people_inactive_reasons ir
        ON pr.relationship_id = ir.inactive_reason_id
    WHERE p.status = 'inactive'
        AND p.inactivated_at >= CURRENT_DATE - INTERVAL '30 days'
    
    UNION ALL
    
    -- First-time visitors (no membership status)
    SELECT
        person_id,
        first_name || ' ' || last_name,
        'First-Time Visitor',
        created_at,
        'Joined ' || TO_CHAR(created_at, 'MM/DD'),
        2
    FROM planning_center.people_people
    WHERE membership = 'Visitor'
        AND created_at >= CURRENT_DATE - INTERVAL '14 days'
        AND status = 'active'
    
    UNION ALL
    
    -- Background check expiring
    SELECT
        p.person_id,
        p.first_name || ' ' || p.last_name,
        'Background Check Expiring',
        bc.expires_on,
        'Expires ' || TO_CHAR(bc.expires_on, 'MM/DD'),
        3
    FROM planning_center.people_people p
    JOIN planning_center.people_background_checks_relationships bcr
        ON bcr.relationship_type = 'Person' AND bcr.relationship_id = p.person_id
    JOIN planning_center.people_background_checks bc
        ON bc.background_check_id = bcr.background_check_id
    WHERE bc.expires_on BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
        AND p.status = 'active'
)
SELECT
    name,
    reason,
    details,
    TO_CHAR(trigger_date, 'MM/DD/YYYY') as date,
    CURRENT_DATE - trigger_date::DATE as days_ago,
    CASE priority
        WHEN 1 THEN '🔴 High'
        WHEN 2 THEN '🟡 Medium'
        ELSE '🟢 Low'
    END as priority_level
FROM follow_up_needs
ORDER BY priority, trigger_date DESC;

Household Analysis Reports

Family Composition Report

-- Analyze household structures and family compositions
WITH household_analysis AS (
    SELECT
        h.household_id,
        h.name as household_name,
        h.member_count,
        COUNT(DISTINCT CASE WHEN p.child = true THEN p.person_id END) as children,
        COUNT(DISTINCT CASE WHEN p.child = false OR p.child IS NULL THEN p.person_id END) as adults,
        COUNT(DISTINCT CASE WHEN p.graduation_year IS NOT NULL THEN p.person_id END) as students,
        MIN(p.birthdate) as oldest_member_birthdate,
        MAX(p.birthdate) as youngest_member_birthdate,
        STRING_AGG(p.first_name, ', ' ORDER BY p.birthdate) as members
    FROM planning_center.people_households h
    JOIN planning_center.people_households_relationships hhr
        ON hhr.household_id = h.household_id AND LOWER(hhr.relationship_type) = 'person'
    JOIN planning_center.people_people p
        ON p.person_id = hhr.relationship_id
        AND p.status = 'active'
    GROUP BY h.household_id, h.name, h.member_count
),
household_categories AS (
    SELECT
        household_name,
        member_count,
        adults,
        children,
        students,
        members,
        CASE
            WHEN adults = 1 AND children = 0 THEN 'Single Adult'
            WHEN adults = 2 AND children = 0 THEN 'Married No Children'
            WHEN adults = 1 AND children > 0 THEN 'Single Parent'
            WHEN adults = 2 AND children > 0 THEN 'Nuclear Family'
            WHEN adults > 2 THEN 'Multi-Generational'
            ELSE 'Other'
        END as family_type,
        CASE
            WHEN children > 0 AND children <= 2 THEN 'Small Family'
            WHEN children >= 3 THEN 'Large Family'
            WHEN students > 0 THEN 'Family with Teens'
            ELSE 'No Children'
        END as family_stage
    FROM household_analysis
)
SELECT
    family_type,
    COUNT(*) as household_count,
    SUM(member_count) as total_people,
    ROUND(AVG(member_count), 1) as avg_household_size,
    SUM(children) as total_children,
    SUM(adults) as total_adults,
    ROUND(AVG(children), 1) as avg_children_per_household,
    ROUND((COUNT(*)::NUMERIC / (SELECT COUNT(*) FROM household_categories)) * 100, 1) as percent_of_households
FROM household_categories
GROUP BY family_type
ORDER BY household_count DESC;

Campus Analysis Reports

Multi-Campus Distribution Report

-- Analyze member distribution across campuses
WITH campus_metrics AS (
    SELECT
        c.campus_id,
        c.name as campus_name,
        c.city,
        c.state,
        COUNT(DISTINCT pr.person_id) as total_members,
        COUNT(DISTINCT CASE WHEN p.membership = 'Member' THEN pr.person_id END) as full_members,
        COUNT(DISTINCT CASE WHEN p.child = true THEN pr.person_id END) as children,
        COUNT(DISTINCT CASE WHEN p.graduation_year IS NOT NULL THEN pr.person_id END) as students,
        COUNT(DISTINCT CASE WHEN p.created_at >= CURRENT_DATE - INTERVAL '90 days' THEN pr.person_id END) as new_in_90_days,
        AVG(EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate))) as avg_age
    FROM planning_center.people_campuses c
    LEFT JOIN planning_center.people_people_relationships pr
        ON c.campus_id = pr.relationship_id
        AND pr.relationship_type = 'PrimaryCampus'
    LEFT JOIN planning_center.people_people p
        ON pr.person_id = p.person_id
        AND p.status = 'active'
    GROUP BY c.campus_id, c.name, c.city, c.state
),
campus_growth AS (
    SELECT
        c.name as campus_name,
        COUNT(DISTINCT CASE 
            WHEN p.created_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' 
            THEN pr.person_id 
        END) as last_month,
        COUNT(DISTINCT CASE 
            WHEN p.created_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '2 months'
            AND p.created_at < DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
            THEN pr.person_id 
        END) as two_months_ago
    FROM planning_center.people_campuses c
    LEFT JOIN planning_center.people_people_relationships pr
        ON c.campus_id = pr.relationship_id
        AND pr.relationship_type = 'PrimaryCampus'
    LEFT JOIN planning_center.people_people p
        ON pr.person_id = p.person_id
    GROUP BY c.name
)
SELECT
    cm.campus_name,
    cm.city || ', ' || cm.state as location,
    cm.total_members,
    cm.full_members,
    ROUND((cm.full_members::NUMERIC / NULLIF(cm.total_members, 0)) * 100, 1) as member_percentage,
    cm.children,
    cm.students,
    ROUND(cm.avg_age, 0) as avg_age,
    cm.new_in_90_days as new_members_90d,
    cg.last_month - cg.two_months_ago as monthly_growth,
    ROUND((cm.total_members::NUMERIC / (SELECT SUM(total_members) FROM campus_metrics)) * 100, 1) as percent_of_church
FROM campus_metrics cm
JOIN campus_growth cg ON cm.campus_name = cg.campus_name
WHERE cm.total_members > 0
ORDER BY cm.total_members DESC;

Communication Reports

Contact Information Completeness Report

-- Analyze completeness of contact information for communication planning
WITH contact_completeness AS (
    SELECT
        p.person_id,
        p.first_name,
        p.last_name,
        p.status,
        p.membership,
        CASE WHEN e.email_id IS NOT NULL THEN 1 ELSE 0 END as has_email,
        CASE WHEN ph.phone_number_id IS NOT NULL THEN 1 ELSE 0 END as has_phone,
        CASE WHEN a.address_id IS NOT NULL THEN 1 ELSE 0 END as has_address,
        CASE WHEN e.blocked = true THEN 1 ELSE 0 END as email_blocked
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_people_relationships er
        ON p.person_id = er.person_id AND er.relationship_type = 'Email'
    LEFT JOIN planning_center.people_emails e
        ON e.email_id = er.relationship_id
        AND e.is_primary = true
    LEFT JOIN planning_center.people_people_relationships pnr
        ON p.person_id = pnr.person_id AND pnr.relationship_type = 'PhoneNumber'
    LEFT JOIN planning_center.people_phone_numbers ph
        ON ph.phone_number_id = pnr.relationship_id
        AND ph.is_primary = true
    LEFT JOIN planning_center.people_people_relationships ar
        ON p.person_id = ar.person_id AND ar.relationship_type = 'Address'
    LEFT JOIN planning_center.people_addresses a
        ON a.address_id = ar.relationship_id
        AND a.is_primary = true
    WHERE p.status = 'active'
),
completeness_summary AS (
    SELECT
        membership,
        COUNT(*) as total_people,
        SUM(has_email) as with_email,
        SUM(has_phone) as with_phone,
        SUM(has_address) as with_address,
        SUM(CASE WHEN has_email = 1 AND has_phone = 1 THEN 1 ELSE 0 END) as email_and_phone,
        SUM(CASE WHEN has_email = 1 AND has_phone = 1 AND has_address = 1 THEN 1 ELSE 0 END) as complete_contact,
        SUM(CASE WHEN has_email = 0 AND has_phone = 0 THEN 1 ELSE 0 END) as no_contact,
        SUM(email_blocked) as emails_blocked
    FROM contact_completeness
    GROUP BY membership
)
SELECT
    COALESCE(membership, 'Unknown') as membership_level,
    total_people,
    with_email,
    ROUND((with_email::NUMERIC / total_people) * 100, 1) as email_percentage,
    with_phone,
    ROUND((with_phone::NUMERIC / total_people) * 100, 1) as phone_percentage,
    with_address,
    ROUND((with_address::NUMERIC / total_people) * 100, 1) as address_percentage,
    complete_contact,
    ROUND((complete_contact::NUMERIC / total_people) * 100, 1) as complete_percentage,
    no_contact,
    emails_blocked
FROM completeness_summary
ORDER BY 
    CASE membership
        WHEN 'Member' THEN 1
        WHEN 'Regular Attender' THEN 2
        WHEN 'Visitor' THEN 3
        ELSE 4
    END;

Growth and Retention Reports

Quarterly Growth Analysis

-- Track membership growth patterns over quarters
WITH quarterly_data AS (
    SELECT
        DATE_TRUNC('quarter', created_at) as quarter,
        COUNT(DISTINCT person_id) as new_people,
        COUNT(DISTINCT CASE WHEN membership = 'Member' THEN person_id END) as new_members,
        COUNT(DISTINCT CASE WHEN membership = 'Regular Attender' THEN person_id END) as new_regular,
        COUNT(DISTINCT CASE WHEN membership = 'Visitor' THEN person_id END) as new_visitors,
        COUNT(DISTINCT CASE WHEN child = true THEN person_id END) as new_children
    FROM planning_center.people_people
    WHERE created_at >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '1 year'
        AND status = 'active'
    GROUP BY DATE_TRUNC('quarter', created_at)
),
inactivated_data AS (
    SELECT
        DATE_TRUNC('quarter', inactivated_at) as quarter,
        COUNT(DISTINCT person_id) as people_inactivated
    FROM planning_center.people_people
    WHERE inactivated_at >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '1 year'
    GROUP BY DATE_TRUNC('quarter', inactivated_at)
),
combined_metrics AS (
    SELECT
        qd.quarter,
        TO_CHAR(qd.quarter, 'Q[Q] YYYY') as quarter_label,
        qd.new_people,
        qd.new_members,
        qd.new_regular,
        qd.new_visitors,
        qd.new_children,
        COALESCE(id.people_inactivated, 0) as people_lost,
        qd.new_people - COALESCE(id.people_inactivated, 0) as net_growth,
        LAG(qd.new_people, 1) OVER (ORDER BY qd.quarter) as prev_quarter_new
    FROM quarterly_data qd
    LEFT JOIN inactivated_data id ON qd.quarter = id.quarter
)
SELECT
    quarter_label,
    new_people,
    new_members,
    new_regular,
    new_visitors,
    people_lost,
    net_growth,
    CASE 
        WHEN prev_quarter_new > 0 THEN 
            ROUND(((new_people - prev_quarter_new)::NUMERIC / prev_quarter_new) * 100, 1)
        ELSE NULL
    END as growth_rate,
    ROUND((new_members::NUMERIC / NULLIF(new_people, 0)) * 100, 1) as member_conversion_rate
FROM combined_metrics
ORDER BY quarter DESC;

Year-End Summary Report

Annual Church Census Report

-- Comprehensive year-end census for annual reports
WITH yearly_stats AS (
    SELECT
        -- Total counts
        COUNT(DISTINCT CASE WHEN status = 'active' THEN person_id END) as total_active,
        COUNT(DISTINCT CASE WHEN status = 'inactive' THEN person_id END) as total_inactive,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Member' THEN person_id END) as members,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Regular Attender' THEN person_id END) as regular_attenders,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Visitor' THEN person_id END) as visitors,
        -- Demographics
        COUNT(DISTINCT CASE WHEN status = 'active' AND child = true THEN person_id END) as children,
        COUNT(DISTINCT CASE WHEN status = 'active' AND graduation_year IS NOT NULL THEN person_id END) as students,
        COUNT(DISTINCT CASE WHEN status = 'active' AND EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) >= 65 THEN person_id END) as seniors,
        -- New this year
        COUNT(DISTINCT CASE 
            WHEN created_at >= DATE_TRUNC('year', CURRENT_DATE) 
            AND status = 'active' 
            THEN person_id 
        END) as new_this_year,
        -- Lost this year
        COUNT(DISTINCT CASE 
            WHEN inactivated_at >= DATE_TRUNC('year', CURRENT_DATE) 
            THEN person_id 
        END) as lost_this_year,
        -- Gender breakdown
        COUNT(DISTINCT CASE WHEN status = 'active' AND gender = 'Male' THEN person_id END) as males,
        COUNT(DISTINCT CASE WHEN status = 'active' AND gender = 'Female' THEN person_id END) as females
    FROM planning_center.people_people
),
household_yearly AS (
    SELECT
        COUNT(DISTINCT household_id) as total_households,
        AVG(member_count) as avg_household_size,
        COUNT(DISTINCT CASE WHEN member_count = 1 THEN household_id END) as single_households,
        COUNT(DISTINCT CASE WHEN member_count >= 4 THEN household_id END) as large_households
    FROM planning_center.people_households
),
campus_count AS (
    SELECT COUNT(DISTINCT campus_id) as campus_count
    FROM planning_center.people_campuses
)
SELECT
    '===========================================' as divider1,
    TO_CHAR(DATE_TRUNC('year', CURRENT_DATE), 'YYYY') || ' ANNUAL CHURCH CENSUS REPORT' as report_title,
    '===========================================' as divider2,
    '' as blank1,
    '📊 MEMBERSHIP OVERVIEW' as section1,
    '-------------------------------------------' as divider3,
    ys.total_active as total_active_people,
    ys.members as full_members,
    ys.regular_attenders,
    ys.visitors,
    ys.new_this_year as people_added_this_year,
    ys.lost_this_year as people_lost_this_year,
    ys.new_this_year - ys.lost_this_year as net_growth,
    '' as blank2,
    '👥 DEMOGRAPHICS' as section2,
    '-------------------------------------------' as divider4,
    ys.children,
    ys.students,
    ys.total_active - ys.children - ys.students - ys.seniors as working_adults,
    ys.seniors,
    ys.males,
    ys.females,
    ROUND(ys.males::NUMERIC / NULLIF(ys.males + ys.females, 0) * 100, 0) as male_percentage,
    '' as blank3,
    '🏠 HOUSEHOLD STATISTICS' as section3,
    '-------------------------------------------' as divider5,
    hy.total_households,
    ROUND(hy.avg_household_size, 1) as avg_household_size,
    hy.single_households as single_person_households,
    hy.large_households as households_4_plus,
    '' as blank4,
    '📍 ORGANIZATIONAL' as section4,
    '-------------------------------------------' as divider6,
    cc.campus_count as number_of_campuses,
    ROUND(ys.total_active::NUMERIC / NULLIF(cc.campus_count, 0), 0) as avg_per_campus,
    '' as blank5,
    '===========================================' as divider7
FROM yearly_stats ys, household_yearly hy, campus_count cc;

Export Tips

These reports can be exported in various formats:

  1. CSV Export: Add \copy (SELECT ...) TO 'report.csv' CSV HEADER;
  2. Excel-Ready: Most results can be copied directly into Excel
  3. Automated Delivery: Schedule these queries to run weekly/monthly
  4. Dashboard Integration: Use these queries as data sources for BI tools

Next Steps

Advanced Planning Center People Queries

Advanced People SQL using window functions and multi-table joins: demographic analysis, geographic spread, engagement scoring, and family structure.

Planning Center Publishing SQL Queries

Query Planning Center Publishing data with SQL to analyze sermon series, speakers, media distribution, and how your content reaches its audience.

On this page

Query RequirementsSchema PrefixRow Level Security (RLS)Executive Dashboard ReportWeekly Church Membership SummaryMonthly Demographic Analysis ReportPastoral Care ReportsBirthday and Anniversary ReportFollow-Up Required ReportHousehold Analysis ReportsFamily Composition ReportCampus Analysis ReportsMulti-Campus Distribution ReportCommunication ReportsContact Information Completeness ReportGrowth and Retention ReportsQuarterly Growth AnalysisYear-End Summary ReportAnnual Church Census ReportExport TipsNext Steps