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.

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

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

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

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

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

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

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

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

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

-- 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 for production-ready reports
Check the Data Model for complete field documentation
Return to Basic Queries for simpler examples

​Advanced Check-ins Queries

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Attendance Analytics

​Weekly Attendance Trends with Growth Metrics

​Service Time Optimization Analysis

​Volunteer Analytics

​Volunteer Reliability Score

​Volunteer-to-Child Ratio Analysis with Alerts

​Guest Retention Analysis

​First-Time Guest Return Rate

​Location Hierarchy Analysis

​Multi-Level Location Utilization

​Event Period Analysis

​Peak Attendance Times with Capacity Planning

​Family Unit Analysis

​Family Check-in Patterns

​Station Performance Analysis

​Next Steps