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.

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

Group Health Metrics
Attendance Analytics
Member Engagement Scoring
Growth Trends
Leadership Analysis
Predictive Indicators
Performance Optimization

Group Health Metrics

Comprehensive Group Health Score

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

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

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

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

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

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

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

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

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

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

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

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

Use CTEs for Complex Logic: Break complex queries into logical steps using Common Table Expressions
Filter Early: Apply WHERE clauses as early as possible in your joins
Use Window Functions: Leverage OVER() clauses for running totals and rankings
Index Key Columns: Ensure frequently joined and filtered columns are indexed
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 - Practical applications for ministry decision-making

Advanced queries

Advanced Groups Queries

Query Customization Required

Query Requirements

Schema Prefix

Row Level Security (RLS)

Table of Contents

Group Health Metrics

Comprehensive Group Health Score

Groups at Risk

Attendance Analytics

Attendance Patterns by Day and Time

Member Attendance Consistency

Member Engagement Scoring

Multi-Dimensional Engagement Score

Growth Trends

Monthly Growth Analysis

Group Lifecycle Analysis

Leadership Analysis

Leadership Coverage and Capacity

Potential Leader Identification

Predictive Indicators

Group Sustainability Prediction

Performance Optimization

Materialized View for Dashboard

Query Performance Analysis

Best Practices

Next Steps

​Advanced Groups Queries

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Table of Contents

​Group Health Metrics

​Comprehensive Group Health Score

​Groups at Risk

​Attendance Analytics

​Attendance Patterns by Day and Time

​Member Attendance Consistency

​Member Engagement Scoring

​Multi-Dimensional Engagement Score

​Growth Trends

​Monthly Growth Analysis

​Group Lifecycle Analysis

​Leadership Analysis

​Leadership Coverage and Capacity

​Potential Leader Identification

​Predictive Indicators

​Group Sustainability Prediction

​Performance Optimization

​Materialized View for Dashboard

​Query Performance Analysis

​Best Practices

​Next Steps

Advanced Groups Queries

Query Requirements

Schema Prefix

Row Level Security (RLS)

Table of Contents

Group Health Metrics

Comprehensive Group Health Score

Groups at Risk

Attendance Analytics

Attendance Patterns by Day and Time

Member Attendance Consistency

Member Engagement Scoring

Multi-Dimensional Engagement Score

Growth Trends

Monthly Growth Analysis

Group Lifecycle Analysis

Leadership Analysis

Leadership Coverage and Capacity

Potential Leader Identification

Predictive Indicators

Group Sustainability Prediction

Performance Optimization

Materialized View for Dashboard

Query Performance Analysis

Best Practices

Next Steps