Advanced People Queries

Master complex SQL patterns for deep insights into your congregation. These queries combine multiple tables, use window functions, and employ advanced techniques for comprehensive analysis.

Query Customization Required

These example queries demonstrate common patterns but may require adjustments to match your specific database schema and field names. Test thoroughly in your environment before use.

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 queries. ✅ 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'

Focus your filters on ministry-specific status, engagement, and demographic criteria while relying on RLS for tenancy and system status.

Demographic Analysis
Engagement Scoring
Family Analytics
Growth and Retention
Communication Optimization
Volunteer Management
Predictive Analytics
Performance Optimization

Demographic Analysis

Comprehensive Demographic Breakdown

-- Multi-dimensional demographic analysis
WITH demographic_data AS (
    SELECT 
        p.person_id,
        p.status,
        p.membership,
        -- Age calculations
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        CASE 
            WHEN p.child = true THEN 'Child'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 18 THEN 'Youth'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 30 THEN 'Young Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 50 THEN 'Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 65 THEN 'Middle Age'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) >= 65 THEN 'Senior'
            ELSE 'Unknown'
        END as age_group,
        -- Gender
        COALESCE(p.gender, 'Not Specified') as gender,
        -- Marital status
        ms.name as marital_status,
        -- Campus
        c.name as campus,
        -- Household info
        CASE 
            WHEN h.member_count = 1 THEN 'Single'
            WHEN h.member_count = 2 THEN 'Couple'
            WHEN h.member_count <= 4 THEN 'Small Family'
            ELSE 'Large Family'
        END as household_type
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_marital_statuses ms 
        ON p.marital_status_id = ms.marital_status_id
    LEFT JOIN planning_center.people_people_relationships pr 
        ON p.person_id = pr.person_id 
        AND pr.relationship_type = 'Campus'
    LEFT JOIN planning_center.people_campuses c 
        ON pr.relationship_id = c.campus_id
    LEFT JOIN planning_center.people_household_memberships hm 
        ON p.person_id = hm.person_id
    LEFT JOIN planning_center.people_households h 
        ON hm.household_id = h.household_id
    WHERE p.status = 'active'
),
demographic_summary AS (
    SELECT 
        age_group,
        gender,
        COUNT(*) as count,
        ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage,
        ROUND(AVG(age), 1) as avg_age_in_group,
        COUNT(*) FILTER (WHERE membership = 'Member') as members,
        COUNT(*) FILTER (WHERE marital_status = 'Married') as married,
        COUNT(DISTINCT campus) as campus_representation
    FROM demographic_data
    GROUP BY age_group, gender
)
SELECT 
    age_group,
    gender,
    count,
    percentage || '%' as pct_of_total,
    avg_age_in_group,
    ROUND(members::numeric / count * 100, 1) || '%' as membership_rate,
    ROUND(married::numeric / count * 100, 1) || '%' as married_pct,
    campus_representation
FROM demographic_summary
ORDER BY 
    CASE age_group
        WHEN 'Child' THEN 1
        WHEN 'Youth' THEN 2
        WHEN 'Young Adult' THEN 3
        WHEN 'Adult' THEN 4
        WHEN 'Middle Age' THEN 5
        WHEN 'Senior' THEN 6
        ELSE 7
    END,
    gender;

Geographic Distribution Analysis

-- Analyze where your congregation lives
WITH address_analysis AS (
    SELECT 
        a.city,
        a.state,
        a.zip,
        COUNT(DISTINCT p.person_id) as people_count,
        COUNT(DISTINCT h.household_id) as household_count,
        AVG(EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate))) as avg_age,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.membership = 'Member') as members,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.child = true) as children
    FROM planning_center.people_addresses a
    JOIN planning_center.people_people p 
        ON a.person_id = p.person_id
    LEFT JOIN planning_center.people_household_memberships hm 
        ON p.person_id = hm.person_id
    LEFT JOIN planning_center.people_households h 
        ON hm.household_id = h.household_id
    WHERE p.status = 'active'
      AND a.is_primary = true
    GROUP BY a.city, a.state, a.zip
),
ranked_locations AS (
    SELECT 
        *,
        RANK() OVER (ORDER BY people_count DESC) as popularity_rank,
        SUM(people_count) OVER (ORDER BY people_count DESC) as cumulative_people,
        SUM(people_count) OVER () as total_people
    FROM address_analysis
)
SELECT 
    city,
    state,
    zip,
    people_count,
    household_count,
    ROUND(avg_age, 1) as avg_age,
    ROUND(members::numeric / people_count * 100, 1) || '%' as membership_rate,
    ROUND(children::numeric / people_count * 100, 1) || '%' as children_pct,
    popularity_rank,
    ROUND(cumulative_people::numeric / total_people * 100, 1) || '%' as cumulative_pct
