> ## 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 Check-ins Queries

This guide provides complex SQL queries for deeper analysis of your Planning Center Check-ins data. These queries use multiple table joins, window functions, and advanced SQL features to answer sophisticated ministry questions.

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

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

### Row Level Security (RLS)

Row Level Security automatically enforces:

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

**Do not duplicate these filters**—RLS already applies them and redundant predicates can hide data or slow execution:

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

Direct your filters toward ministry-specific attendance and volunteer logic while trusting RLS for tenancy and status.

## Attendance Analytics

### Weekly Attendance Trends with Growth Metrics

```sql theme={null}
-- Calculate week-over-week attendance growth with moving averages
WITH weekly_attendance AS (
    SELECT
        DATE_TRUNC('week', created_at) as week_start,
        COUNT(DISTINCT check_in_id) as total_attendance,
        COUNT(DISTINCT CASE WHEN kind = 'Regular' THEN check_in_id END) as regular_attendance,
        COUNT(DISTINCT CASE WHEN kind = 'Guest' THEN check_in_id END) as guest_attendance,
        COUNT(DISTINCT CASE WHEN kind = 'Volunteer' THEN check_in_id END) as volunteer_attendance
    FROM planning_center.checkins_checkins
    WHERE created_at >= CURRENT_DATE - INTERVAL '12 weeks'
    GROUP BY DATE_TRUNC('week', created_at)
),
attendance_with_growth AS (
    SELECT
        week_start,
        total_attendance,
        regular_attendance,
        guest_attendance,
        volunteer_attendance,
        LAG(total_attendance, 1) OVER (ORDER BY week_start) as prev_week_attendance,
        AVG(total_attendance) OVER (
            ORDER BY week_start
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ) as four_week_avg
    FROM weekly_attendance
)
SELECT
    TO_CHAR(week_start, 'YYYY-MM-DD') as week,
    total_attendance,
    regular_attendance,
    guest_attendance,
    volunteer_attendance,
    ROUND(four_week_avg, 0) as rolling_4wk_avg,
    CASE
        WHEN prev_week_attendance > 0 THEN
            ROUND(((total_attendance - prev_week_attendance)::NUMERIC / prev_week_attendance) * 100, 1)
        ELSE NULL
    END as week_over_week_change_pct
FROM attendance_with_growth
ORDER BY week_start DESC;
```

### Service Time Optimization Analysis

```sql theme={null}
-- Analyze attendance distribution across service times to identify optimization opportunities
WITH service_attendance AS (
    SELECT
        et.event_time_id,
        et.starts_at,
        e.name as event_name,
        TO_CHAR(et.starts_at, 'Day') as day_of_week,
        TO_CHAR(et.starts_at, 'HH12:MI AM') as service_time,
        COUNT(DISTINCT c.check_in_id) as attendance
    FROM planning_center.checkins_event_times et
    JOIN planning_center.checkins_eventtime_relationships etr
        ON et.event_time_id = etr.eventtime_id
        AND etr.relationship_type = 'Event'
    JOIN planning_center.checkins_events e
        ON etr.relationship_id = e.event_id
    LEFT JOIN planning_center.checkins_checkin_relationships cr
        ON et.event_time_id = cr.relationship_id
        AND cr.relationship_type = 'EventTime'
    LEFT JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
    WHERE et.starts_at >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY et.event_time_id, et.starts_at, e.name
),
service_stats AS (
    SELECT
        event_name,
        day_of_week,
        service_time,
        AVG(attendance) as avg_attendance,
        MIN(attendance) as min_attendance,
        MAX(attendance) as max_attendance,
        STDDEV(attendance) as attendance_stddev,
        COUNT(*) as service_count
    FROM service_attendance
    GROUP BY event_name, day_of_week, service_time
)
SELECT
    event_name,
    day_of_week,
    service_time,
    ROUND(avg_attendance, 0) as avg_attendance,
    min_attendance,
    max_attendance,
    ROUND(attendance_stddev, 1) as variance,
    service_count as times_held,
    CASE
        WHEN attendance_stddev > avg_attendance * 0.3 THEN 'High variance - investigate'
        WHEN avg_attendance < 50 THEN 'Consider combining services'
        WHEN max_attendance > avg_attendance * 1.5 THEN 'Capacity issues possible'
        ELSE 'Stable'
    END as recommendation
FROM service_stats
ORDER BY event_name,
         CASE day_of_week
            WHEN 'Sunday' THEN 1
            WHEN 'Saturday' THEN 2
            WHEN 'Wednesday' THEN 3
            ELSE 4
         END,
         service_time;
```

