Advanced Services Queries

This guide provides sophisticated SQL queries for deeper analysis of your Planning Center Services data. These queries help identify patterns, optimize scheduling, and improve ministry effectiveness.

SQL Proficiency Recommended

These queries use advanced SQL features like CTEs, window functions, and complex joins. Familiarity with SQL will help you customize them for your specific needs.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Services module live in the planning_center schema. Always prefix table names with planning_center. in advanced queries. ✅ CORRECT: SELECT * FROM planning_center.services_plan_people ❌ INCORRECT: SELECT * FROM services_plan_people

Row Level Security (RLS)

Row Level Security automatically manages:

tenant_organization_id – isolates results to your organization
system_status – active records returned by default

Avoid adding 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'

Center your filters on scheduling, volunteer, and worship-specific logic while relying on RLS for tenancy and status.

Volunteer Analytics

Volunteer Burnout Detection

-- Identify volunteers who may be overserving
WITH volunteer_stats AS (
    SELECT
        p.person_id,
        p.full_name,
        COUNT(DISTINCT pl.plan_id) as services_scheduled,
        COUNT(DISTINCT DATE_TRUNC('week', pl.sort_date)) as weeks_served,
        COUNT(DISTINCT t.team_id) as teams_serving_on,
        COUNT(CASE WHEN pp.status = 'D' THEN 1 END) as times_declined,
        MAX(pl.sort_date) as last_scheduled
    FROM planning_center.services_people p
    JOIN planning_center.services_plan_people pp ON p.person_id = pp.person_id
    JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
    JOIN planning_center.services_teams t ON pp.team_id = t.team_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '2 months'
        AND pl.sort_date <= CURRENT_DATE + INTERVAL '1 month'
    GROUP BY p.person_id, p.full_name
),
burnout_indicators AS (
    SELECT
        person_id,
        full_name,
        services_scheduled,
        weeks_served,
        teams_serving_on,
        times_declined,
        last_scheduled,
        ROUND(services_scheduled::numeric / NULLIF(weeks_served, 0), 2) as avg_per_week,
        ROUND(times_declined::numeric * 100 / NULLIF(services_scheduled, 0), 1) as decline_rate
    FROM volunteer_stats
)
SELECT
    full_name,
    services_scheduled,
    weeks_served,
    teams_serving_on,
    avg_per_week,
    decline_rate as decline_percentage,
    CASE
        WHEN avg_per_week > 3 THEN 'High Risk'
        WHEN avg_per_week > 2 OR decline_rate > 30 THEN 'Medium Risk'
        WHEN avg_per_week > 1.5 OR decline_rate > 20 THEN 'Low Risk'
        ELSE 'Healthy'
    END as burnout_risk,
    last_scheduled
FROM burnout_indicators
WHERE services_scheduled >= 4  -- Only show active volunteers
ORDER BY avg_per_week DESC, decline_rate DESC;

Team Health Score

-- Comprehensive team health analysis
WITH team_metrics AS (
    SELECT
        t.team_id,
        t.name as team_name,
        COUNT(DISTINCT pp.person_id) as active_members,
        COUNT(DISTINCT pp.plan_id) as total_schedules,
        AVG(CASE WHEN pp.status = 'C' THEN 1.0 ELSE 0 END) * 100 as confirm_rate,
        COUNT(DISTINCT np.plan_id) as plans_with_needs
    FROM planning_center.services_teams t
    LEFT JOIN planning_center.services_plan_people pp ON t.team_id = pp.team_id
    LEFT JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
    LEFT JOIN planning_center.services_needed_positions np ON t.team_id = np.team_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
        AND t.archived_at IS NULL
    GROUP BY t.team_id, t.name
),
position_coverage AS (
    SELECT
        t.team_id,
        COUNT(DISTINCT tp.team_position_id) as total_positions,
        COUNT(DISTINCT pa.person_id) as qualified_people
    FROM planning_center.services_teams t
    JOIN planning_center.services_team_positions tp ON t.team_id = tp.team_id
    LEFT JOIN planning_center.services_person_team_position_assignments pa
        ON tp.team_position_id = pa.team_position_id
    GROUP BY t.team_id
)
SELECT
    tm.team_name,
    tm.active_members,
    pc.total_positions,
    pc.qualified_people,
    ROUND(pc.qualified_people::numeric / NULLIF(pc.total_positions, 0), 2) as people_per_position,
    ROUND(tm.confirm_rate, 1) as confirm_percentage,
    tm.plans_with_needs as unfilled_schedules,
    CASE
        WHEN tm.confirm_rate >= 90 AND pc.qualified_people >= pc.total_positions * 2 THEN 'Excellent'
        WHEN tm.confirm_rate >= 80 AND pc.qualified_people >= pc.total_positions * 1.5 THEN 'Good'
        WHEN tm.confirm_rate >= 70 AND pc.qualified_people >= pc.total_positions THEN 'Fair'
        ELSE 'Needs Attention'
    END as team_health
