Advanced Registrations Queries

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 Trends and Patterns

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_signup_relationships sr 
        ON sr.signup_id = s.signup_id 
        AND sr.relationship_type = 'signup_time'
    JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr.relationship_id
    JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_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, 1) as avg_registration_lead_time,
    ROUND(median_days_before, 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_signup_relationships sr 
        ON sr.signup_id = s.signup_id 
        AND sr.relationship_type = 'signup_time'
    JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr.relationship_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_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'), 'Month') 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_attendee_relationships ar 
        ON ar.attendee_id = a.attendee_id 
        AND ar.relationship_type = 'signup'
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = ar.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_signup_relationships sr 
        ON sr.relationship_id = sl.signup_location_id 
        AND sr.relationship_type = 'signup_location'
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = sr.signup_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_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_signup_relationships sr_campus 
        ON sr_campus.relationship_id = c.campus_id 
        AND sr_campus.relationship_type = 'campus'
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = sr_campus.signup_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_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;

Financial Analysis

Revenue Analysis by Category

-- Detailed revenue breakdown
WITH event_revenue AS (
    SELECT 
        cat.name as category_name,
        s.name as event_name,
        selt.name as selection_type,
        selt.price_cents,
        COUNT(a.attendee_id) as tickets_sold,
        (selt.price_cents * COUNT(a.attendee_id)) as revenue_cents
    FROM planning_center.registrations_signups s
    -- Join to category
    LEFT JOIN planning_center.registrations_signup_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id 
        AND sr_cat.relationship_type = 'category'
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    -- Join to selection types
    JOIN planning_center.registrations_selection_types selt 
        ON EXISTS (
            SELECT 1 FROM planning_center.registrations_signup_relationships sr
            WHERE sr.signup_id = s.signup_id
            AND sr.relationship_type = 'selection_type'
            AND sr.relationship_id = selt.selection_type_id
        )
    -- Join to attendees
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id 
        AND a.active = true
    WHERE s.archived = false
    GROUP BY cat.name, s.name, selt.name, selt.price_cents
),
category_summary AS (
    SELECT 
        COALESCE(category_name, 'Uncategorized') as category,
        COUNT(DISTINCT event_name) as events_count,
        SUM(tickets_sold) as total_tickets,
        SUM(revenue_cents) / 100.0 as total_revenue,
        AVG(price_cents) / 100.0 as avg_ticket_price,
        MIN(price_cents) / 100.0 as min_price,
        MAX(price_cents) / 100.0 as max_price
    FROM event_revenue
    GROUP BY category_name
)
SELECT 
    category,
    events_count,
    total_tickets,
    ROUND(total_revenue, 2) as total_revenue_dollars,
    ROUND(avg_ticket_price, 2) as avg_ticket_price,
    ROUND(total_revenue / NULLIF(total_tickets, 0), 2) as actual_avg_paid,
    min_price,
    max_price,
    ROUND(total_revenue / NULLIF(SUM(total_revenue) OVER (), 0) * 100, 1) as revenue_percentage
FROM category_summary
ORDER BY total_revenue DESC;

Price Elasticity Analysis

-- Analyze registration patterns by price point
WITH price_analysis AS (
    SELECT 
        selt.price_cents / 100.0 as price_dollars,
        s.name as event_name,
        COUNT(DISTINCT a.attendee_id) as registrations,
        COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlisted,
        AVG(DATE_PART('day', st.starts_at - a.created_at)) as avg_days_advance_registration
    FROM planning_center.registrations_selection_types selt
    JOIN planning_center.registrations_signup_relationships sr_sel 
        ON sr_sel.relationship_id = selt.selection_type_id 
        AND sr_sel.relationship_type = 'selection_type'
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = sr_sel.signup_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    WHERE s.archived = false
    GROUP BY selt.price_cents, s.name
),
price_bands AS (
    SELECT 
        CASE 
            WHEN price_dollars = 0 THEN 'Free'
            WHEN price_dollars <= 25 THEN '$1-25'
            WHEN price_dollars <= 50 THEN '$26-50'
            WHEN price_dollars <= 100 THEN '$51-100'
            ELSE '$100+'
        END as price_band,
        COUNT(DISTINCT event_name) as events,
        SUM(registrations) as total_registrations,
        AVG(registrations) as avg_registrations,
        SUM(waitlisted) as total_waitlisted,
        AVG(avg_days_advance_registration) as avg_advance_days
    FROM price_analysis
    GROUP BY 
        CASE 
            WHEN price_dollars = 0 THEN 'Free'
            WHEN price_dollars <= 25 THEN '$1-25'
            WHEN price_dollars <= 50 THEN '$26-50'
            WHEN price_dollars <= 100 THEN '$51-100'
            ELSE '$100+'
        END
)
SELECT 
    price_band,
    events,
    total_registrations,
    ROUND(avg_registrations, 1) as avg_registrations_per_event,
    total_waitlisted,
    ROUND(total_waitlisted * 100.0 / NULLIF(total_registrations, 0), 1) as waitlist_rate,
    ROUND(avg_advance_days, 1) as avg_days_advance_registration
FROM price_bands
ORDER BY 
    CASE price_band
        WHEN 'Free' THEN 1
        WHEN '$1-25' THEN 2
        WHEN '$26-50' THEN 3
        WHEN '$51-100' THEN 4
        ELSE 5
    END;

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_registration_relationships rr 
        ON rr.relationship_id = rp.person_id 
        AND rr.relationship_type = 'person'
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_id = rr.registration_id 
        AND ar.relationship_type = 'registration'
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar_signup 
        ON ar_signup.attendee_id = a.attendee_id 
        AND ar_signup.relationship_type = 'signup'
    LEFT JOIN planning_center.registrations_signups s 
        ON s.signup_id = ar_signup.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_registration_relationships rr 
        ON rr.relationship_id = rp.person_id 
        AND rr.relationship_type = 'person'
    JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_id = rr.registration_id 
        AND ar.relationship_type = 'registration'
    JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id 
        AND a.active = true
    JOIN planning_center.registrations_attendee_relationships ar_signup 
        ON ar_signup.attendee_id = a.attendee_id 
        AND ar_signup.relationship_type = 'signup'
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = ar_signup.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id 
        AND sr_cat.relationship_type = 'category'
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = '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 gd.donor_id) as donors_count,
        COUNT(DISTINCT CASE 
            WHEN gd.created_at > ea.event_date 
            THEN gd.donor_id 
        END) as new_donors_after_event,
        SUM(CASE 
            WHEN gd.created_at > ea.event_date 
            THEN gd.amount_cents / 100.0 
        END) as giving_after_event
    FROM event_attendees ea
    LEFT JOIN planning_center.giving_donors gd 
        ON gd.person_id = ea.person_id
    LEFT JOIN planning_center.giving_donations don 
        ON don.donor_id = gd.donor_id
    GROUP BY ea.event_category
)
SELECT 
    COALESCE(event_category, 'Uncategorized') as category,
    attendee_count,
    donors_count,
    ROUND(donors_count * 100.0 / attendee_count, 1) as donor_percentage,
    new_donors_after_event,
    ROUND(new_donors_after_event * 100.0 / attendee_count, 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_attendee_relationships ar 
        ON ar.attendee_id = a.attendee_id 
        AND ar.relationship_type = '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;

Materialized View Pattern

-- Create a materialized view for frequently accessed metrics
-- (Run this once to create the view)
CREATE MATERIALIZED VIEW planning_center.registrations_event_summary AS
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,
        AVG(selt.price_cents) / 100.0 as avg_price
    FROM planning_center.registrations_signups s
    -- All the necessary joins...
    LEFT JOIN planning_center.registrations_signup_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id AND sr_cat.relationship_type = 'category'
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_camp 
        ON sr_camp.signup_id = s.signup_id AND sr_camp.relationship_type = 'campus'
    LEFT JOIN planning_center.registrations_campuses camp 
        ON camp.campus_id = sr_camp.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_loc 
        ON sr_loc.signup_id = s.signup_id AND sr_loc.relationship_type = 'signup_location'
    LEFT JOIN planning_center.registrations_signup_locations loc 
        ON loc.signup_location_id = sr_loc.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_tim 
        ON sr_tim.signup_id = s.signup_id AND sr_tim.relationship_type = 'signup_time'
    LEFT JOIN planning_center.registrations_signup_times tim 
        ON tim.signup_time_id = sr_tim.relationship_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' AND ar.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    LEFT JOIN planning_center.registrations_selection_types selt 
        ON EXISTS (
            SELECT 1 FROM planning_center.registrations_signup_relationships sr
            WHERE sr.signup_id = s.signup_id
            AND sr.relationship_type = 'selection_type'
            AND sr.relationship_id = selt.selection_type_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;

-- Refresh the materialized view periodically
REFRESH MATERIALIZED VIEW planning_center.registrations_event_summary;

-- Query the materialized view for fast results
SELECT * FROM planning_center.registrations_event_summary
WHERE archived = false
  AND event_date >= CURRENT_DATE
ORDER BY event_date;

Tips for Advanced Queries

Use CTEs liberally - They make complex queries readable and maintainable
Index awareness - Structure WHERE clauses to use existing indexes
Window functions - Great for running totals, rankings, and comparisons
COALESCE for NULLs - Handle missing data gracefully
Cross-module carefully - Join to other modules only when necessary
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.

Getting started

Release Notes

Planning Center

Advanced queries

Advanced Registrations Queries

Complex Analytics for Strategic Event Management

Query Requirements

Schema Prefix

Row Level Security (RLS)

Registration Trends and Patterns

Registration Velocity Analysis

Year-over-Year Event Comparison

Waitlist Analytics

Waitlist Conversion Funnel

Geographic Analysis

Registration Heatmap Data

Campus Performance Comparison

Financial Analysis

Revenue Analysis by Category

Price Elasticity Analysis

Cross-Module Integration

Registrations with People Data

Registration Impact on Giving

Performance Optimization Patterns

Indexed Subquery Pattern

Materialized View Pattern

Tips for Advanced Queries

Next Steps

Getting started

Release Notes

Planning Center

​Advanced Registrations Queries

​Complex Analytics for Strategic Event Management

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Registration Trends and Patterns

​Registration Velocity Analysis

​Year-over-Year Event Comparison

​Waitlist Analytics

​Waitlist Conversion Funnel

​Geographic Analysis

​Registration Heatmap Data

​Campus Performance Comparison

​Financial Analysis

​Revenue Analysis by Category

​Price Elasticity Analysis

​Cross-Module Integration

​Registrations with People Data

​Registration Impact on Giving

​Performance Optimization Patterns

​Indexed Subquery Pattern

​Materialized View Pattern

​Tips for Advanced Queries

​Next Steps

Advanced Registrations Queries

Complex Analytics for Strategic Event Management

Query Requirements

Schema Prefix

Row Level Security (RLS)

Registration Trends and Patterns

Registration Velocity Analysis

Year-over-Year Event Comparison

Waitlist Analytics

Waitlist Conversion Funnel

Geographic Analysis

Registration Heatmap Data

Campus Performance Comparison

Financial Analysis

Revenue Analysis by Category

Price Elasticity Analysis

Cross-Module Integration

Registrations with People Data

Registration Impact on Giving

Performance Optimization Patterns

Indexed Subquery Pattern

Materialized View Pattern

Tips for Advanced Queries

Next Steps