FROM ranked_locations
WHERE popularity_rank <= 20
ORDER BY popularity_rank;

Engagement Scoring

Multi-Factor Engagement Score

-- Calculate comprehensive engagement score for each person
WITH engagement_metrics AS (
    SELECT 
        p.person_id,
        p.name,
        p.membership,
        p.created_at,
        -- Tenure score (max 20 points)
        LEAST(EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.created_at)) * 4, 20) as tenure_score,
        -- Contact completeness (max 15 points)
        (CASE WHEN e.email_id IS NOT NULL THEN 5 ELSE 0 END +
         CASE WHEN pn.phone_number_id IS NOT NULL THEN 5 ELSE 0 END +
         CASE WHEN a.address_id IS NOT NULL THEN 5 ELSE 0 END) as contact_score,
        -- Household participation (max 15 points)
        CASE 
            WHEN h.household_id IS NOT NULL AND h.member_count > 1 THEN 15
            WHEN h.household_id IS NOT NULL THEN 10
            ELSE 0
        END as household_score,
        -- List memberships (max 20 points)
        LEAST(COUNT(DISTINCT lr.list_id) * 5, 20) as list_score,
        -- Form submissions (max 15 points)
        LEAST(COUNT(DISTINCT fs.form_submission_id) * 3, 15) as form_score,
        -- Workflow participation (max 15 points)
        LEAST(COUNT(DISTINCT wc.workflow_card_id) * 5, 15) as workflow_score
    FROM planning_center.people_people p
    -- Contact info
    LEFT JOIN planning_center.people_email_relationships er 
        ON p.person_id = er.person_id
    LEFT JOIN planning_center.people_emails e 
        ON er.email_id = e.email_id AND e.is_primary = true
    LEFT JOIN planning_center.people_phone_number_relationships pnr 
        ON p.person_id = pnr.person_id
    LEFT JOIN planning_center.people_phone_numbers pn 
        ON pnr.phone_number_id = pn.phone_number_id AND pn.is_primary = true
    LEFT JOIN planning_center.people_addresses a 
        ON p.person_id = a.person_id AND a.is_primary = true
    -- Household
    LEFT JOIN planning_center.people_household_memberships hm 
        ON p.person_id = hm.person_id
    LEFT JOIN planning_center.people_households h 
        ON hm.household_id = h.household_id
    -- Lists
    LEFT JOIN planning_center.people_list_results lr 
        ON p.person_id = lr.person_id
    -- Forms
    LEFT JOIN planning_center.people_form_submission_relationships fsr 
        ON p.person_id = fsr.person_id
    LEFT JOIN planning_center.people_form_submissions fs 
        ON fsr.form_submission_id = fs.form_submission_id
    -- Workflows
    LEFT JOIN planning_center.people_workflow_card_relationships wcr 
        ON p.person_id = wcr.person_id
    LEFT JOIN planning_center.people_workflow_cards wc 
        ON wcr.workflow_card_id = wc.workflow_card_id
    WHERE p.status = 'active'
    GROUP BY 
        p.person_id, p.name, p.membership, p.created_at,
        e.email_id, pn.phone_number_id, a.address_id,
        h.household_id, h.member_count
),
scored_people AS (
    SELECT 
        person_id,
        name,
        membership,
        tenure_score,
        contact_score,
        household_score,
        list_score,
        form_score,
        workflow_score,
        tenure_score + contact_score + household_score + 
        list_score + form_score + workflow_score as total_score,
        CASE 
            WHEN tenure_score + contact_score + household_score + 
                 list_score + form_score + workflow_score >= 75 THEN 'Highly Engaged'
            WHEN tenure_score + contact_score + household_score + 
                 list_score + form_score + workflow_score >= 50 THEN 'Engaged'
            WHEN tenure_score + contact_score + household_score + 
                 list_score + form_score + workflow_score >= 25 THEN 'Moderately Engaged'
            WHEN tenure_score + contact_score + household_score + 
                 list_score + form_score + workflow_score >= 10 THEN 'Lightly Engaged'
            ELSE 'New/Inactive'
        END as engagement_level
    FROM engagement_metrics
)
SELECT 
    engagement_level,
    COUNT(*) as people_count,
    ROUND(AVG(total_score), 1) as avg_score,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage,
    COUNT(*) FILTER (WHERE membership = 'Member') as members_in_level
FROM scored_people
GROUP BY engagement_level
ORDER BY avg_score DESC;

Engagement Trajectory