FROM team_metrics tm
JOIN position_coverage pc ON tm.team_id = pc.team_id
WHERE tm.active_members > 0
ORDER BY tm.confirm_rate DESC;

Scheduling Patterns Analysis

-- Analyze when people prefer to serve
WITH scheduling_patterns AS (
    SELECT
        p.person_id,
        p.full_name,
        pt.name as time_name,
        EXTRACT(HOUR FROM pt.starts_at) as service_hour,
        COUNT(*) as times_scheduled,
        COUNT(CASE WHEN pp.status = 'C' THEN 1 END) as times_confirmed,
        COUNT(CASE WHEN pp.status = 'D' THEN 1 END) as times_declined
    FROM planning_center.services_people p
    JOIN planning_center.services_plan_people pp ON p.person_id = pp.person_id
    JOIN planning_center.services_plan_person_times ppt ON pp.plan_person_id = ppt.plan_person_id
    JOIN planning_center.services_plan_times pt ON ppt.plan_time_id = pt.plan_time_id
    WHERE pt.starts_at IS NOT NULL
    GROUP BY p.person_id, p.full_name, pt.name, service_hour
)
SELECT
    full_name,
    time_name,
    service_hour,
    times_scheduled,
    times_confirmed,
    times_declined,
    ROUND(times_confirmed::numeric * 100 / NULLIF(times_scheduled, 0), 1) as acceptance_rate,
    CASE
        WHEN times_confirmed::numeric / NULLIF(times_scheduled, 0) >= 0.9 THEN 'Preferred'
        WHEN times_declined::numeric / NULLIF(times_scheduled, 0) >= 0.5 THEN 'Not Preferred'
        ELSE 'Neutral'
    END as time_preference
FROM scheduling_patterns
WHERE times_scheduled >= 3  -- Minimum data for pattern
ORDER BY full_name, acceptance_rate DESC;

Song & Worship Analytics

Song Rotation Optimization

-- Analyze song usage patterns and suggest rotation
WITH song_usage AS (
    SELECT
        s.song_id,
        s.title,
        s.author,
        COUNT(DISTINCT i.plan_id) as total_uses,
        COUNT(DISTINCT DATE_TRUNC('month', pl.sort_date)) as months_used,
        MIN(pl.sort_date) as first_used,
        MAX(pl.sort_date) as last_used,
        AVG(pl.sort_date - LAG(pl.sort_date) OVER (PARTITION BY s.song_id ORDER BY pl.sort_date)) as avg_days_between
    FROM planning_center.services_songs s
    JOIN planning_center.services_items i ON s.song_id = i.song_id
    JOIN planning_center.services_plans pl ON i.plan_id = pl.plan_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '1 year'
        AND i.item_type = 'song'
    GROUP BY s.song_id, s.title, s.author
),
song_categories AS (
    SELECT
        song_id,
        title,
        author,
        total_uses,
        months_used,
        last_used,
        CURRENT_DATE - last_used as days_since_last,
        EXTRACT(DAYS FROM avg_days_between) as typical_gap_days,
        CASE
            WHEN total_uses >= 20 AND CURRENT_DATE - last_used > 60 THEN 'Overdue - High Rotation'
            WHEN total_uses >= 10 AND CURRENT_DATE - last_used > 90 THEN 'Overdue - Medium Rotation'
            WHEN total_uses >= 5 AND CURRENT_DATE - last_used > 120 THEN 'Overdue - Low Rotation'
            WHEN CURRENT_DATE - last_used < 14 THEN 'Recently Used'
            WHEN total_uses < 3 THEN 'New/Rarely Used'
            ELSE 'Normal Rotation'
        END as rotation_status
    FROM song_usage
)
SELECT
    title,
    author,
    total_uses,
    days_since_last as days_since_last_use,
    typical_gap_days as typical_days_between,
    rotation_status,
    CASE
        WHEN rotation_status LIKE 'Overdue%' THEN 'Consider scheduling soon'
        WHEN rotation_status = 'Recently Used' THEN 'Wait ' || GREATEST(0, typical_gap_days - days_since_last) || ' more days'
        ELSE 'Normal scheduling'
    END as recommendation
