Skip to main content
Parable
Open Parable

Advanced Planning Center Registrations Queries

Advanced Registrations SQL using CTEs and window functions to analyze signup velocity, waitlist movement, and overall event performance.

Complex Analytics for Strategic Event Management

Take your event analysis to the next level with CTEs, window functions, and cross-module integration. These queries provide deep insights for strategic planning and optimization.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Registrations module live in the planning_center schema. Always prefix table names with planning_center. in advanced queries.

✅ CORRECT: SELECT * FROM planning_center.registrations_attendees ❌ INCORRECT: SELECT * FROM registrations_attendees

Row Level Security (RLS)

Row Level Security automatically filters results by:

  • tenant_organization_id – limits data to your organization
  • system_status – active records returned by default

Skip manual filters for these columns—RLS already enforces them and redundant predicates can hide data or slow execution:

  • ❌ WHERE tenant_organization_id = 1
  • ❌ WHERE system_status = 'active'

Focus on event-specific logic (archived flags, status transitions, waitlists) while RLS manages tenancy and system status.

Registration Velocity Analysis

-- Track registration speed and predict fill rates
WITH registration_velocity AS (
    SELECT 
        s.signup_id,
        s.name as event_name,
        st.starts_at as event_date,
        a.created_at as registration_date,
        DATE_PART('day', st.starts_at - a.created_at) as days_before_event,
        COUNT(*) OVER (
            PARTITION BY s.signup_id 
            ORDER BY a.created_at 
            ROWS UNBOUNDED PRECEDING
        ) as cumulative_registrations,
        ROW_NUMBER() OVER (
            PARTITION BY s.signup_id 
            ORDER BY a.created_at
        ) as registration_order
    FROM planning_center.registrations_signups s
    JOIN planning_center.registrations_signups_relationships sr 
        ON sr.signup_id = s.signup_id 
        AND sr.relationship_type IN ('SignupTime', 'signup_time')
    JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr.relationship_id
    JOIN planning_center.registrations_registrations_relationships ar_sg
        ON ar_sg.relationship_type IN ('Signup', 'signup')
        AND ar_sg.relationship_id = s.signup_id
    JOIN planning_center.registrations_attendees_relationships ar
        ON ar.relationship_type IN ('Registration', 'registration')
        AND ar.relationship_id = ar_sg.registration_id
    JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id 
        AND a.active = true
    WHERE s.archived = false
),
velocity_stats AS (
    SELECT 
        event_name,
        event_date,
        MAX(cumulative_registrations) as total_registrations,
        AVG(days_before_event) as avg_days_before,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_before_event) as median_days_before,
        COUNT(CASE WHEN days_before_event >= 30 THEN 1 END) as early_birds,
        COUNT(CASE WHEN days_before_event < 7 THEN 1 END) as last_minute
    FROM registration_velocity
    GROUP BY event_name, event_date
)
SELECT 
    event_name,
    event_date,
    total_registrations,
    ROUND(avg_days_before::numeric, 1) as avg_registration_lead_time,
    ROUND(median_days_before::numeric, 1) as median_registration_lead_time,
    ROUND(early_birds * 100.0 / total_registrations, 1) as early_bird_percentage,
    ROUND(last_minute * 100.0 / total_registrations, 1) as last_minute_percentage
FROM velocity_stats
ORDER BY event_date DESC;

Year-over-Year Event Comparison