-- Track engagement changes over time
WITH monthly_activity AS (
    SELECT 
        p.person_id,
        p.name,
        DATE_TRUNC('month', activity_date) as month,
        activity_type,
        COUNT(*) as activity_count
    FROM planning_center.people_people p
    CROSS JOIN LATERAL (
        -- Forms submitted
        SELECT fs.created_at as activity_date, 'Form' as activity_type
        FROM planning_center.people_form_submission_relationships fsr
        JOIN planning_center.people_form_submissions fs 
            ON fsr.form_submission_id = fs.form_submission_id
        WHERE fsr.person_id = p.person_id
        UNION ALL
        -- Workflow cards
        SELECT wc.created_at, 'Workflow'
        FROM planning_center.people_workflow_card_relationships wcr
        JOIN planning_center.people_workflow_cards wc 
            ON wcr.workflow_card_id = wc.workflow_card_id
        WHERE wcr.person_id = p.person_id
        UNION ALL
        -- Notes added
        SELECT n.created_at, 'Note'
        FROM planning_center.people_note_relationships nr
        JOIN planning_center.people_notes n 
            ON nr.note_id = n.note_id
        WHERE nr.person_id = p.person_id
    ) activities
    WHERE p.status = 'active'
      AND activity_date >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY p.person_id, p.name, month, activity_type
),
engagement_trend AS (
    SELECT 
        person_id,
        name,
        month,
        SUM(activity_count) as total_activities,
        LAG(SUM(activity_count)) OVER (PARTITION BY person_id ORDER BY month) as prev_month_activities,
        AVG(SUM(activity_count)) OVER (PARTITION BY person_id) as avg_activities
    FROM monthly_activity
    GROUP BY person_id, name, month
)
SELECT 
    person_id,
    name,
    COUNT(DISTINCT month) as active_months,
    SUM(total_activities) as total_activities_6mo,
    ROUND(AVG(total_activities), 1) as avg_monthly_activities,
    MAX(total_activities) as peak_month_activities,
    CASE 
        WHEN SUM(CASE WHEN total_activities > prev_month_activities THEN 1 ELSE 0 END) > 
             SUM(CASE WHEN total_activities < prev_month_activities THEN 1 ELSE 0 END) 
        THEN 'Increasing'
        WHEN SUM(CASE WHEN total_activities < prev_month_activities THEN 1 ELSE 0 END) > 
             SUM(CASE WHEN total_activities > prev_month_activities THEN 1 ELSE 0 END)
        THEN 'Decreasing'
        ELSE 'Stable'
    END as engagement_trend
FROM engagement_trend
GROUP BY person_id, name
HAVING COUNT(DISTINCT month) >= 3  -- At least 3 months of data
ORDER BY total_activities_6mo DESC
LIMIT 100;

Family Analytics

Family Composition Analysis

-- Detailed family structure analysis
WITH family_composition AS (
    SELECT 
        h.household_id,
        h.name as family_name,
        h.member_count,
        COUNT(DISTINCT p.person_id) as actual_members,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.child = true) as children,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.child = false OR p.child IS NULL) as adults,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.gender = 'Male' AND (p.child = false OR p.child IS NULL)) as adult_males,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.gender = 'Female' AND (p.child = false OR p.child IS NULL)) as adult_females,
        MIN(p.birthdate) FILTER (WHERE p.child = false OR p.child IS NULL) as oldest_adult_birthdate,
        MAX(p.birthdate) FILTER (WHERE p.child = true) as youngest_child_birthdate,
        ARRAY_AGG(DISTINCT p.grade ORDER BY p.grade) FILTER (WHERE p.grade IS NOT NULL) as children_grades
    FROM planning_center.people_households h
    JOIN planning_center.people_household_memberships hm 
        ON h.household_id = hm.household_id
    JOIN planning_center.people_people p 
        ON hm.person_id = p.person_id
    WHERE p.status = 'active'
      AND hm.pending = false
    GROUP BY h.household_id, h.name, h.member_count
),
family_types AS (
    SELECT 
        *,
        CASE 
            WHEN adults = 1 AND children = 0 THEN 'Single Adult'
            WHEN adults = 2 AND children = 0 THEN 'Couple'
            WHEN adults = 1 AND children > 0 THEN 'Single Parent'
            WHEN adults = 2 AND children > 0 THEN 'Nuclear Family'
            WHEN adults > 2 AND children > 0 THEN 'Extended Family'
            WHEN adults > 2 AND children = 0 THEN 'Adult Household'
            ELSE 'Other'
        END as family_type,
        CASE 
            WHEN children = 0 THEN 'No Children'
            WHEN youngest_child_birthdate > CURRENT_DATE - INTERVAL '5 years' THEN 'Young Children'
            WHEN youngest_child_birthdate > CURRENT_DATE - INTERVAL '12 years' THEN 'Elementary Age'
            WHEN youngest_child_birthdate > CURRENT_DATE - INTERVAL '18 years' THEN 'Teenagers'
            ELSE 'Adult Children'
        END as children_stage
    FROM family_composition
)
SELECT 
    family_type,
    children_stage,
    COUNT(*) as family_count,
    ROUND(AVG(actual_members), 1) as avg_size,
    ROUND(AVG(children), 1) as avg_children,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM family_types
