> ## 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 Groups Queries

Master complex SQL patterns to gain deep insights into your groups ministry. These queries combine multiple tables, use window functions, and employ advanced SQL techniques.

<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 Groups module live in the `planning_center` schema. Always prefix table names with `planning_center.` in advanced queries.

✅ CORRECT: `SELECT * FROM planning_center.groups_memberships`
❌ INCORRECT: `SELECT * FROM groups_memberships`

### Row Level Security (RLS)

Row Level Security automatically enforces:

* **tenant\_organization\_id** – results scoped to your organization
* **system\_status** – active records returned by default

**Skip manual filters for these columns**—RLS already applies them and redundant predicates can suppress data or degrade performance:

* ❌ `WHERE tenant_organization_id = 1`
* ❌ `WHERE system_status = 'active'`

Focus your logic on ministry-specific signals (archived status, leader roles, attendance) while trusting RLS for tenancy and system status.

## Table of Contents

* [Group Health Metrics](#group-health-metrics)
* [Attendance Analytics](#attendance-analytics)
* [Member Engagement Scoring](#member-engagement-scoring)
* [Growth Trends](#growth-trends)
* [Leadership Analysis](#leadership-analysis)
* [Predictive Indicators](#predictive-indicators)
* [Performance Optimization](#performance-optimization)

## Group Health Metrics

### Comprehensive Group Health Score

```sql theme={null}
-- Calculate a health score for each group based on multiple factors
WITH group_metrics AS (
    SELECT 
        g.group_id,
        g.name,
        g.memberships_count,
        g.created_at,
        -- Member metrics
        COUNT(DISTINCT m.person_id) as actual_members,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.person_id END) as leader_count,
        -- Event metrics (last 90 days)
        COUNT(DISTINCT e.event_id) as recent_events,
        COUNT(DISTINCT CASE WHEN e.canceled = false THEN e.event_id END) as completed_events,
        MAX(e.starts_at) as last_event_date,
        -- Attendance metrics
        AVG(CASE WHEN a.attended = true THEN 1 ELSE 0 END) as avg_attendance_rate,
        -- Calculate weeks since creation
        EXTRACT(EPOCH FROM (CURRENT_DATE - g.created_at)) / 604800 as weeks_active
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m 
        ON g.group_id = m.group_id
    LEFT JOIN planning_center.groups_event_relationships er 
        ON g.group_id = er.group_id 
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e 
        ON er.relationship_id = e.event_id 
        AND e.starts_at >= CURRENT_DATE - INTERVAL '90 days'
    LEFT JOIN planning_center.groups_attendance_relationships aer 
        ON e.event_id = aer.attendance_id 
        AND aer.relationship_type = 'Event'
    LEFT JOIN planning_center.groups_attendances a 
        ON aer.relationship_id = a.attendance_id
    WHERE g.archived_at IS NULL
    GROUP BY g.group_id, g.name, g.memberships_count, g.created_at
)
SELECT 
    group_id,
    name,
    actual_members,
    leader_count,
    recent_events,
    ROUND(avg_attendance_rate * 100, 1) as attendance_rate,
    -- Calculate health score (0-100)
    ROUND(
        (
            -- Size score (optimal 8-15 members)
            CASE 
                WHEN actual_members BETWEEN 8 AND 15 THEN 25
                WHEN actual_members BETWEEN 6 AND 7 OR actual_members BETWEEN 16 AND 20 THEN 15
                WHEN actual_members > 0 THEN 5
                ELSE 0
            END +
            -- Leadership score
            CASE 
                WHEN leader_count >= 2 THEN 25
                WHEN leader_count = 1 THEN 15
                ELSE 0
            END +
            -- Activity score
            CASE 
                WHEN recent_events >= 12 THEN 25  -- Weekly meetings
                WHEN recent_events >= 6 THEN 15   -- Bi-weekly
                WHEN recent_events >= 3 THEN 10   -- Monthly
                WHEN recent_events > 0 THEN 5
                ELSE 0
            END +
            -- Attendance score
            COALESCE(avg_attendance_rate * 25, 0)
        )::numeric, 
        1
    ) as health_score,
    -- Status indicators
    CASE 
        WHEN last_event_date IS NULL THEN 'No Events'
        WHEN last_event_date < CURRENT_DATE - INTERVAL '30 days' THEN 'Inactive'
        WHEN last_event_date < CURRENT_DATE - INTERVAL '14 days' THEN 'Low Activity'
        ELSE 'Active'
    END as activity_status
FROM group_metrics
ORDER BY health_score DESC;
```

### Groups at Risk

```sql theme={null}
-- Identify groups that may need pastoral attention
WITH risk_indicators AS (
    SELECT 
        g.group_id,
        g.name,
        g.memberships_count,
        -- Member risk factors
        COUNT(DISTINCT m.person_id) as current_members,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.person_id END) as leaders,
        COUNT(DISTINCT CASE WHEN m.joined_at > CURRENT_DATE - INTERVAL '30 days' THEN m.person_id END) as new_members,
        -- Event risk factors
        COUNT(DISTINCT e.event_id) FILTER (WHERE e.starts_at > CURRENT_DATE - INTERVAL '30 days') as recent_events,
        COUNT(DISTINCT e.event_id) FILTER (WHERE e.canceled = true AND e.canceled_at > CURRENT_DATE - INTERVAL '30 days') as canceled_events,
        MAX(e.starts_at) as last_event,
        -- Calculate risk factors
        CASE WHEN COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.person_id END) = 0 THEN 1 ELSE 0 END as no_leader,
        CASE WHEN g.memberships_count <= 3 THEN 1 ELSE 0 END as too_small,
        CASE WHEN MAX(e.starts_at) < CURRENT_DATE - INTERVAL '30 days' OR MAX(e.starts_at) IS NULL THEN 1 ELSE 0 END as inactive
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m ON g.group_id = m.group_id
    LEFT JOIN planning_center.groups_event_relationships er ON g.group_id = er.group_id AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e ON er.relationship_id = e.event_id
    WHERE g.archived_at IS NULL
    GROUP BY g.group_id, g.name, g.memberships_count
)
SELECT 
    group_id,
    name,
    current_members,
    leaders,
    recent_events,
    canceled_events,
    no_leader + too_small + inactive as risk_score,
    ARRAY_REMOVE(ARRAY[
        CASE WHEN no_leader = 1 THEN 'No Leader' END,
        CASE WHEN too_small = 1 THEN 'Too Small' END,
        CASE WHEN inactive = 1 THEN 'Inactive' END
    ], NULL) as risk_factors,
    CASE 
        WHEN no_leader + too_small + inactive >= 2 THEN 'High Risk'
        WHEN no_leader + too_small + inactive = 1 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END as risk_level
FROM risk_indicators
WHERE no_leader + too_small + inactive > 0
ORDER BY risk_score DESC, current_members;
```

## Attendance Analytics

### Attendance Patterns by Day and Time

```sql theme={null}
-- Analyze when groups meet and attendance rates
WITH event_attendance AS (
    SELECT 
        e.event_id,
        e.name as event_name,
        e.starts_at,
        EXTRACT(DOW FROM e.starts_at) as day_of_week,
        EXTRACT(HOUR FROM e.starts_at) as hour_of_day,
        TO_CHAR(e.starts_at, 'Day') as day_name,
        CASE 
            WHEN EXTRACT(HOUR FROM e.starts_at) < 12 THEN 'Morning'
            WHEN EXTRACT(HOUR FROM e.starts_at) < 17 THEN 'Afternoon'
            ELSE 'Evening'
        END as time_period,
        COUNT(a.attendance_id) as total_registered,
        COUNT(a.attendance_id) FILTER (WHERE a.attended = true) as attended_count
    FROM planning_center.groups_events e
    LEFT JOIN planning_center.groups_attendance_relationships ar 
        ON e.event_id = ar.attendance_id 
        AND ar.relationship_type = 'Event'
    LEFT JOIN planning_center.groups_attendances a 
        ON ar.relationship_id = a.attendance_id
    WHERE e.starts_at >= CURRENT_DATE - INTERVAL '90 days'
      AND e.starts_at < CURRENT_DATE
      AND e.canceled = false
    GROUP BY e.event_id, e.name, e.starts_at
)
SELECT 
    day_name,
    time_period,
    COUNT(*) as event_count,
    SUM(total_registered) as total_registered,
    SUM(attended_count) as total_attended,
    ROUND(AVG(CASE WHEN total_registered > 0 
        THEN attended_count::numeric / total_registered * 100 
        ELSE 0 END), 1) as avg_attendance_rate,
    ROUND(AVG(attended_count), 1) as avg_attendees_per_event
FROM event_attendance
GROUP BY day_of_week, day_name, time_period
ORDER BY day_of_week, 
    CASE time_period 
        WHEN 'Morning' THEN 1 
        WHEN 'Afternoon' THEN 2 
        ELSE 3 
    END;
```

### Member Attendance Consistency

```sql theme={null}
-- Identify consistent vs sporadic attendees
WITH member_attendance AS (
    SELECT 
        p.person_id,
        m.group_id,
        g.name as group_name,
        COUNT(DISTINCT e.event_id) as events_available,
        COUNT(DISTINCT CASE WHEN a.attended = true THEN e.event_id END) as events_attended,
        MIN(e.starts_at) as first_event,
        MAX(e.starts_at) as last_event,
        -- Calculate weeks between first and last event
        GREATEST(1, EXTRACT(EPOCH FROM (MAX(e.starts_at) - MIN(e.starts_at))) / 604800) as weeks_span
    FROM planning_center.groups_people p
    JOIN planning_center.groups_memberships m ON p.person_id = m.person_id
    JOIN planning_center.groups_groups g ON m.group_id = g.group_id
    JOIN planning_center.groups_event_relationships er ON g.group_id = er.group_id AND er.relationship_type = 'Group'
    JOIN planning_center.groups_events e ON er.relationship_id = e.event_id
    LEFT JOIN planning_center.groups_attendance_relationships aer ON e.event_id = aer.attendance_id AND aer.relationship_type = 'Event'
    LEFT JOIN planning_center.groups_attendances a ON aer.relationship_id = a.attendance_id
    LEFT JOIN planning_center.groups_attendance_relationships apr ON a.attendance_id = apr.attendance_id AND apr.relationship_type = 'Person'
        AND apr.relationship_id = p.person_id
    WHERE e.starts_at >= CURRENT_DATE - INTERVAL '90 days'
      AND e.starts_at < CURRENT_DATE
      AND e.canceled = false
      AND g.archived_at IS NULL
    GROUP BY p.person_id, m.group_id, g.name
)
SELECT 
    person_id,
    group_name,
    events_available,
    events_attended,
    ROUND(events_attended::numeric / NULLIF(events_available, 0) * 100, 1) as attendance_rate,
    ROUND(events_attended::numeric / weeks_span, 2) as events_per_week,
    CASE 
        WHEN events_attended::numeric / NULLIF(events_available, 0) >= 0.75 THEN 'Consistent'
        WHEN events_attended::numeric / NULLIF(events_available, 0) >= 0.50 THEN 'Regular'
        WHEN events_attended::numeric / NULLIF(events_available, 0) >= 0.25 THEN 'Occasional'
        ELSE 'Rare'
    END as attendance_category,
    weeks_span as weeks_active
FROM member_attendance
WHERE events_available > 0
ORDER BY attendance_rate DESC, events_attended DESC;
```

## Member Engagement Scoring

### Multi-Dimensional Engagement Score

```sql theme={null}
-- Calculate comprehensive engagement score for each member
WITH member_activity AS (
    SELECT 
        p.person_id,
        -- Group participation
        COUNT(DISTINCT m.group_id) as groups_count,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.group_id END) as groups_led,
        MIN(m.joined_at) as earliest_join,
        -- Event attendance (last 90 days)
        COUNT(DISTINCT e.event_id) FILTER (WHERE apr.relationship_id = p.person_id) as events_registered,
        COUNT(DISTINCT e.event_id) FILTER (WHERE apr.relationship_id = p.person_id AND a.attended = true) as events_attended,
        -- Recent activity
        MAX(CASE WHEN apr.relationship_id = p.person_id THEN e.starts_at END) as last_attended_event,
        COUNT(DISTINCT e.event_id) FILTER (
            WHERE apr.relationship_id = p.person_id 
            AND a.attended = true 
            AND e.starts_at >= CURRENT_DATE - INTERVAL '30 days'
        ) as recent_attendances
    FROM planning_center.groups_people p
    LEFT JOIN planning_center.groups_memberships m ON p.person_id = m.person_id
    LEFT JOIN planning_center.groups_groups g ON m.group_id = g.group_id AND g.archived_at IS NULL
    LEFT JOIN planning_center.groups_event_relationships er ON g.group_id = er.group_id AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e ON er.relationship_id = e.event_id 
        AND e.starts_at >= CURRENT_DATE - INTERVAL '90 days' 
        AND e.canceled = false
    LEFT JOIN planning_center.groups_attendance_relationships aer ON e.event_id = aer.attendance_id AND aer.relationship_type = 'Event'
    LEFT JOIN planning_center.groups_attendances a ON aer.relationship_id = a.attendance_id
    LEFT JOIN planning_center.groups_attendance_relationships apr ON a.attendance_id = apr.attendance_id AND apr.relationship_type = 'Person'
    GROUP BY p.person_id
),
engagement_scores AS (
    SELECT 
        person_id,
        groups_count,
        groups_led,
        events_attended,
        recent_attendances,
        -- Calculate component scores
        LEAST(groups_count * 10, 30) as group_score,  -- Max 30 points
        groups_led * 15 as leadership_score,  -- 15 points per group led
        LEAST(events_attended * 2, 30) as attendance_score,  -- Max 30 points
        LEAST(recent_attendances * 5, 25) as recency_score,  -- Max 25 points
        -- Tenure bonus
        CASE 
            WHEN earliest_join < CURRENT_DATE - INTERVAL '2 years' THEN 10
            WHEN earliest_join < CURRENT_DATE - INTERVAL '1 year' THEN 5
            ELSE 0
        END as tenure_bonus
    FROM member_activity
)
SELECT 
    person_id,
    groups_count,
    groups_led,
    events_attended,
    recent_attendances,
    group_score + leadership_score + attendance_score + recency_score + tenure_bonus as total_engagement_score,
    CASE 
        WHEN group_score + leadership_score + attendance_score + recency_score + tenure_bonus >= 75 THEN 'Highly Engaged'
        WHEN group_score + leadership_score + attendance_score + recency_score + tenure_bonus >= 50 THEN 'Engaged'
        WHEN group_score + leadership_score + attendance_score + recency_score + tenure_bonus >= 25 THEN 'Moderately Engaged'
        WHEN group_score + leadership_score + attendance_score + recency_score + tenure_bonus > 0 THEN 'Low Engagement'
        ELSE 'Inactive'
    END as engagement_level
FROM engagement_scores
ORDER BY total_engagement_score DESC;
```

## Growth Trends

### Monthly Growth Analysis

```sql theme={null}
-- Track growth trends across multiple dimensions
WITH monthly_metrics AS (
    SELECT 
        DATE_TRUNC('month', series.month) as month,
        -- New groups created
        COUNT(DISTINCT g.group_id) FILTER (
            WHERE DATE_TRUNC('month', g.created_at) = DATE_TRUNC('month', series.month)
        ) as new_groups,
        -- Total active groups
        COUNT(DISTINCT g.group_id) FILTER (
            WHERE g.created_at <= series.month 
            AND (g.archived_at IS NULL OR g.archived_at > series.month)
        ) as active_groups,
        -- New memberships
        COUNT(DISTINCT m.membership_id) FILTER (
            WHERE DATE_TRUNC('month', m.joined_at) = DATE_TRUNC('month', series.month)
        ) as new_memberships,
        -- Total memberships
        COUNT(DISTINCT m.membership_id) FILTER (
            WHERE m.joined_at <= series.month
        ) as total_memberships,
        -- Events held
        COUNT(DISTINCT e.event_id) FILTER (
            WHERE DATE_TRUNC('month', e.starts_at) = DATE_TRUNC('month', series.month)
            AND e.canceled = false
        ) as events_held
    FROM generate_series(
        DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months'),
        DATE_TRUNC('month', CURRENT_DATE),
        INTERVAL '1 month'
    ) as series(month)
    CROSS JOIN planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m ON g.group_id = m.group_id
    LEFT JOIN planning_center.groups_event_relationships er ON g.group_id = er.group_id AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e ON er.relationship_id = e.event_id
    GROUP BY series.month
)
SELECT 
    TO_CHAR(month, 'YYYY-MM') as month,
    new_groups,
    active_groups,
    new_memberships,
    total_memberships,
    events_held,
    -- Calculate growth rates
    LAG(active_groups) OVER (ORDER BY month) as prev_active_groups,
    CASE 
        WHEN LAG(active_groups) OVER (ORDER BY month) > 0 
        THEN ROUND((active_groups - LAG(active_groups) OVER (ORDER BY month))::numeric / 
             LAG(active_groups) OVER (ORDER BY month) * 100, 1)
        ELSE NULL 
    END as group_growth_rate,
    LAG(total_memberships) OVER (ORDER BY month) as prev_memberships,
    CASE 
        WHEN LAG(total_memberships) OVER (ORDER BY month) > 0 
        THEN ROUND((total_memberships - LAG(total_memberships) OVER (ORDER BY month))::numeric / 
             LAG(total_memberships) OVER (ORDER BY month) * 100, 1)
        ELSE NULL 
    END as membership_growth_rate
FROM monthly_metrics
ORDER BY month DESC;
```

### Group Lifecycle Analysis

```sql theme={null}
-- Analyze how groups evolve over time
WITH group_lifecycle AS (
    SELECT 
        g.group_id,
        g.name,
        g.created_at,
        g.archived_at,
        g.memberships_count as current_size,
        -- Calculate age in months
        EXTRACT(YEAR FROM AGE(COALESCE(g.archived_at, CURRENT_DATE), g.created_at)) * 12 +
        EXTRACT(MONTH FROM AGE(COALESCE(g.archived_at, CURRENT_DATE), g.created_at)) as age_months,
        -- Get membership history
        COUNT(DISTINCT m.person_id) as total_members_ever,
        COUNT(DISTINCT CASE WHEN m.joined_at >= CURRENT_DATE - INTERVAL '90 days' THEN m.person_id END) as recent_joins,
        MIN(m.joined_at) as first_member_joined,
        MAX(m.joined_at) as last_member_joined,
        -- Event activity
        COUNT(DISTINCT e.event_id) as total_events,
        COUNT(DISTINCT CASE WHEN e.starts_at >= CURRENT_DATE - INTERVAL '90 days' THEN e.event_id END) as recent_events
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m ON g.group_id = m.group_id
    LEFT JOIN planning_center.groups_event_relationships er ON g.group_id = er.group_id AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e ON er.relationship_id = e.event_id
    GROUP BY g.group_id, g.name, g.created_at, g.archived_at, g.memberships_count
)
SELECT 
    group_id,
    name,
    age_months,
    current_size,
    total_members_ever,
    recent_joins,
    recent_events,
    CASE 
        WHEN archived_at IS NOT NULL THEN 'Archived'
        WHEN age_months < 3 THEN 'New'
        WHEN age_months < 12 THEN 'Growing'
        WHEN recent_events = 0 THEN 'Dormant'
        WHEN recent_joins > 0 THEN 'Active'
        ELSE 'Stable'
    END as lifecycle_stage,
    CASE 
        WHEN total_members_ever > 0 
        THEN ROUND(current_size::numeric / total_members_ever * 100, 1)
        ELSE 0 
    END as retention_rate,
    ROUND(total_events::numeric / NULLIF(age_months, 0), 1) as events_per_month
FROM group_lifecycle
ORDER BY 
    CASE 
        WHEN archived_at IS NOT NULL THEN 4
        WHEN age_months < 3 THEN 1
        WHEN recent_joins > 0 THEN 2
        ELSE 3
    END,
    current_size DESC;
```

## Leadership Analysis

### Leadership Coverage and Capacity

```sql theme={null}
-- Analyze leadership distribution and capacity
WITH leadership_metrics AS (
    SELECT 
        p.person_id,
        COUNT(DISTINCT m.group_id) FILTER (WHERE m.role = 'leader') as groups_leading,
        COUNT(DISTINCT m.group_id) FILTER (WHERE m.role = 'member') as groups_participating,
        ARRAY_AGG(DISTINCT g.name ORDER BY g.name) FILTER (WHERE m.role = 'leader') as groups_led_names,
        SUM(g.memberships_count) FILTER (WHERE m.role = 'leader') as total_members_under_leadership,
        MAX(m.joined_at) FILTER (WHERE m.role = 'leader') as became_leader_date
    FROM planning_center.groups_people p
    JOIN planning_center.groups_memberships m ON p.person_id = m.person_id
    JOIN planning_center.groups_groups g ON m.group_id = g.group_id
    WHERE g.archived_at IS NULL
    GROUP BY p.person_id
),
group_leadership AS (
    SELECT 
        g.group_id,
        g.name,
        g.memberships_count,
        COUNT(DISTINCT m.person_id) FILTER (WHERE m.role = 'leader') as leader_count,
        COUNT(DISTINCT m.person_id) FILTER (WHERE m.role = 'member') as member_count,
        ARRAY_AGG(DISTINCT m.person_id ORDER BY m.joined_at) FILTER (WHERE m.role = 'leader') as leader_ids
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m ON g.group_id = m.group_id
    WHERE g.archived_at IS NULL
    GROUP BY g.group_id, g.name, g.memberships_count
)
SELECT 
    'Leadership Overview' as metric_category,
    'Total Leaders' as metric,
    COUNT(DISTINCT person_id) FILTER (WHERE groups_leading > 0)::text as value
FROM leadership_metrics
UNION ALL
SELECT 
    'Leadership Overview',
    'Avg Groups per Leader',
    ROUND(AVG(groups_leading) FILTER (WHERE groups_leading > 0), 2)::text
FROM leadership_metrics
UNION ALL
SELECT 
    'Leadership Overview',
    'Leaders Leading Multiple Groups',
    COUNT(DISTINCT person_id) FILTER (WHERE groups_leading > 1)::text
FROM leadership_metrics
UNION ALL
SELECT 
    'Group Coverage',
    'Groups Without Leaders',
    COUNT(*)::text
FROM group_leadership
WHERE leader_count = 0
UNION ALL
SELECT 
    'Group Coverage',
    'Groups with Single Leader',
    COUNT(*)::text
FROM group_leadership
WHERE leader_count = 1
UNION ALL
SELECT 
    'Group Coverage',
    'Groups with Multiple Leaders',
    COUNT(*)::text
FROM group_leadership
WHERE leader_count > 1
UNION ALL
SELECT 
    'Leadership Capacity',
    'Avg Members per Leader',
    ROUND(SUM(memberships_count)::numeric / NULLIF(SUM(leader_count), 0), 1)::text
FROM group_leadership
WHERE leader_count > 0;
```

### Potential Leader Identification

```sql theme={null}
-- Identify members who might be ready for leadership
WITH member_qualifications AS (
    SELECT 
        p.person_id,
        -- Current involvement
        COUNT(DISTINCT m.group_id) as groups_count,
        BOOL_OR(m.role = 'leader') as is_current_leader,
        MIN(m.joined_at) as first_joined,
        -- Attendance record (last 90 days)
        COUNT(DISTINCT e.event_id) FILTER (WHERE a.attended = true) as events_attended,
        COUNT(DISTINCT e.event_id) as events_available,
        -- Consistency metrics
        COUNT(DISTINCT DATE_TRUNC('week', e.starts_at)) FILTER (WHERE a.attended = true) as weeks_attended,
        -- Tenure
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, MIN(m.joined_at))) * 12 +
        EXTRACT(MONTH FROM AGE(CURRENT_DATE, MIN(m.joined_at))) as tenure_months
    FROM planning_center.groups_people p
    JOIN planning_center.groups_memberships m ON p.person_id = m.person_id
    JOIN planning_center.groups_groups g ON m.group_id = g.group_id AND g.archived_at IS NULL
    LEFT JOIN planning_center.groups_event_relationships er ON g.group_id = er.group_id AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e ON er.relationship_id = e.event_id 
        AND e.starts_at >= CURRENT_DATE - INTERVAL '90 days'
        AND e.canceled = false
    LEFT JOIN planning_center.groups_attendance_relationships aer ON e.event_id = aer.attendance_id AND aer.relationship_type = 'Event'
    LEFT JOIN planning_center.groups_attendances a ON aer.relationship_id = a.attendance_id
    LEFT JOIN planning_center.groups_attendance_relationships apr ON a.attendance_id = apr.attendance_id 
        AND apr.relationship_type = 'Person' 
        AND apr.relationship_id = p.person_id
    GROUP BY p.person_id
)
SELECT 
    person_id,
    groups_count,
    events_attended,
    ROUND(events_attended::numeric / NULLIF(events_available, 0) * 100, 1) as attendance_rate,
    tenure_months,
    weeks_attended,
    -- Calculate leadership readiness score
    (
        CASE WHEN tenure_months >= 12 THEN 20 ELSE tenure_months * 20 / 12 END +  -- Tenure score
        CASE WHEN events_attended::numeric / NULLIF(events_available, 0) >= 0.75 THEN 30 
             WHEN events_attended::numeric / NULLIF(events_available, 0) >= 0.50 THEN 20
             ELSE 10 END +  -- Attendance score
        CASE WHEN weeks_attended >= 10 THEN 25 ELSE weeks_attended * 2.5 END +  -- Consistency score
        CASE WHEN groups_count > 1 THEN 15 ELSE groups_count * 15 END  -- Involvement score
    ) as readiness_score,
    CASE 
        WHEN tenure_months >= 12 
            AND events_attended::numeric / NULLIF(events_available, 0) >= 0.75
            AND weeks_attended >= 10
        THEN 'Ready Now'
        WHEN tenure_months >= 6
            AND events_attended::numeric / NULLIF(events_available, 0) >= 0.50
        THEN 'Developing'
        ELSE 'Future Potential'
    END as leadership_potential
FROM member_qualifications
WHERE is_current_leader = false
  AND events_available > 0
  AND tenure_months >= 3
ORDER BY readiness_score DESC
LIMIT 20;
```

## Predictive Indicators

### Group Sustainability Prediction

```sql theme={null}
-- Predict which groups might struggle based on patterns
WITH group_indicators AS (
    SELECT 
        g.group_id,
        g.name,
        g.created_at,
        g.memberships_count,
        -- Size trajectory
        COUNT(DISTINCT m.person_id) as current_members,
        COUNT(DISTINCT CASE WHEN m.joined_at >= CURRENT_DATE - INTERVAL '90 days' THEN m.person_id END) as new_members_90d,
        COUNT(DISTINCT CASE WHEN m.joined_at >= CURRENT_DATE - INTERVAL '180 days' 
                        AND m.joined_at < CURRENT_DATE - INTERVAL '90 days' THEN m.person_id END) as members_90_180d,
        -- Leadership stability
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.person_id END) as leader_count,
        MAX(CASE WHEN m.role = 'leader' THEN m.joined_at END) as last_leader_joined,
        -- Event consistency
        COUNT(DISTINCT e.event_id) FILTER (WHERE e.starts_at >= CURRENT_DATE - INTERVAL '30 days') as events_30d,
        COUNT(DISTINCT e.event_id) FILTER (WHERE e.starts_at >= CURRENT_DATE - INTERVAL '90 days') as events_90d,
        STDDEV(EXTRACT(EPOCH FROM (e.starts_at - LAG(e.starts_at) OVER (PARTITION BY g.group_id ORDER BY e.starts_at)))) as event_interval_variance,
        -- Attendance trend
        AVG(CASE WHEN a.attended = true THEN 1 ELSE 0 END) FILTER (WHERE e.starts_at >= CURRENT_DATE - INTERVAL '30 days') as recent_attendance,
        AVG(CASE WHEN a.attended = true THEN 1 ELSE 0 END) FILTER (WHERE e.starts_at >= CURRENT_DATE - INTERVAL '90 days' 
                                                                  AND e.starts_at < CURRENT_DATE - INTERVAL '30 days') as prior_attendance
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m ON g.group_id = m.group_id
    LEFT JOIN planning_center.groups_event_relationships er ON g.group_id = er.group_id AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e ON er.relationship_id = e.event_id AND e.canceled = false
    LEFT JOIN planning_center.groups_attendance_relationships aer ON e.event_id = aer.attendance_id AND aer.relationship_type = 'Event'
    LEFT JOIN planning_center.groups_attendances a ON aer.relationship_id = a.attendance_id
    WHERE g.archived_at IS NULL
      AND g.created_at < CURRENT_DATE - INTERVAL '90 days'  -- Established groups only
    GROUP BY g.group_id, g.name, g.created_at, g.memberships_count
),
predictions AS (
    SELECT 
        *,
        -- Calculate risk scores
        CASE WHEN current_members < members_90_180d THEN 2 ELSE 0 END as declining_membership,
        CASE WHEN leader_count = 0 THEN 3 WHEN leader_count = 1 THEN 1 ELSE 0 END as leadership_risk,
        CASE WHEN events_30d = 0 THEN 3 WHEN events_30d < 2 THEN 1 ELSE 0 END as activity_risk,
        CASE WHEN recent_attendance < prior_attendance THEN 2 ELSE 0 END as attendance_decline,
        CASE WHEN new_members_90d = 0 THEN 1 ELSE 0 END as no_new_members
    FROM group_indicators
)
SELECT 
    group_id,
    name,
    current_members,
    leader_count,
    events_30d,
    ROUND(recent_attendance * 100, 1) as recent_attendance_rate,
    declining_membership + leadership_risk + activity_risk + attendance_decline + no_new_members as total_risk_score,
    CASE 
        WHEN declining_membership + leadership_risk + activity_risk + attendance_decline + no_new_members >= 5 THEN 'High Risk - Immediate Attention'
        WHEN declining_membership + leadership_risk + activity_risk + attendance_decline + no_new_members >= 3 THEN 'Medium Risk - Monitor Closely'
        WHEN declining_membership + leadership_risk + activity_risk + attendance_decline + no_new_members >= 1 THEN 'Low Risk - Watch'
        ELSE 'Healthy'
    END as sustainability_prediction,
    ARRAY_REMOVE(ARRAY[
        CASE WHEN declining_membership > 0 THEN 'Declining Membership' END,
        CASE WHEN leadership_risk > 0 THEN 'Leadership Issues' END,
        CASE WHEN activity_risk > 0 THEN 'Low Activity' END,
        CASE WHEN attendance_decline > 0 THEN 'Attendance Declining' END,
        CASE WHEN no_new_members > 0 THEN 'No New Members' END
    ], NULL) as risk_factors
FROM predictions
WHERE declining_membership + leadership_risk + activity_risk + attendance_decline + no_new_members > 0
ORDER BY total_risk_score DESC, current_members DESC;
```

## Performance Optimization

### Materialized View for Dashboard

```sql theme={null}
-- Create a materialized view for frequently accessed metrics
CREATE MATERIALIZED VIEW IF NOT EXISTS groups_dashboard_metrics AS
WITH base_metrics AS (
    SELECT 
        g.group_id,
        g.name,
        g.created_at,
        g.archived_at,
        g.memberships_count,
        g.location_type_preference,
        COUNT(DISTINCT m.person_id) as actual_members,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.person_id END) as leader_count,
        COUNT(DISTINCT e.event_id) FILTER (WHERE e.starts_at >= CURRENT_DATE - INTERVAL '30 days') as recent_events,
        MAX(e.starts_at) as last_event,
        AVG(CASE WHEN a.attended = true THEN 1 ELSE 0 END) as avg_attendance_rate
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m ON g.group_id = m.group_id
    LEFT JOIN planning_center.groups_event_relationships er ON g.group_id = er.group_id AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e ON er.relationship_id = e.event_id AND e.canceled = false
    LEFT JOIN planning_center.groups_attendance_relationships aer ON e.event_id = aer.attendance_id AND aer.relationship_type = 'Event'
    LEFT JOIN planning_center.groups_attendances a ON aer.relationship_id = a.attendance_id
    GROUP BY g.group_id, g.name, g.created_at, g.archived_at, g.memberships_count, g.location_type_preference
)
SELECT 
    group_id,
    name,
    created_at,
    archived_at,
    memberships_count,
    location_type_preference,
    actual_members,
    leader_count,
    recent_events,
    last_event,
    ROUND(avg_attendance_rate * 100, 1) as attendance_rate_percent,
    CASE 
        WHEN archived_at IS NOT NULL THEN 'Archived'
        WHEN last_event IS NULL OR last_event < CURRENT_DATE - INTERVAL '30 days' THEN 'Inactive'
        WHEN leader_count = 0 THEN 'No Leader'
        WHEN actual_members < 4 THEN 'Small'
        ELSE 'Active'
    END as status,
    CURRENT_TIMESTAMP as last_refreshed
FROM base_metrics;

-- Create indexes on the materialized view
CREATE INDEX idx_groups_dashboard_status ON groups_dashboard_metrics(status);
CREATE INDEX idx_groups_dashboard_members ON groups_dashboard_metrics(actual_members);

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

### Query Performance Analysis

```sql theme={null}
-- Analyze query patterns for optimization opportunities
WITH query_stats AS (
    SELECT 
        'Groups Table' as table_name,
        COUNT(*) as row_count,
        pg_size_pretty(pg_relation_size('planning_center.groups_groups')) as table_size
    FROM planning_center.groups_groups
    UNION ALL
    SELECT 
        'Memberships Table',
        COUNT(*),
        pg_size_pretty(pg_relation_size('planning_center.groups_memberships'))
    FROM planning_center.groups_memberships
    UNION ALL
    SELECT 
        'Events Table',
        COUNT(*),
        pg_size_pretty(pg_relation_size('planning_center.groups_events'))
    FROM planning_center.groups_events
    UNION ALL
    SELECT 
        'Attendances Table',
        COUNT(*),
        pg_size_pretty(pg_relation_size('planning_center.groups_attendances'))
    FROM planning_center.groups_attendances
)
SELECT * FROM query_stats
ORDER BY row_count DESC;
```

## Best Practices

1. **Use CTEs for Complex Logic**: Break complex queries into logical steps using Common Table Expressions
2. **Filter Early**: Apply WHERE clauses as early as possible in your joins
3. **Use Window Functions**: Leverage OVER() clauses for running totals and rankings
4. **Index Key Columns**: Ensure frequently joined and filtered columns are indexed
5. **Monitor Performance**: Use EXPLAIN ANALYZE to understand query execution plans

## Next Steps

Ready to apply these queries to real ministry scenarios? Check out:

* [Reporting Examples](/planning-center/groups/reporting-examples) - Practical applications for ministry decision-making