## Volunteer Analytics

### Volunteer Reliability Score

```sql theme={null}
-- Calculate volunteer reliability based on check-in patterns
WITH volunteer_schedule AS (
    SELECT
        p.person_id,
        p.first_name,
        p.last_name,
        DATE_TRUNC('week', c.created_at) as week,
        COUNT(DISTINCT DATE(c.created_at)) as days_served
    FROM planning_center.checkins_people p
    JOIN planning_center.checkins_checkin_relationships cr
        ON p.person_id = cr.relationship_id
        AND cr.relationship_type = 'Person'
    JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
        AND c.kind = 'Volunteer'
    WHERE c.created_at >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY p.person_id, p.first_name, p.last_name, DATE_TRUNC('week', c.created_at)
),
volunteer_stats AS (
    SELECT
        person_id,
        first_name,
        last_name,
        COUNT(DISTINCT week) as weeks_served,
        SUM(days_served) as total_days_served,
        AVG(days_served) as avg_days_per_week
    FROM volunteer_schedule
    GROUP BY person_id, first_name, last_name
)
SELECT
    first_name,
    last_name,
    weeks_served,
    total_days_served,
    ROUND(avg_days_per_week, 1) as avg_days_per_week,
    ROUND((weeks_served::NUMERIC / 13) * 100, 0) as consistency_pct,
    CASE
        WHEN weeks_served >= 10 AND avg_days_per_week >= 1 THEN 'Highly Reliable'
        WHEN weeks_served >= 6 THEN 'Reliable'
        WHEN weeks_served >= 3 THEN 'Occasional'
        ELSE 'New or Inactive'
    END as reliability_rating
FROM volunteer_stats
ORDER BY weeks_served DESC, total_days_served DESC;
```

### Volunteer-to-Child Ratio Analysis with Alerts

```sql theme={null}
-- Complex ratio analysis with safety thresholds and recommendations
WITH current_checkins AS (
    SELECT
        l.location_id,
        l.name as location_name,
        l.attendees_per_volunteer as required_ratio,
        l.min_volunteers,
        l.max_occupancy,
        COUNT(DISTINCT CASE WHEN c.kind = 'Regular' THEN c.check_in_id END) as child_count,
        COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN c.check_in_id END) as volunteer_count
    FROM planning_center.checkins_locations l
    LEFT JOIN planning_center.checkins_checkin_relationships cr
        ON l.location_id = cr.relationship_id
        AND cr.relationship_type = 'Location'
    LEFT JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
        AND DATE(c.created_at) = CURRENT_DATE
        AND c.checked_out_at IS NULL
    WHERE l.child_or_adult = 'child'
        AND l.kind = 'Folder'
    GROUP BY l.location_id, l.name, l.attendees_per_volunteer, l.min_volunteers, l.max_occupancy
),
ratio_analysis AS (
    SELECT
        location_name,
        child_count,
        volunteer_count,
        required_ratio,
        min_volunteers,
        max_occupancy,
        CASE
            WHEN volunteer_count = 0 THEN NULL
            ELSE ROUND(child_count::NUMERIC / volunteer_count, 1)
        END as actual_ratio,
        CASE
            WHEN required_ratio IS NOT NULL AND volunteer_count > 0 THEN
                CEIL(child_count::NUMERIC / required_ratio)
            ELSE min_volunteers
        END as volunteers_needed
    FROM current_checkins
)
SELECT
    location_name,
    child_count,
    volunteer_count,
    COALESCE(actual_ratio::TEXT, 'No volunteers') as actual_ratio,
    required_ratio,
    volunteers_needed,
    volunteer_count - volunteers_needed as volunteer_surplus_deficit,
    CASE
        WHEN volunteer_count = 0 AND child_count > 0 THEN '🚨 CRITICAL: No volunteers present!'
        WHEN volunteer_count < COALESCE(min_volunteers, 1) THEN '⚠️ Below minimum volunteers'
        WHEN volunteers_needed > volunteer_count THEN '⚠️ Need more volunteers'
        WHEN actual_ratio > required_ratio * 1.5 THEN '⚠️ Ratio exceeds safe limit'
        WHEN child_count > COALESCE(max_occupancy, 999) THEN '⚠️ Over capacity'
        WHEN volunteer_count > volunteers_needed + 2 THEN '✓ Overstaffed (reassign possible)'
        ELSE '✓ Properly staffed'
    END as status
FROM ratio_analysis
WHERE child_count > 0 OR volunteer_count > 0
ORDER BY
    CASE
        WHEN volunteer_count = 0 AND child_count > 0 THEN 1
        WHEN volunteer_count < volunteers_needed THEN 2
        ELSE 3
    END,
    location_name;
```