GROUP BY family_type, children_stage
ORDER BY family_count DESC;

Multi-Generational Households

-- Identify multi-generational families
WITH household_ages AS (
    SELECT 
        h.household_id,
        h.name as household_name,
        p.person_id,
        p.name as person_name,
        p.birthdate,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        CASE 
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 18 THEN 'Child'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 40 THEN 'Young Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 65 THEN 'Middle Age'
            ELSE 'Senior'
        END as generation
    FROM planning_center.people_households h
    JOIN planning_center.people_household_memberships hm 
        ON h.household_id = hm.household_id
    JOIN planning_center.people_people p 
        ON hm.person_id = p.person_id
    WHERE p.status = 'active'
      AND p.birthdate IS NOT NULL
      AND hm.pending = false
),
generation_analysis AS (
    SELECT 
        household_id,
        household_name,
        COUNT(DISTINCT generation) as generation_count,
        COUNT(DISTINCT person_id) as member_count,
        MAX(age) - MIN(age) as age_span,
        STRING_AGG(DISTINCT generation, ', ' ORDER BY generation) as generations_present,
        MIN(age) as youngest_age,
        MAX(age) as oldest_age
    FROM household_ages
    GROUP BY household_id, household_name
)
SELECT 
    household_id,
    household_name,
    member_count,
    generation_count,
    generations_present,
    age_span as age_span_years,
    youngest_age,
    oldest_age,
    CASE 
        WHEN generation_count >= 3 THEN 'Multi-Generational'
        WHEN generation_count = 2 AND age_span > 30 THEN 'Likely Multi-Gen'
        WHEN generation_count = 2 THEN 'Two Generations'
        ELSE 'Single Generation'
    END as household_type
FROM generation_analysis
WHERE generation_count >= 2
ORDER BY generation_count DESC, age_span DESC;

Growth and Retention

Cohort Retention Analysis

-- Track retention by cohort over time
WITH cohort_base AS (
    SELECT 
        DATE_TRUNC('month', created_at) as cohort_month,
        person_id,
        created_at,
        status,
        inactivated_at
    FROM planning_center.people_people
    WHERE created_at >= CURRENT_DATE - INTERVAL '24 months'
),
retention_calc AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT person_id) as cohort_size,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active' 
            OR inactivated_at > cohort_month + INTERVAL '1 month'
        ) as month_1,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active' 
            OR inactivated_at > cohort_month + INTERVAL '3 months'
        ) as month_3,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active' 
            OR inactivated_at > cohort_month + INTERVAL '6 months'
        ) as month_6,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active' 
            OR inactivated_at > cohort_month + INTERVAL '12 months'
        ) as month_12,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active'
        ) as still_active
    FROM cohort_base
    GROUP BY cohort_month
)
SELECT 
    TO_CHAR(cohort_month, 'YYYY-MM') as cohort,
    cohort_size as started,
    ROUND(month_1::numeric / cohort_size * 100, 1) as pct_retained_1mo,
    ROUND(month_3::numeric / cohort_size * 100, 1) as pct_retained_3mo,
    ROUND(month_6::numeric / cohort_size * 100, 1) as pct_retained_6mo,
    ROUND(month_12::numeric / cohort_size * 100, 1) as pct_retained_12mo,
    ROUND(still_active::numeric / cohort_size * 100, 1) as pct_still_active,
    still_active as currently_active
FROM retention_calc
WHERE cohort_month <= CURRENT_DATE - INTERVAL '1 month'
ORDER BY cohort_month DESC;

Growth Velocity Analysis