-- Compare event performance across years
WITH yearly_events AS (
    SELECT 
        s.name as event_name,
        DATE_PART('year', st.starts_at) as event_year,
        DATE_PART('month', st.starts_at) as event_month,
        COUNT(DISTINCT a.attendee_id) as total_attendees,
        COUNT(CASE WHEN a.active = true THEN 1 END) as active_registrations,
        COUNT(CASE WHEN a.canceled = true THEN 1 END) as cancellations,
        COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlisted
    FROM planning_center.registrations_signups s
    JOIN planning_center.registrations_signups_relationships sr 
        ON sr.signup_id = s.signup_id 
        AND sr.relationship_type IN ('SignupTime', 'signup_time')
    JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr.relationship_id
    LEFT JOIN planning_center.registrations_registrations_relationships ar_sg
        ON ar_sg.relationship_type IN ('Signup', 'signup')
        AND ar_sg.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees_relationships ar
        ON ar.relationship_type IN ('Registration', 'registration')
        AND ar.relationship_id = ar_sg.registration_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    GROUP BY s.name, DATE_PART('year', st.starts_at), DATE_PART('month', st.starts_at)
),
year_comparison AS (
    SELECT 
        event_name,
        event_month,
        MAX(CASE WHEN event_year = DATE_PART('year', CURRENT_DATE) - 1 
            THEN total_attendees END) as last_year,
        MAX(CASE WHEN event_year = DATE_PART('year', CURRENT_DATE) 
            THEN total_attendees END) as this_year,
        MAX(CASE WHEN event_year = DATE_PART('year', CURRENT_DATE) - 1 
            THEN cancellations END) as cancellations_last_year,
        MAX(CASE WHEN event_year = DATE_PART('year', CURRENT_DATE) 
            THEN cancellations END) as cancellations_this_year
    FROM yearly_events
    GROUP BY event_name, event_month
)
SELECT 
    event_name,
    TO_CHAR(TO_DATE(event_month::text, 'MM'), 'FMMonth') as month,
    COALESCE(last_year, 0) as last_year_attendees,
    COALESCE(this_year, 0) as this_year_attendees,
    CASE 
        WHEN last_year > 0 
        THEN ROUND((this_year - last_year) * 100.0 / last_year, 1)
        ELSE NULL 
    END as growth_percentage,
    COALESCE(cancellations_last_year, 0) as cancellations_last_year,
    COALESCE(cancellations_this_year, 0) as cancellations_this_year
FROM year_comparison
WHERE last_year IS NOT NULL OR this_year IS NOT NULL
ORDER BY event_month, event_name;

Waitlist Analytics

Waitlist Conversion Funnel

-- Analyze waitlist to registration conversions
WITH waitlist_timeline AS (
    SELECT 
        a.attendee_id,
        s.signup_id,
        s.name as event_name,
        a.waitlisted_at,
        a.created_at as registration_date,
        a.waitlisted,
        a.active,
        a.canceled,
        LEAD(a.waitlisted) OVER (
            PARTITION BY a.attendee_id 
            ORDER BY a.updated_at
        ) as next_waitlist_status,
        LEAD(a.active) OVER (
            PARTITION BY a.attendee_id 
            ORDER BY a.updated_at
        ) as next_active_status
    FROM planning_center.registrations_attendees a
    JOIN planning_center.registrations_attendees_relationships ar
        ON ar.attendee_id = a.attendee_id
        AND ar.relationship_type IN ('Registration', 'registration')
    JOIN planning_center.registrations_registrations_relationships ar_sg
        ON ar_sg.registration_id = ar.relationship_id
        AND ar_sg.relationship_type IN ('Signup', 'signup')
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = ar_sg.relationship_id
    WHERE a.waitlisted_at IS NOT NULL
),
conversion_metrics AS (
    SELECT 
        event_name,
        COUNT(DISTINCT attendee_id) as total_waitlisted,
        COUNT(DISTINCT CASE 
            WHEN active = true 
            THEN attendee_id 
        END) as converted_to_active,
        COUNT(DISTINCT CASE 
            WHEN canceled = true 
            THEN attendee_id 
        END) as canceled_from_waitlist,
        AVG(CASE 
            WHEN active = true 
            THEN EXTRACT(EPOCH FROM (registration_date - waitlisted_at))/3600 
        END) as avg_hours_to_conversion
    FROM waitlist_timeline
    GROUP BY event_name
)
SELECT 
    event_name,
    total_waitlisted,
    converted_to_active,
    ROUND(converted_to_active * 100.0 / NULLIF(total_waitlisted, 0), 1) as conversion_rate,
    canceled_from_waitlist,
    ROUND(canceled_from_waitlist * 100.0 / NULLIF(total_waitlisted, 0), 1) as cancellation_rate,
    ROUND(avg_hours_to_conversion / 24, 1) as avg_days_to_conversion