## Guest Retention Analysis

### First-Time Guest Return Rate

```sql theme={null}
-- Track whether first-time guests return within different time windows
WITH first_visits AS (
    SELECT
        p.person_id,
        p.first_name,
        p.last_name,
        MIN(c.created_at) as first_visit_date
    FROM planning_center.checkins_people p
    JOIN planning_center.checkins_checkin_relationships cr
        ON p.person_id = cr.relationship_id
        AND cr.relationship_type = 'Person'
    JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
    WHERE c.one_time_guest = true
        OR c.kind = 'Guest'
    GROUP BY p.person_id, p.first_name, p.last_name
),
return_visits AS (
    SELECT
        fv.person_id,
        fv.first_name,
        fv.last_name,
        fv.first_visit_date,
        COUNT(DISTINCT DATE(c.created_at)) as total_visits,
        MAX(c.created_at) as last_visit_date,
        COUNT(DISTINCT CASE
            WHEN c.created_at > fv.first_visit_date
            AND c.created_at <= fv.first_visit_date + INTERVAL '7 days'
            THEN DATE(c.created_at)
        END) as visits_within_1_week,
        COUNT(DISTINCT CASE
            WHEN c.created_at > fv.first_visit_date
            AND c.created_at <= fv.first_visit_date + INTERVAL '30 days'
            THEN DATE(c.created_at)
        END) as visits_within_1_month,
        COUNT(DISTINCT CASE
            WHEN c.created_at > fv.first_visit_date
            AND c.created_at <= fv.first_visit_date + INTERVAL '90 days'
            THEN DATE(c.created_at)
        END) as visits_within_3_months
    FROM first_visits fv
    LEFT JOIN planning_center.checkins_checkin_relationships cr
        ON fv.person_id = cr.relationship_id
        AND cr.relationship_type = 'Person'
    LEFT JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
    WHERE fv.first_visit_date >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY fv.person_id, fv.first_name, fv.last_name, fv.first_visit_date
)
SELECT
    DATE_TRUNC('month', first_visit_date) as cohort_month,
    COUNT(*) as total_first_time_guests,
    COUNT(CASE WHEN visits_within_1_week > 0 THEN 1 END) as returned_within_1_week,
    COUNT(CASE WHEN visits_within_1_month > 0 THEN 1 END) as returned_within_1_month,
    COUNT(CASE WHEN visits_within_3_months > 0 THEN 1 END) as returned_within_3_months,
    ROUND(COUNT(CASE WHEN visits_within_1_week > 0 THEN 1 END)::NUMERIC / COUNT(*) * 100, 1) as week_return_rate,
    ROUND(COUNT(CASE WHEN visits_within_1_month > 0 THEN 1 END)::NUMERIC / COUNT(*) * 100, 1) as month_return_rate,
    ROUND(COUNT(CASE WHEN visits_within_3_months > 0 THEN 1 END)::NUMERIC / COUNT(*) * 100, 1) as quarter_return_rate
FROM return_visits
GROUP BY DATE_TRUNC('month', first_visit_date)
ORDER BY cohort_month DESC;
```

## Location Hierarchy Analysis

### Multi-Level Location Utilization