-- Analyze growth patterns and velocity
WITH weekly_metrics AS (
    SELECT 
        DATE_TRUNC('week', d.date) as week,
        -- New people
        COUNT(DISTINCT p.person_id) FILTER (
            WHERE DATE_TRUNC('week', p.created_at) = DATE_TRUNC('week', d.date)
        ) as new_people,
        -- Inactivated people
        COUNT(DISTINCT p.person_id) FILTER (
            WHERE DATE_TRUNC('week', p.inactivated_at) = DATE_TRUNC('week', d.date)
        ) as inactivated_people,
        -- Total active at end of week
        COUNT(DISTINCT p.person_id) FILTER (
            WHERE p.created_at <= d.date + INTERVAL '6 days'
            AND (p.inactivated_at IS NULL OR p.inactivated_at > d.date + INTERVAL '6 days')
        ) as total_active
    FROM generate_series(
        CURRENT_DATE - INTERVAL '12 weeks',
        CURRENT_DATE,
        INTERVAL '1 week'
    ) d(date)
    CROSS JOIN planning_center.people_people p
    GROUP BY DATE_TRUNC('week', d.date)
),
growth_analysis AS (
    SELECT 
        week,
        new_people,
        inactivated_people,
        new_people - inactivated_people as net_growth,
        total_active,
        LAG(total_active) OVER (ORDER BY week) as prev_week_active,
        AVG(new_people) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as avg_new_4wk,
        AVG(new_people - inactivated_people) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as avg_net_4wk
    FROM weekly_metrics
)
SELECT 
    TO_CHAR(week, 'YYYY-MM-DD') as week_starting,
    new_people,
    inactivated_people,
    net_growth,
    total_active,
    ROUND((total_active - prev_week_active)::numeric / NULLIF(prev_week_active, 0) * 100, 2) as weekly_growth_rate,
    ROUND(avg_new_4wk, 1) as rolling_avg_new,
    ROUND(avg_net_4wk, 1) as rolling_avg_net,
    CASE 
        WHEN net_growth > avg_net_4wk * 1.5 THEN 'Accelerating'
        WHEN net_growth > avg_net_4wk THEN 'Above Average'
        WHEN net_growth > 0 THEN 'Growing'
        WHEN net_growth = 0 THEN 'Flat'
        ELSE 'Declining'
    END as growth_status
FROM growth_analysis
WHERE week <= CURRENT_DATE
ORDER BY week DESC;

Communication Optimization

Communication Preference Analysis

-- Analyze best communication channels by demographic
WITH communication_channels AS (
    SELECT 
        p.person_id,
        p.name,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        p.gender,
        p.membership,
        -- Email status
        CASE 
            WHEN e.email_id IS NOT NULL AND e.blocked = false THEN 'Has Email'
            WHEN e.email_id IS NOT NULL AND e.blocked = true THEN 'Email Blocked'
            ELSE 'No Email'
        END as email_status,
        -- Phone status
        CASE 
            WHEN pn.phone_number_id IS NOT NULL AND pn.carrier_name IS NOT NULL THEN 'SMS Capable'
            WHEN pn.phone_number_id IS NOT NULL THEN 'Voice Only'
            ELSE 'No Phone'
        END as phone_status,
        -- Physical mail
        CASE 
            WHEN a.address_id IS NOT NULL THEN 'Has Address'
            ELSE 'No Address'
        END as mail_status
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_email_relationships er 
        ON p.person_id = er.person_id
    LEFT JOIN planning_center.people_emails e 
        ON er.email_id = e.email_id AND e.is_primary = true
    LEFT JOIN planning_center.people_phone_number_relationships pnr 
        ON p.person_id = pnr.person_id
    LEFT JOIN planning_center.people_phone_numbers pn 
        ON pnr.phone_number_id = pn.phone_number_id AND pn.is_primary = true
    LEFT JOIN planning_center.people_addresses a 
        ON p.person_id = a.person_id AND a.is_primary = true
    WHERE p.status = 'active'
),
channel_summary AS (
    SELECT 
        CASE 
            WHEN age < 30 THEN 'Under 30'
            WHEN age < 50 THEN '30-49'
            WHEN age < 70 THEN '50-69'
            WHEN age >= 70 THEN '70+'
            ELSE 'Unknown'
        END as age_group,
        COUNT(*) as total_people,
        COUNT(*) FILTER (WHERE email_status = 'Has Email') as has_email,
        COUNT(*) FILTER (WHERE phone_status = 'SMS Capable') as has_sms,
        COUNT(*) FILTER (WHERE phone_status IN ('SMS Capable', 'Voice Only')) as has_phone,
        COUNT(*) FILTER (WHERE mail_status = 'Has Address') as has_address,
        -- Best channel determination
        COUNT(*) FILTER (WHERE email_status = 'Has Email' AND phone_status = 'SMS Capable') as multi_channel,
        COUNT(*) FILTER (WHERE email_status != 'Has Email' AND phone_status != 'SMS Capable' AND mail_status = 'Has Address') as mail_only
    FROM communication_channels
    GROUP BY age_group
)
SELECT 
    age_group,
    total_people,
    ROUND(has_email::numeric / total_people * 100, 1) || '%' as email_reach,
    ROUND(has_sms::numeric / total_people * 100, 1) || '%' as sms_reach,
    ROUND(has_phone::numeric / total_people * 100, 1) || '%' as phone_reach,
    ROUND(has_address::numeric / total_people * 100, 1) || '%' as mail_reach,
    ROUND(multi_channel::numeric / total_people * 100, 1) || '%' as multi_channel_pct,
    mail_only as mail_only_count,
    CASE 
        WHEN has_sms::numeric / total_people > 0.7 THEN 'SMS Preferred'
        WHEN has_email::numeric / total_people > 0.8 THEN 'Email Preferred'
        WHEN has_phone::numeric / total_people > 0.9 THEN 'Phone Preferred'
        ELSE 'Mixed Channels'
    END as recommended_primary