FROM conversion_metrics
WHERE total_waitlisted > 0
ORDER BY conversion_rate DESC;

Geographic Analysis

Registration Heatmap Data

-- Geographic distribution of registrations
WITH location_registrations AS (
    SELECT 
        sl.latitude,
        sl.longitude,
        sl.name as location_name,
        sl.formatted_address,
        s.name as event_name,
        COUNT(DISTINCT a.attendee_id) as registration_count
    FROM planning_center.registrations_signup_locations sl
    JOIN planning_center.registrations_signups_relationships sr 
        ON sr.relationship_id = sl.signup_location_id 
        AND sr.relationship_type IN ('SignupLocation', 'signup_location')
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = sr.signup_id
    LEFT JOIN planning_center.registrations_registrations_relationships ar_sg
        ON ar_sg.relationship_type IN ('Signup', 'signup')
        AND ar_sg.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees_relationships ar
        ON ar.relationship_type IN ('Registration', 'registration')
        AND ar.relationship_id = ar_sg.registration_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id 
        AND a.active = true
    WHERE sl.latitude IS NOT NULL 
      AND sl.longitude IS NOT NULL
      AND s.archived = false
    GROUP BY sl.latitude, sl.longitude, sl.name, sl.formatted_address, s.name
),
location_stats AS (
    SELECT 
        latitude,
        longitude,
        location_name,
        formatted_address,
        COUNT(DISTINCT event_name) as events_at_location,
        SUM(registration_count) as total_registrations,
        AVG(registration_count) as avg_registrations_per_event,
        STRING_AGG(event_name || ' (' || registration_count || ')', ', ' 
            ORDER BY registration_count DESC) as event_details
    FROM location_registrations
    GROUP BY latitude, longitude, location_name, formatted_address
)
SELECT 
    location_name,
    formatted_address,
    latitude,
    longitude,
    events_at_location,
    total_registrations,
    ROUND(avg_registrations_per_event, 1) as avg_registrations,
    event_details
FROM location_stats
ORDER BY total_registrations DESC;

Campus Performance Comparison

-- Comprehensive campus metrics
WITH campus_events AS (
    SELECT 
        c.campus_id,
        c.name as campus_name,
        s.signup_id,
        s.name as event_name,
        st.starts_at as event_date,
        COUNT(DISTINCT a.attendee_id) as total_attendees,
        COUNT(CASE WHEN a.active = true THEN 1 END) as active_registrations,
        COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlisted
    FROM planning_center.registrations_campuses c
    JOIN planning_center.registrations_signups_relationships sr_campus 
        ON sr_campus.relationship_id = c.campus_id 
        AND sr_campus.relationship_type IN ('Campus', 'campus')
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = sr_campus.signup_id
    LEFT JOIN planning_center.registrations_signups_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type IN ('SignupTime', 'signup_time')
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    LEFT JOIN planning_center.registrations_registrations_relationships ar_sg
        ON ar_sg.relationship_type IN ('Signup', 'signup')
        AND ar_sg.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees_relationships ar
        ON ar.relationship_type IN ('Registration', 'registration')
        AND ar.relationship_id = ar_sg.registration_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    WHERE s.archived = false
    GROUP BY c.campus_id, c.name, s.signup_id, s.name, st.starts_at
),
campus_summary AS (
    SELECT 
        campus_name,
        COUNT(DISTINCT signup_id) as total_events,
        SUM(total_attendees) as total_registrations,
        AVG(total_attendees) as avg_attendees_per_event,
        SUM(waitlisted) as total_waitlisted,
        MIN(event_date) as first_event,
        MAX(event_date) as last_event
    FROM campus_events
    GROUP BY campus_name
)
SELECT 
    campus_name,
    total_events,
    total_registrations,
    ROUND(avg_attendees_per_event, 1) as avg_attendees,
    total_waitlisted,
    ROUND(total_waitlisted * 100.0 / NULLIF(total_registrations, 0), 1) as waitlist_percentage,
    first_event::date as first_event_date,
    last_event::date as last_event_date,
    DATE_PART('day', last_event - first_event) as days_of_activity
