> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getparable.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Advanced queries

# 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.

<Card title="Query Customization Required" icon="circle-exclamation" color="#FFA500" horizontal>
  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.
</Card>

## 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.

## Table of Contents

* [Demographic Analysis](#demographic-analysis)
* [Engagement Scoring](#engagement-scoring)
* [Family Analytics](#family-analytics)
* [Growth and Retention](#growth-and-retention)
* [Communication Optimization](#communication-optimization)
* [Volunteer Management](#volunteer-management)
* [Predictive Analytics](#predictive-analytics)
* [Performance Optimization](#performance-optimization)

## Demographic Analysis

### Comprehensive Demographic Breakdown

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

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

## Next Steps

Apply these advanced queries to real ministry scenarios:

* [Reporting Examples](/planning-center/people/reporting-examples) - Practical ministry applications and reports