FROM channel_summary
ORDER BY 
    CASE age_group
        WHEN 'Under 30' THEN 1
        WHEN '30-49' THEN 2
        WHEN '50-69' THEN 3
        WHEN '70+' THEN 4
        ELSE 5
    END;

Volunteer Management

Volunteer Capacity Analysis

-- Identify volunteer capacity and opportunities
WITH volunteer_data AS (
    SELECT 
        p.person_id,
        p.name,
        p.membership,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        -- Background check status
        CASE 
            WHEN bc.background_check_id IS NOT NULL AND bc.status = 'passed' THEN 'Cleared'
            WHEN bc.background_check_id IS NOT NULL AND bc.status = 'pending' THEN 'Pending'
            WHEN bc.background_check_id IS NOT NULL THEN 'Failed'
            ELSE 'Not Checked'
        END as background_status,
        -- Lists (as proxy for ministry involvement)
        COUNT(DISTINCT lr.list_id) as list_memberships,
        -- Workflows completed
        COUNT(DISTINCT wc.workflow_card_id) FILTER (WHERE wc.completed_at IS NOT NULL) as completed_workflows,
        -- Custom fields for skills/interests (example)
        MAX(CASE WHEN fd.name = 'Skills' THEN fdat.value END) as skills,
        MAX(CASE WHEN fd.name = 'Availability' THEN fdat.value END) as availability
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_background_check_relationships bcr 
        ON p.person_id = bcr.person_id
    LEFT JOIN planning_center.people_background_checks bc 
        ON bcr.background_check_id = bc.background_check_id
    LEFT JOIN planning_center.people_list_results lr 
        ON p.person_id = lr.person_id
    LEFT JOIN planning_center.people_workflow_card_relationships wcr 
        ON p.person_id = wcr.person_id
    LEFT JOIN planning_center.people_workflow_cards wc 
        ON wcr.workflow_card_id = wc.workflow_card_id
    LEFT JOIN planning_center.people_field_data_relationships fdr 
        ON p.person_id = fdr.person_id
    LEFT JOIN planning_center.people_field_data fdat 
        ON fdr.field_data_id = fdat.field_data_id
    LEFT JOIN planning_center.people_field_definitions fd 
        ON fdat.field_definition_id = fd.field_definition_id
    WHERE p.status = 'active'
      AND p.child = false  -- Adults only
      AND (p.membership = 'Member' OR list_memberships > 0)  -- Members or involved
    GROUP BY p.person_id, p.name, p.membership, p.birthdate, bc.background_check_id, bc.status
),
volunteer_segments AS (
    SELECT 
        person_id,
        name,
        age,
        background_status,
        list_memberships,
        completed_workflows,
        CASE 
            WHEN list_memberships >= 3 THEN 'Highly Active'
            WHEN list_memberships >= 1 THEN 'Active'
            WHEN membership = 'Member' THEN 'Available'
            ELSE 'Potential'
        END as volunteer_status,
        CASE 
            WHEN background_status = 'Cleared' AND age >= 18 THEN 'Children/Youth Ready'
            WHEN age >= 18 AND age < 65 THEN 'General Service Ready'
            WHEN age >= 65 THEN 'Senior Service Ready'
            ELSE 'Not Ready'
        END as service_readiness
    FROM volunteer_data
)
SELECT 
    volunteer_status,
    service_readiness,
    COUNT(*) as people_count,
    ROUND(AVG(age), 1) as avg_age,
    COUNT(*) FILTER (WHERE background_status = 'Cleared') as background_cleared,
    ROUND(AVG(list_memberships), 1) as avg_involvements,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM volunteer_segments
GROUP BY volunteer_status, service_readiness
ORDER BY 
    CASE volunteer_status
        WHEN 'Highly Active' THEN 1
        WHEN 'Active' THEN 2
        WHEN 'Available' THEN 3
        ELSE 4
    END,
    service_readiness;

Predictive Analytics

Churn Risk Prediction