```sql theme={null}
-- Analyze utilization across location hierarchy (building > floor > room)
WITH RECURSIVE location_hierarchy AS (
    -- Base case: top-level locations
    SELECT
        location_id,
        name,
        parent_id,
        kind,
        max_occupancy,
        0 as level,
        name::TEXT as path
    FROM planning_center.checkins_locations
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case: child locations
    SELECT
        l.location_id,
        l.name,
        l.parent_id,
        l.kind,
        l.max_occupancy,
        lh.level + 1,
        lh.path || ' > ' || l.name
    FROM planning_center.checkins_locations l
    JOIN location_hierarchy lh ON l.parent_id = lh.location_id
),
location_attendance AS (
    SELECT
        lh.location_id,
        lh.path,
        lh.level,
        lh.kind,
        lh.max_occupancy,
        COUNT(DISTINCT c.check_in_id) as current_attendance
    FROM location_hierarchy lh
    LEFT JOIN planning_center.checkins_checkin_relationships cr
        ON lh.location_id = cr.relationship_id
        AND cr.relationship_type = 'Location'
    LEFT JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
        AND DATE(c.created_at) = CURRENT_DATE
        AND c.checked_out_at IS NULL
    GROUP BY lh.location_id, lh.path, lh.level, lh.kind, lh.max_occupancy
)
SELECT
    REPEAT('  ', level) || SPLIT_PART(path, ' > ', level + 1) as location,
    kind as type,
    current_attendance,
    max_occupancy,
    CASE
        WHEN max_occupancy IS NULL THEN '-'
        WHEN max_occupancy = 0 THEN '-'
        ELSE ROUND((current_attendance::NUMERIC / max_occupancy) * 100, 0)::TEXT || '%'
    END as utilization,
    CASE
        WHEN max_occupancy IS NOT NULL AND current_attendance >= max_occupancy THEN 'FULL'
        WHEN max_occupancy IS NOT NULL AND current_attendance >= max_occupancy * 0.8 THEN 'Nearly Full'
        ELSE 'Available'
    END as status
FROM location_attendance
ORDER BY path;
```

## Event Period Analysis

### Peak Attendance Times with Capacity Planning

```sql theme={null}
-- Identify peak times and capacity constraints across event periods
WITH period_metrics AS (
    SELECT
        ep.event_period_id,
        ep.starts_at,
        ep.ends_at,
        e.name as event_name,
        TO_CHAR(ep.starts_at, 'Day') as day_of_week,
        TO_CHAR(ep.starts_at, 'HH12:MI AM') as start_time,
        COUNT(DISTINCT c.check_in_id) as total_checkins,
        COUNT(DISTINCT CASE WHEN c.kind = 'Regular' THEN c.check_in_id END) as regular_checkins,
        COUNT(DISTINCT CASE WHEN c.kind = 'Guest' THEN c.check_in_id END) as guest_checkins,
        COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN c.check_in_id END) as volunteer_checkins,
        COUNT(DISTINCT ct.check_in_time_id) as actual_check_in_times
    FROM planning_center.checkins_event_periods ep
    JOIN planning_center.checkins_events e
        ON ep.event_id = e.event_id
    LEFT JOIN planning_center.checkins_checkin_relationships cr
        ON ep.event_period_id = cr.relationship_id
        AND cr.relationship_type = 'EventPeriod'
    LEFT JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
    LEFT JOIN planning_center.checkins_checkin_times ct
        ON c.check_in_id = ct.check_in_id
    WHERE ep.starts_at >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY ep.event_period_id, ep.starts_at, ep.ends_at, e.name
),
period_analysis AS (
    SELECT
        event_name,
        day_of_week,
        start_time,
        AVG(total_checkins) as avg_attendance,
        MAX(total_checkins) as peak_attendance,
        MIN(total_checkins) as min_attendance,
        AVG(guest_checkins) as avg_guests,
        AVG(volunteer_checkins) as avg_volunteers,
        COUNT(*) as period_count
    FROM period_metrics
    GROUP BY event_name, day_of_week, start_time
)
SELECT
    event_name,
    day_of_week,
    start_time,
    ROUND(avg_attendance, 0) as avg_attendance,
    peak_attendance,
    min_attendance,
    ROUND(avg_guests, 1) as avg_guests,
    ROUND(avg_volunteers, 1) as avg_volunteers,
    period_count as times_held,
    peak_attendance - ROUND(avg_attendance, 0) as peak_variance,
    CASE
        WHEN peak_attendance > avg_attendance * 1.3 THEN 'Prepare for ' || peak_attendance || ' attendees'
        WHEN avg_volunteers < 5 THEN 'Consider recruiting more volunteers'
        WHEN avg_guests > avg_attendance * 0.2 THEN 'High guest ratio - ensure welcome team'
        ELSE 'Normal operations'
    END as planning_note
FROM period_analysis
WHERE period_count >= 3  -- Only show recurring events
ORDER BY
    CASE day_of_week
        WHEN 'Sunday' THEN 1
        WHEN 'Saturday' THEN 2
        WHEN 'Wednesday' THEN 3
        ELSE 4
    END,
    start_time;
```