FROM campus_summary
ORDER BY total_registrations DESC;

Demand Analysis

The queries below analyse demand. For actual registration cost, collected revenue, and outstanding balances, see Revenue Collected and Outstanding by Event.

Category Performance by Volume

-- Which categories of events actually draw registrations
WITH signup_categories AS (
    SELECT signup_id, relationship_id as category_id
    FROM planning_center.registrations_signups_relationships
    WHERE relationship_type IN ('Category', 'category')
),
attendee_counts AS (
    SELECT
        rr.relationship_id as signup_id,
        COUNT(DISTINCT a.attendee_id) FILTER (WHERE a.active = true) as active_attendees,
        COUNT(DISTINCT a.attendee_id) FILTER (WHERE a.waitlisted = true) as waitlisted,
        COUNT(DISTINCT a.attendee_id) FILTER (WHERE a.canceled = true) as canceled
    FROM planning_center.registrations_registrations_relationships rr
    JOIN planning_center.registrations_attendees_relationships ar
        ON ar.relationship_id = rr.registration_id
        AND ar.relationship_type IN ('Registration', 'registration')
    JOIN planning_center.registrations_attendees a
        ON a.attendee_id = ar.attendee_id
    WHERE rr.relationship_type IN ('Signup', 'signup')
    GROUP BY rr.relationship_id
)
SELECT
    cat.name as category_name,
    COUNT(DISTINCT s.signup_id) as events,
    COALESCE(SUM(ac.active_attendees), 0) as total_registered,
    COALESCE(SUM(ac.waitlisted), 0) as total_waitlisted,
    COALESCE(SUM(ac.canceled), 0) as total_canceled,
    ROUND(
        COALESCE(SUM(ac.active_attendees), 0)::NUMERIC
        / NULLIF(COUNT(DISTINCT s.signup_id), 0), 1
    ) as avg_registrations_per_event
FROM planning_center.registrations_categories cat
JOIN signup_categories sc ON sc.category_id = cat.category_id
JOIN planning_center.registrations_signups s ON s.signup_id = sc.signup_id
LEFT JOIN attendee_counts ac ON ac.signup_id = s.signup_id
GROUP BY cat.name
ORDER BY total_registered DESC;

How Early People Register

-- Lead time between registering and the event starting.
-- A negative average means registrations kept arriving after the start date,
-- which is normal for rolling or ongoing signups.
WITH event_start AS (
    SELECT
        sr.signup_id,
        MIN(st.starts_at) as event_start
    FROM planning_center.registrations_signups_relationships sr
    JOIN planning_center.registrations_signup_times st
        ON st.signup_time_id = sr.relationship_id
    WHERE sr.relationship_type IN ('SignupTime', 'signup_time')
    GROUP BY sr.signup_id
)
SELECT
    s.name as event_name,
    es.event_start,
    COUNT(DISTINCT a.attendee_id) FILTER (WHERE a.active = true) as registered,
    COUNT(DISTINCT a.attendee_id) FILTER (WHERE a.waitlisted = true) as waitlisted,
    ROUND(AVG(DATE_PART('day', es.event_start - a.created_at))::NUMERIC, 1) as avg_days_booked_ahead
FROM planning_center.registrations_signups s
JOIN event_start es ON es.signup_id = s.signup_id
JOIN planning_center.registrations_registrations_relationships rr
    ON rr.relationship_id = s.signup_id
    AND rr.relationship_type IN ('Signup', 'signup')