-- Identify people at risk of becoming inactive
WITH activity_metrics AS (
    SELECT 
        p.person_id,
        p.name,
        p.created_at,
        p.membership,
        -- Recent activity indicators
        MAX(fs.created_at) as last_form_submission,
        MAX(wc.created_at) as last_workflow_activity,
        MAX(n.created_at) as last_note,
        -- Engagement frequency
        COUNT(DISTINCT DATE_TRUNC('month', fs.created_at)) as active_months_forms,
        COUNT(DISTINCT DATE_TRUNC('month', wc.created_at)) as active_months_workflows,
        -- Connection strength
        COUNT(DISTINCT lr.list_id) as list_count,
        CASE WHEN h.household_id IS NOT NULL THEN 1 ELSE 0 END as has_household
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_form_submission_relationships fsr 
        ON p.person_id = fsr.person_id
    LEFT JOIN planning_center.people_form_submissions fs 
        ON fsr.form_submission_id = fs.form_submission_id
    LEFT JOIN planning_center.people_workflow_card_relationships wcr 
        ON p.person_id = wcr.person_id
    LEFT JOIN planning_center.people_workflow_cards wc 
        ON wcr.workflow_card_id = wc.workflow_card_id
    LEFT JOIN planning_center.people_note_relationships nr 
        ON p.person_id = nr.person_id
    LEFT JOIN planning_center.people_notes n 
        ON nr.note_id = n.note_id
    LEFT JOIN planning_center.people_list_results lr 
        ON p.person_id = lr.person_id
    LEFT JOIN planning_center.people_household_memberships hm 
        ON p.person_id = hm.person_id
    LEFT JOIN planning_center.people_households h 
        ON hm.household_id = h.household_id
    WHERE p.status = 'active'
      AND p.created_at < CURRENT_DATE - INTERVAL '90 days'  -- Established people only
    GROUP BY p.person_id, p.name, p.created_at, p.membership, h.household_id
),
risk_scoring AS (
    SELECT 
        person_id,
        name,
        membership,
        -- Risk factors
        CASE WHEN COALESCE(last_form_submission, last_workflow_activity, last_note) < CURRENT_DATE - INTERVAL '90 days' 
             OR COALESCE(last_form_submission, last_workflow_activity, last_note) IS NULL 
             THEN 3 ELSE 0 END as inactivity_risk,
        CASE WHEN list_count = 0 THEN 2 ELSE 0 END as disconnection_risk,
        CASE WHEN has_household = 0 THEN 1 ELSE 0 END as isolation_risk,
        CASE WHEN active_months_forms + active_months_workflows < 3 THEN 2 ELSE 0 END as low_engagement_risk,
        -- Last activity
        GREATEST(
            COALESCE(last_form_submission, '1900-01-01'::timestamp),
            COALESCE(last_workflow_activity, '1900-01-01'::timestamp),
            COALESCE(last_note, '1900-01-01'::timestamp)
        ) as last_activity,
        -- Engagement score
        active_months_forms + active_months_workflows as total_active_months,
        list_count
    FROM activity_metrics
)
SELECT 
    person_id,
    name,
    membership,
    TO_CHAR(last_activity, 'YYYY-MM-DD') as last_seen,
    CURRENT_DATE - last_activity::date as days_inactive,
    inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk as total_risk_score,
    CASE 
        WHEN inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk >= 5 THEN 'High Risk'
        WHEN inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk >= 3 THEN 'Medium Risk'
        WHEN inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk >= 1 THEN 'Low Risk'
        ELSE 'Stable'
    END as risk_level,
    ARRAY_REMOVE(ARRAY[
        CASE WHEN inactivity_risk > 0 THEN 'Long Inactivity' END,
        CASE WHEN disconnection_risk > 0 THEN 'No Groups/Lists' END,
        CASE WHEN isolation_risk > 0 THEN 'No Household' END,
        CASE WHEN low_engagement_risk > 0 THEN 'Low Engagement' END
    ], NULL) as risk_factors
FROM risk_scoring
WHERE inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk > 0
ORDER BY total_risk_score DESC, days_inactive DESC
LIMIT 100;

Performance Optimization

Materialized View for Dashboard