## Family Unit Analysis

### Family Check-in Patterns

```sql theme={null}
-- Identify families checking in together based on matching last names and check-in times
WITH checkin_groups AS (
    SELECT
        c1.check_in_id as parent_checkin,
        c1.first_name as parent_first_name,
        c1.last_name as family_name,
        c2.check_in_id as child_checkin,
        c2.first_name as child_first_name,
        c1.created_at as checkin_time,
        ABS(EXTRACT(EPOCH FROM (c2.created_at - c1.created_at))) as seconds_apart
    FROM planning_center.checkins_checkins c1
    JOIN planning_center.checkins_checkins c2
        ON c1.last_name = c2.last_name
        AND c1.check_in_id != c2.check_in_id
        AND DATE(c1.created_at) = DATE(c2.created_at)
        AND ABS(EXTRACT(EPOCH FROM (c2.created_at - c1.created_at))) <= 300  -- Within 5 minutes
    WHERE DATE(c1.created_at) = CURRENT_DATE
        AND c1.kind IN ('Regular', 'Guest')
        AND c2.kind IN ('Regular', 'Guest')
),
family_groups AS (
    SELECT
        family_name,
        COUNT(DISTINCT parent_checkin) + COUNT(DISTINCT child_checkin) as family_size,
        STRING_AGG(DISTINCT parent_first_name || ', ' || child_first_name, '; ') as family_members,
        MIN(checkin_time) as first_checkin,
        MAX(seconds_apart) as max_seconds_between_checkins
    FROM checkin_groups
    GROUP BY family_name
    HAVING COUNT(DISTINCT parent_checkin) + COUNT(DISTINCT child_checkin) >= 2
)
SELECT
    family_name,
    family_size,
    family_members,
    TO_CHAR(first_checkin, 'HH12:MI AM') as checkin_time,
    ROUND(max_seconds_between_checkins / 60.0, 1) as minutes_to_complete_checkin
FROM family_groups
ORDER BY family_size DESC, first_checkin;
```

## Station Performance Analysis

```sql theme={null}
-- Analyze check-in station usage and performance
WITH station_metrics AS (
    SELECT
        s.station_id,
        s.name as station_name,
        s.mode as station_mode,
        COUNT(DISTINCT c.check_in_id) as total_checkins,
        COUNT(DISTINCT DATE_TRUNC('hour', c.created_at)) as active_hours,
        MIN(c.created_at) as first_checkin,
        MAX(c.created_at) as last_checkin
    FROM planning_center.checkins_stations s
    LEFT JOIN planning_center.checkins_checkins c
        ON s.station_id = c.checked_in_at_id
    WHERE c.created_at >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY s.station_id, s.name, s.mode
),
station_performance AS (
    SELECT
        station_name,
        station_mode,
        total_checkins,
        active_hours,
        CASE
            WHEN active_hours > 0 THEN ROUND(total_checkins::NUMERIC / active_hours, 1)
            ELSE 0
        END as avg_checkins_per_hour,
        TO_CHAR(first_checkin, 'MM/DD HH12:MI AM') as first_use,
        TO_CHAR(last_checkin, 'MM/DD HH12:MI AM') as last_use,
        EXTRACT(EPOCH FROM (last_checkin - first_checkin)) / 3600 as total_hours_used
    FROM station_metrics
)
SELECT
    station_name,
    station_mode,
    total_checkins,
    avg_checkins_per_hour,
    ROUND(total_hours_used, 1) as total_hours_used,
    first_use,
    last_use,
    CASE
        WHEN avg_checkins_per_hour > 30 THEN 'High traffic - may need additional stations'
        WHEN avg_checkins_per_hour < 5 AND total_checkins > 10 THEN 'Low utilization - consider relocating'
        WHEN total_checkins = 0 THEN 'Unused - verify station is working'
        ELSE 'Normal usage'
    END as recommendation
FROM station_performance
ORDER BY total_checkins DESC;
```

## Next Steps

* Review [Reporting Examples](/planning-center/check-ins/reporting-examples) for production-ready reports
* Check the [Data Model](/planning-center/check-ins/data-model) for complete field documentation
* Return to [Basic Queries](/planning-center/check-ins/basic-queries) for simpler examples