JOIN planning_center.registrations_attendees_relationships ar
    ON ar.relationship_id = rr.registration_id
    AND ar.relationship_type IN ('Registration', 'registration')
JOIN planning_center.registrations_attendees a
    ON a.attendee_id = ar.attendee_id
WHERE s.archived = false
GROUP BY s.name, es.event_start
ORDER BY registered DESC;

Cross-Module Integration

Registrations with People Data

-- Combine registrations with People module demographics
WITH person_registrations AS (
    SELECT 
        rp.person_id,
        rp.name as registrant_name,
        pp.birthdate,
        pp.gender,
        pp.membership,
        pp.status as person_status,
        COUNT(DISTINCT s.signup_id) as events_registered,
        SUM(CASE WHEN a.active = true THEN 1 ELSE 0 END) as active_registrations,
        SUM(CASE WHEN a.waitlisted = true THEN 1 ELSE 0 END) as waitlist_registrations
    FROM planning_center.registrations_people rp
    LEFT JOIN planning_center.people_people pp 
        ON pp.person_id = rp.person_id
    LEFT JOIN planning_center.registrations_attendees_relationships ar_person
        ON ar_person.relationship_id = rp.person_id
        AND ar_person.relationship_type IN ('Person', 'person')
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar_person.attendee_id
    LEFT JOIN planning_center.registrations_attendees_relationships ar_signup
        ON ar_signup.attendee_id = a.attendee_id
        AND ar_signup.relationship_type IN ('Registration', 'registration')
    LEFT JOIN planning_center.registrations_registrations_relationships ar_signup_sg
        ON ar_signup_sg.registration_id = ar_signup.relationship_id
        AND ar_signup_sg.relationship_type IN ('Signup', 'signup')
    LEFT JOIN planning_center.registrations_signups s 
        ON s.signup_id = ar_signup_sg.relationship_id
    GROUP BY rp.person_id, rp.name, pp.birthdate, pp.gender, pp.membership, pp.status
),
demographic_summary AS (
    SELECT 
        CASE 
            WHEN birthdate IS NULL THEN 'Unknown'
            WHEN DATE_PART('year', AGE(birthdate)) < 18 THEN 'Youth'
            WHEN DATE_PART('year', AGE(birthdate)) < 30 THEN 'Young Adult'
            WHEN DATE_PART('year', AGE(birthdate)) < 50 THEN 'Adult'
            ELSE 'Senior'
        END as age_group,
        COALESCE(gender, 'Not Specified') as gender,
        COALESCE(membership, 'Non-Member') as membership_status,
        COUNT(DISTINCT person_id) as unique_registrants,
        SUM(events_registered) as total_event_registrations,
        AVG(events_registered) as avg_events_per_person,
        SUM(active_registrations) as total_active,
        SUM(waitlist_registrations) as total_waitlisted
    FROM person_registrations
    WHERE person_status = 'active'
    GROUP BY age_group, gender, membership_status
)
SELECT 
    age_group,
    gender,
    membership_status,
    unique_registrants,
    total_event_registrations,
    ROUND(avg_events_per_person, 1) as avg_events_per_person,
    total_active,
    total_waitlisted,
    ROUND(total_waitlisted * 100.0 / NULLIF(total_active + total_waitlisted, 0), 1) as waitlist_percentage
FROM demographic_summary
ORDER BY unique_registrants DESC;

Registration Impact on Giving

