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

<Card title="SQL Proficiency Recommended" icon="code" color="#0066CC" horizontal>
  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.
</Card>

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

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

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

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

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

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

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

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

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

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

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

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

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

<Note>
  **Window Functions**: Use OVER() clauses for running totals, rankings, and comparisons within groups.
</Note>

<Warning>
  **Performance Considerations**: These queries process significant data. Consider adding indexes on frequently filtered columns like sort\_date and person\_id.
</Warning>

## Next Steps

Ready to build comprehensive reports? Check out our [Services Reporting Examples](/planning-center/services/reporting-examples) for:

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