FROM song_categories
ORDER BY
    CASE
        WHEN rotation_status LIKE 'Overdue%' THEN 1
        WHEN rotation_status = 'New/Rarely Used' THEN 2
        ELSE 3
    END,
    total_uses DESC;

Key Progression Analysis

-- Analyze key changes within services for smooth transitions
WITH service_keys AS (
    SELECT
        pl.plan_id,
        pl.title as plan_title,
        pl.sort_date,
        i.sequence,
        i.title as item_title,
        s.title as song_title,
        a.chord_chart_key as song_key,
        LAG(a.chord_chart_key) OVER (PARTITION BY pl.plan_id ORDER BY i.sequence) as previous_key,
        LEAD(a.chord_chart_key) OVER (PARTITION BY pl.plan_id ORDER BY i.sequence) as next_key
    FROM planning_center.services_items i
    JOIN planning_center.services_plans pl ON i.plan_id = pl.plan_id
    JOIN planning_center.services_songs s ON i.song_id = s.song_id
    JOIN planning_center.services_arrangements a ON i.arrangement_id = a.arrangement_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
        AND i.item_type = 'song'
        AND a.chord_chart_key IS NOT NULL
)
SELECT
    plan_title,
    sort_date,
    STRING_AGG(
        CASE
            WHEN song_key != previous_key AND previous_key IS NOT NULL
            THEN previous_key || '→' || song_key
            ELSE song_key
        END,
        ' | ' ORDER BY sequence
    ) as key_progression,
    COUNT(CASE WHEN song_key != previous_key AND previous_key IS NOT NULL THEN 1 END) as key_changes,
    COUNT(DISTINCT song_key) as unique_keys
FROM service_keys
GROUP BY plan_id, plan_title, sort_date
ORDER BY sort_date DESC
LIMIT 20;

Song Theme Correlation

-- Analyze which themes are used together
WITH song_themes AS (
    SELECT
        pl.plan_id,
        pl.title as plan_title,
        s.song_id,
        s.title as song_title,
        UNNEST(STRING_TO_ARRAY(LOWER(s.themes), ',')) as theme
    FROM planning_center.services_items i
    JOIN planning_center.services_plans pl ON i.plan_id = pl.plan_id
    JOIN planning_center.services_songs s ON i.song_id = s.song_id
    WHERE s.themes IS NOT NULL AND s.themes != ''
        AND pl.sort_date >= CURRENT_DATE - INTERVAL '6 months'
        AND i.item_type = 'song'
),
theme_pairs AS (
    SELECT
        t1.theme as theme1,
        t2.theme as theme2,
        COUNT(DISTINCT t1.plan_id) as plans_together
    FROM song_themes t1
    JOIN song_themes t2 ON t1.plan_id = t2.plan_id
        AND t1.song_id < t2.song_id
        AND t1.theme < t2.theme
    GROUP BY t1.theme, t2.theme
)
SELECT
    TRIM(theme1) as theme_1,
    TRIM(theme2) as theme_2,
    plans_together as services_paired,
    ROUND(plans_together::numeric * 100 / (
        SELECT COUNT(DISTINCT plan_id)
        FROM song_themes
    ), 1) as percentage_of_services
FROM theme_pairs
WHERE plans_together >= 3
ORDER BY plans_together DESC
LIMIT 25;

Service Planning Intelligence

Optimal Service Length Analysis

-- Analyze service length patterns and attendance correlation
WITH service_lengths AS (
    SELECT
        st.name as service_type,
        pt.name as time_name,
        pl.plan_id,
        pl.sort_date,
        pl.total_length / 60 as length_minutes,
        pl.plan_people_count as volunteers,
        EXTRACT(DOW FROM pl.sort_date) as day_of_week,
        EXTRACT(MONTH FROM pl.sort_date) as month
    FROM planning_center.services_plans pl
    JOIN planning_center.services_service_types st ON pl.service_type_id = st.service_type_id
    LEFT JOIN planning_center.services_plan_times pt ON pl.plan_id = pt.plan_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '1 year'
        AND pl.total_length > 0
        AND pt.time_type = 'service'
),
length_stats AS (
    SELECT
        service_type,
        time_name,
        AVG(length_minutes) as avg_length,
        STDDEV(length_minutes) as stddev_length,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY length_minutes) as q1_length,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY length_minutes) as median_length,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY length_minutes) as q3_length,
        MIN(length_minutes) as min_length,
        MAX(length_minutes) as max_length,
        COUNT(*) as service_count
    FROM service_lengths
    GROUP BY service_type, time_name
)
SELECT
    service_type,
    time_name,
    service_count,
    ROUND(avg_length, 1) as avg_minutes,
    ROUND(median_length, 1) as median_minutes,
    ROUND(stddev_length, 1) as variation,
    ROUND(min_length, 0) || '-' || ROUND(max_length, 0) as range_minutes,
    ROUND(q1_length, 0) || '-' || ROUND(q3_length, 0) as typical_range,
    CASE
        WHEN stddev_length / NULLIF(avg_length, 0) > 0.2 THEN 'High Variation'
        WHEN stddev_length / NULLIF(avg_length, 0) > 0.1 THEN 'Moderate Variation'
        ELSE 'Consistent'
    END as consistency