-- Analyze giving patterns of event attendees
WITH event_attendees AS (
    SELECT DISTINCT
        rp.person_id,
        rp.name,
        s.name as event_name,
        cat.name as event_category,
        st.starts_at as event_date
    FROM planning_center.registrations_people rp
    JOIN planning_center.registrations_attendees_relationships ar_person
        ON ar_person.relationship_id = rp.person_id
        AND ar_person.relationship_type IN ('Person', 'person')
    JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar_person.attendee_id
        AND a.active = true
    JOIN planning_center.registrations_attendees_relationships ar_signup
        ON ar_signup.attendee_id = a.attendee_id
        AND ar_signup.relationship_type IN ('Registration', 'registration')
    JOIN planning_center.registrations_registrations_relationships ar_signup_sg
        ON ar_signup_sg.registration_id = ar_signup.relationship_id
        AND ar_signup_sg.relationship_type IN ('Signup', 'signup')
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = ar_signup_sg.relationship_id
    LEFT JOIN planning_center.registrations_signups_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id 
        AND sr_cat.relationship_type IN ('Category', 'category')
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signups_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type IN ('SignupTime', 'signup_time')
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
),
giving_analysis AS (
    SELECT 
        ea.event_category,
        COUNT(DISTINCT ea.person_id) as attendee_count,
        COUNT(DISTINCT gp.person_id) as donors_count,
        COUNT(DISTINCT CASE 
            WHEN d.received_at > ea.event_date 
            THEN gp.person_id 
        END) as donors_after_event,
        SUM(CASE 
            WHEN d.received_at > ea.event_date 
            THEN d.amount_cents / 100.0 
        END) as giving_after_event
    FROM event_attendees ea
    LEFT JOIN planning_center.giving_people gp 
        ON gp.person_id = ea.person_id
    LEFT JOIN planning_center.giving_donations_relationships dr
        ON dr.relationship_id = gp.person_id AND dr.relationship_type IN ('Person', 'person')
    LEFT JOIN planning_center.giving_donations d 
        ON d.donation_id = dr.donation_id
    GROUP BY ea.event_category
)
SELECT 
    COALESCE(event_category, 'Uncategorized') as category,
    attendee_count,
    donors_count,
    ROUND(donors_count * 100.0 / NULLIF(attendee_count, 0), 1) as donor_percentage,
    donors_after_event,
    ROUND(donors_after_event * 100.0 / NULLIF(attendee_count, 0), 1) as new_donor_percentage,
    COALESCE(ROUND(giving_after_event, 2), 0) as total_giving_after_events
FROM giving_analysis
WHERE attendee_count > 0
ORDER BY attendee_count DESC;

Performance Optimization Patterns

Indexed Subquery Pattern

-- Efficient pattern for large datasets using indexed subqueries
WITH indexed_signups AS (
    SELECT 
        signup_id,
        name,
        archived
    FROM planning_center.registrations_signups
    WHERE archived = false
      AND created_at >= CURRENT_DATE - INTERVAL '1 year'
),
indexed_attendees AS (
    SELECT 
        ar.relationship_id as signup_id,
        COUNT(*) as attendee_count,
        COUNT(CASE WHEN a.active = true THEN 1 END) as active_count,
        COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlist_count
    FROM planning_center.registrations_attendees a
    JOIN planning_center.registrations_attendees_relationships ar
        ON ar.attendee_id = a.attendee_id
        AND ar.relationship_type IN ('Registration', 'registration')
    JOIN planning_center.registrations_registrations_relationships ar_sg
        ON ar_sg.registration_id = ar.relationship_id
        AND ar_sg.relationship_type IN ('Signup', 'signup')
    WHERE a.created_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY ar.relationship_id
)
SELECT 
    s.name,
    COALESCE(a.attendee_count, 0) as total_attendees,
    COALESCE(a.active_count, 0) as active_registrations,
    COALESCE(a.waitlist_count, 0) as waitlisted,
    CASE 
        WHEN a.waitlist_count > 0 
        THEN ROUND(a.waitlist_count * 100.0 / a.attendee_count, 1)
        ELSE 0 
    END as waitlist_percentage
FROM indexed_signups s
LEFT JOIN indexed_attendees a 
    ON a.signup_id = s.signup_id
ORDER BY a.attendee_count DESC NULLS LAST;

Event Metrics Rollup

