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.

Documentation Index

​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