FROM length_stats
WHERE service_count >= 5
ORDER BY service_type, time_name;

Item Type Distribution

-- Analyze the composition of services
WITH item_analysis AS (
    SELECT
        st.name as service_type,
        pl.plan_id,
        pl.title,
        COUNT(*) as total_items,
        COUNT(CASE WHEN i.item_type = 'song' THEN 1 END) as songs,
        COUNT(CASE WHEN i.item_type = 'header' THEN 1 END) as headers,
        COUNT(CASE WHEN i.item_type = 'media' THEN 1 END) as media,
        COUNT(CASE WHEN i.item_type = 'item' THEN 1 END) as other_items,
        SUM(i.length) / 60 as total_minutes,
        SUM(CASE WHEN i.item_type = 'song' THEN i.length ELSE 0 END) / 60 as song_minutes
    FROM planning_center.services_items i
    JOIN planning_center.services_plans pl ON i.plan_id = pl.plan_id
    JOIN planning_center.services_service_types st ON pl.service_type_id = st.service_type_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY st.name, pl.plan_id, pl.title
)
SELECT
    service_type,
    AVG(total_items) as avg_items,
    AVG(songs) as avg_songs,
    AVG(headers) as avg_headers,
    AVG(media) as avg_media,
    AVG(other_items) as avg_other,
    ROUND(AVG(song_minutes), 1) as avg_music_minutes,
    ROUND(AVG(song_minutes) * 100 / NULLIF(AVG(total_minutes), 0), 1) as music_percentage
FROM item_analysis
GROUP BY service_type
ORDER BY service_type;

Team Scheduling Optimization

Find Best Team Combinations

-- Identify teams that work well together
WITH team_combinations AS (
    SELECT
        pp1.plan_id,
        t1.name as team1,
        t2.name as team2,
        COUNT(*) OVER (PARTITION BY t1.team_id, t2.team_id) as times_together,
        AVG(CASE WHEN pp1.status = 'C' AND pp2.status = 'C' THEN 1.0 ELSE 0 END)
            OVER (PARTITION BY t1.team_id, t2.team_id) as both_confirm_rate
    FROM planning_center.services_plan_people pp1
    JOIN planning_center.services_plan_people pp2 ON pp1.plan_id = pp2.plan_id
        AND pp1.team_id < pp2.team_id
    JOIN planning_center.services_teams t1 ON pp1.team_id = t1.team_id
    JOIN planning_center.services_teams t2 ON pp2.team_id = t2.team_id
    JOIN planning_center.services_plans pl ON pp1.plan_id = pl.plan_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '6 months'
)
SELECT DISTINCT
    team1,
    team2,
    times_together,
    ROUND(both_confirm_rate * 100, 1) as both_confirm_percentage,
    CASE
        WHEN both_confirm_rate >= 0.9 AND times_together >= 10 THEN 'Excellent Pairing'
        WHEN both_confirm_rate >= 0.8 AND times_together >= 5 THEN 'Good Pairing'
        WHEN both_confirm_rate < 0.6 THEN 'Consider Separating'
        ELSE 'Neutral'
    END as recommendation
FROM team_combinations
WHERE times_together >= 5
ORDER BY both_confirm_rate DESC, times_together DESC;

Volunteer Availability Forecast