Your Parable database connection is read-only. You cannot create materialized views or indexes through it. Run this query directly, schedule it as a Parable report, or let your BI tool cache the result set.

-- Frequently accessed event metrics — schedule as a report or BI dataset
WITH event_metrics AS (
    SELECT 
        s.signup_id,
        s.name as event_name,
        s.archived,
        s.open_at,
        s.close_at,
        cat.name as category,
        camp.name as campus,
        loc.name as location,
        tim.starts_at as event_date,
        COUNT(DISTINCT a.attendee_id) as total_attendees,
        COUNT(CASE WHEN a.active = true THEN 1 END) as active_registrations,
        COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlisted,
        COUNT(CASE WHEN a.canceled = true THEN 1 END) as canceled
    FROM planning_center.registrations_signups s
    -- All the necessary joins...
    LEFT JOIN planning_center.registrations_signups_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id AND sr_cat.relationship_type IN ('Category', 'category')
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signups_relationships sr_camp 
        ON sr_camp.signup_id = s.signup_id AND sr_camp.relationship_type IN ('Campus', 'campus')
    LEFT JOIN planning_center.registrations_campuses camp 
        ON camp.campus_id = sr_camp.relationship_id
    LEFT JOIN planning_center.registrations_signups_relationships sr_loc 
        ON sr_loc.signup_id = s.signup_id AND sr_loc.relationship_type IN ('SignupLocation', 'signup_location')
    LEFT JOIN planning_center.registrations_signup_locations loc 
        ON loc.signup_location_id = sr_loc.relationship_id
    LEFT JOIN planning_center.registrations_signups_relationships sr_tim 
        ON sr_tim.signup_id = s.signup_id AND sr_tim.relationship_type IN ('SignupTime', 'signup_time')
    LEFT JOIN planning_center.registrations_signup_times tim 
        ON tim.signup_time_id = sr_tim.relationship_id
    LEFT JOIN planning_center.registrations_registrations_relationships ar_sg
        ON ar_sg.relationship_type IN ('Signup', 'signup') AND ar_sg.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees_relationships ar
        ON ar.relationship_type IN ('Registration', 'registration') AND ar.relationship_id = ar_sg.registration_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    GROUP BY s.signup_id, s.name, s.archived, s.open_at, s.close_at, 
             cat.name, camp.name, loc.name, tim.starts_at
)
SELECT *
FROM event_metrics
WHERE archived = false
  AND event_date >= CURRENT_DATE
ORDER BY event_date;

Tips for Advanced Queries

  1. Use CTEs liberally - They make complex queries readable and maintainable
  2. Index awareness - Structure WHERE clauses to use existing indexes
  3. Window functions - Great for running totals, rankings, and comparisons
  4. COALESCE for NULLs - Handle missing data gracefully
  5. Cross-module carefully - Join to other modules only when necessary
  6. Test with EXPLAIN - Analyze query plans for performance bottlenecks

Next Steps

Ready to build production reports? Check out our Reporting Examples for complete, ready-to-use report templates.

Basic Planning Center Registrations Queries

Practical SQL for Planning Center Registrations: list signups by event, count attendees, and filter registrations by status or date.

Planning Center Registrations Report Examples

Production-ready Registrations reports for event teams: signup summaries, waitlist status, and attendee rosters optimized for BI tools.

On this page

Complex Analytics for Strategic Event ManagementQuery RequirementsSchema PrefixRow Level Security (RLS)Registration Trends and PatternsRegistration Velocity AnalysisYear-over-Year Event ComparisonWaitlist AnalyticsWaitlist Conversion FunnelGeographic AnalysisRegistration Heatmap DataCampus Performance ComparisonDemand AnalysisCategory Performance by VolumeHow Early People RegisterCross-Module IntegrationRegistrations with People DataRegistration Impact on GivingPerformance Optimization PatternsIndexed Subquery PatternEvent Metrics RollupTips for Advanced QueriesNext Steps