-- Create a materialized view for frequently accessed metrics
CREATE MATERIALIZED VIEW IF NOT EXISTS people_dashboard_metrics AS
WITH base_metrics AS (
    SELECT 
        p.person_id,
        p.status,
        p.membership,
        p.child,
        p.gender,
        p.created_at,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        -- Campus
        c.name as campus,
        -- Household
        h.member_count as household_size,
        -- Contact completeness
        CASE WHEN e.email_id IS NOT NULL THEN 1 ELSE 0 END as has_email,
        CASE WHEN pn.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
    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 = 'Campus'
    LEFT JOIN planning_center.people_campuses c 
        ON pr.relationship_id = c.campus_id
    LEFT JOIN planning_center.people_household_memberships hm 
        ON p.person_id = hm.person_id
    LEFT JOIN planning_center.people_households h 
        ON hm.household_id = h.household_id
    LEFT JOIN planning_center.people_email_relationships er 
        ON p.person_id = er.person_id
    LEFT JOIN planning_center.people_emails e 
        ON er.email_id = e.email_id AND e.is_primary = true
    LEFT JOIN planning_center.people_phone_number_relationships pnr 
        ON p.person_id = pnr.person_id
    LEFT JOIN planning_center.people_phone_numbers pn 
        ON pnr.phone_number_id = pn.phone_number_id AND pn.is_primary = true
    LEFT JOIN planning_center.people_addresses a 
        ON p.person_id = a.person_id AND a.is_primary = true
)
SELECT 
    COUNT(*) FILTER (WHERE status = 'active') as total_active,
    COUNT(*) FILTER (WHERE status = 'inactive') as total_inactive,
    COUNT(*) FILTER (WHERE membership = 'Member') as total_members,
    COUNT(*) FILTER (WHERE child = true) as total_children,
    COUNT(*) FILTER (WHERE gender = 'Male') as total_males,
    COUNT(*) FILTER (WHERE gender = 'Female') as total_females,
    AVG(age) FILTER (WHERE age IS NOT NULL) as avg_age,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '30 days') as new_last_30_days,
    COUNT(*) FILTER (WHERE has_email = 1) as with_email,
    COUNT(*) FILTER (WHERE has_phone = 1) as with_phone,
    COUNT(*) FILTER (WHERE has_address = 1) as with_address,
    COUNT(DISTINCT campus) as campus_count,
    AVG(household_size) as avg_household_size,
    CURRENT_TIMESTAMP as last_refreshed
FROM base_metrics;

-- Create indexes on the materialized view
CREATE INDEX idx_people_dashboard_refresh ON people_dashboard_metrics(last_refreshed);

-- Refresh the materialized view (schedule this regularly)
-- REFRESH MATERIALIZED VIEW CONCURRENTLY people_dashboard_metrics;

Best Practices

Use CTEs for Clarity: Break complex queries into logical steps
Leverage Window Functions: Use OVER() for running totals and comparisons
Filter Early: Apply WHERE clauses as early as possible
Index Strategic Columns: Ensure frequently joined/filtered columns are indexed
Monitor Query Performance: Use EXPLAIN ANALYZE for optimization

Next Steps

Apply these advanced queries to real ministry scenarios:

Reporting Examples - Practical ministry applications and reports

Advanced queries

Advanced People Queries

Query Customization Required

Query Requirements

Schema Prefix

Row Level Security (RLS)

Table of Contents

Demographic Analysis

Comprehensive Demographic Breakdown

Geographic Distribution Analysis

Engagement Scoring

Multi-Factor Engagement Score

Engagement Trajectory

Family Analytics

Family Composition Analysis

Multi-Generational Households

Growth and Retention

Cohort Retention Analysis

Growth Velocity Analysis

Communication Optimization

Communication Preference Analysis

Volunteer Management

Volunteer Capacity Analysis

Predictive Analytics

Churn Risk Prediction

Performance Optimization

Materialized View for Dashboard

Best Practices

Next Steps

​Advanced People Queries

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Table of Contents

​Demographic Analysis

​Comprehensive Demographic Breakdown

​Geographic Distribution Analysis

​Engagement Scoring

​Multi-Factor Engagement Score

​Engagement Trajectory

​Family Analytics

​Family Composition Analysis

​Multi-Generational Households

​Growth and Retention

​Cohort Retention Analysis

​Growth Velocity Analysis

​Communication Optimization

​Communication Preference Analysis

​Volunteer Management

​Volunteer Capacity Analysis

​Predictive Analytics

​Churn Risk Prediction

​Performance Optimization

​Materialized View for Dashboard

​Best Practices

​Next Steps

Advanced People Queries

Query Requirements

Schema Prefix

Row Level Security (RLS)

Table of Contents

Demographic Analysis

Comprehensive Demographic Breakdown

Geographic Distribution Analysis

Engagement Scoring

Multi-Factor Engagement Score

Engagement Trajectory

Family Analytics

Family Composition Analysis

Multi-Generational Households

Growth and Retention

Cohort Retention Analysis

Growth Velocity Analysis

Communication Optimization

Communication Preference Analysis

Volunteer Management

Volunteer Capacity Analysis

Predictive Analytics

Churn Risk Prediction

Performance Optimization

Materialized View for Dashboard

Best Practices

Next Steps