-- Predict volunteer availability based on historical patterns
WITH volunteer_history AS (
    SELECT
        p.person_id,
        p.full_name,
        DATE_TRUNC('month', pl.sort_date) as month,
        COUNT(*) as times_scheduled,
        COUNT(CASE WHEN pp.status = 'C' THEN 1 END) as times_available,
        COUNT(CASE WHEN pp.status = 'D' THEN 1 END) as times_unavailable
    FROM planning_center.services_people p
    JOIN planning_center.services_plan_people pp ON p.person_id = pp.person_id
    JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
    WHERE pl.sort_date >= CURRENT_DATE - INTERVAL '12 months'
        AND pl.sort_date < CURRENT_DATE
    GROUP BY p.person_id, p.full_name, DATE_TRUNC('month', pl.sort_date)
),
availability_trends AS (
    SELECT
        person_id,
        full_name,
        AVG(times_available::numeric / NULLIF(times_scheduled, 0)) as avg_availability,
        STDDEV(times_available::numeric / NULLIF(times_scheduled, 0)) as availability_variance,
        COUNT(DISTINCT month) as months_active
    FROM volunteer_history
    GROUP BY person_id, full_name
    HAVING COUNT(DISTINCT month) >= 3  -- Minimum history for prediction
)
SELECT
    full_name,
    ROUND(avg_availability * 100, 1) as historical_availability_pct,
    ROUND(availability_variance * 100, 1) as variance_pct,
    months_active,
    CASE
        WHEN avg_availability >= 0.9 AND availability_variance < 0.1 THEN 'Very Reliable'
        WHEN avg_availability >= 0.8 AND availability_variance < 0.2 THEN 'Reliable'
        WHEN avg_availability >= 0.7 THEN 'Moderately Reliable'
        WHEN avg_availability >= 0.5 THEN 'Variable Availability'
        ELSE 'Limited Availability'
    END as reliability_rating,
    ROUND(avg_availability * 4, 0) as predicted_available_per_month
FROM availability_trends
ORDER BY avg_availability DESC, availability_variance;

Performance Monitoring

Service Preparation Timeline

-- Track how far in advance teams confirm
WITH confirmation_timeline AS (
    SELECT
        t.name as team,
        pp.status,
        pl.sort_date as service_date,
        pp.status_updated_at as confirmation_date,
        pl.sort_date - pp.status_updated_at as days_before_service
    FROM planning_center.services_plan_people pp
    JOIN planning_center.services_teams t ON pp.team_id = t.team_id
    JOIN planning_center.services_plans pl ON pp.plan_id = pl.plan_id
    WHERE pp.status = 'C'
        AND pp.status_updated_at IS NOT NULL
        AND pl.sort_date >= CURRENT_DATE - INTERVAL '3 months'
)
SELECT
    team,
    COUNT(*) as total_confirmations,
    ROUND(AVG(EXTRACT(DAYS FROM days_before_service)), 1) as avg_days_advance,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(DAYS FROM days_before_service)) as median_days_advance,
    COUNT(CASE WHEN days_before_service >= INTERVAL '7 days' THEN 1 END) as confirmed_week_plus,
    COUNT(CASE WHEN days_before_service < INTERVAL '2 days' THEN 1 END) as last_minute,
    ROUND(
        COUNT(CASE WHEN days_before_service >= INTERVAL '7 days' THEN 1 END)::numeric * 100 /
        NULLIF(COUNT(*), 0), 1
    ) as pct_early_confirmation
FROM confirmation_timeline
GROUP BY team
ORDER BY avg_days_advance DESC;

Tips for Advanced Queries

CTEs (WITH clauses): Break complex logic into readable steps. Each CTE builds on the previous one.

Window Functions: Use OVER() clauses for running totals, rankings, and comparisons within groups.

Performance Considerations: These queries process significant data. Consider adding indexes on frequently filtered columns like sort_date and person_id.

Next Steps

Ready to build comprehensive reports? Check out our Services Reporting Examples for:

Complete volunteer dashboards
Worship planning analytics
Team health scorecards
Service effectiveness metrics
Multi-campus coordination reports

​Advanced Services Queries

SQL Proficiency Recommended

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Volunteer Analytics

​Volunteer Burnout Detection

​Team Health Score

​Scheduling Patterns Analysis

​Song & Worship Analytics

​Song Rotation Optimization

​Key Progression Analysis

​Song Theme Correlation

​Service Planning Intelligence

​Optimal Service Length Analysis

​Item Type Distribution

​Team Scheduling Optimization

​Find Best Team Combinations

​Volunteer Availability Forecast

​Performance Monitoring

​Service Preparation Timeline

​Tips for Advanced Queries

​Next Steps

Advanced Services Queries

Query Requirements

Schema Prefix

Row Level Security (RLS)

Volunteer Analytics

Volunteer Burnout Detection

Team Health Score

Scheduling Patterns Analysis

Song & Worship Analytics

Song Rotation Optimization

Key Progression Analysis

Song Theme Correlation

Service Planning Intelligence

Optimal Service Length Analysis

Item Type Distribution

Team Scheduling Optimization

Find Best Team Combinations

Volunteer Availability Forecast

Performance Monitoring

Service Preparation Timeline

Tips for Advanced Queries

Next Steps