Planning Center Registrations SQL Queries

-- See your 10 most recent event signups
SELECT 
    s.signup_id,
    s.name as event_name,
    s.description,
    s.open_at,
    s.close_at,
    COUNT(DISTINCT a.attendee_id) as attendee_count,
    COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlist_count
FROM planning_center.registrations_signups s
LEFT JOIN planning_center.registrations_attendees a 
    ON EXISTS (
        SELECT 1
        FROM planning_center.registrations_attendees_relationships ar
        JOIN planning_center.registrations_registrations_relationships rr
            ON rr.registration_id = ar.relationship_id
            AND rr.relationship_type IN ('Signup', 'signup')
        WHERE ar.attendee_id = a.attendee_id
        AND ar.relationship_type IN ('Registration', 'registration')
        AND rr.relationship_id = s.signup_id
    )
WHERE s.archived = false
GROUP BY s.signup_id, s.name, s.description, s.open_at, s.close_at, s.created_at
ORDER BY s.created_at DESC
LIMIT 10;

-- Events with waitlists and conversion potential
SELECT 
    s.name as event_name,
    st.starts_at as event_date,
    COUNT(CASE WHEN a.active = true THEN 1 END) as registered_count,
    COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlist_count,
    sl.name as location_name
FROM planning_center.registrations_signups s
LEFT JOIN planning_center.registrations_signups_relationships sr 
    ON sr.signup_id = s.signup_id 
    AND sr.relationship_type IN ('SignupTime', 'signup_time')
LEFT JOIN planning_center.registrations_signup_times st 
    ON st.signup_time_id = sr.relationship_id
LEFT JOIN planning_center.registrations_signups_relationships slr 
    ON slr.signup_id = s.signup_id 
    AND slr.relationship_type IN ('SignupLocation', 'signup_location')
LEFT JOIN planning_center.registrations_signup_locations sl 
    ON sl.signup_location_id = slr.relationship_id
LEFT JOIN planning_center.registrations_attendees a 
    ON EXISTS (
        SELECT 1
        FROM planning_center.registrations_attendees_relationships ar
        JOIN planning_center.registrations_registrations_relationships rr
            ON rr.registration_id = ar.relationship_id
            AND rr.relationship_type IN ('Signup', 'signup')
        WHERE ar.attendee_id = a.attendee_id
        AND ar.relationship_type IN ('Registration', 'registration')
        AND rr.relationship_id = s.signup_id
    )
WHERE s.archived = false
  AND st.starts_at >= CURRENT_DATE
GROUP BY s.name, st.starts_at, sl.name
HAVING COUNT(CASE WHEN a.waitlisted = true THEN 1 END) > 0
ORDER BY st.starts_at;

-- Registration patterns by days before event
WITH registration_timeline AS (
    SELECT 
        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
    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
    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
    WHERE a.active = true
),
windowed AS (
    SELECT 
        CASE 
            WHEN days_before_event >= 30 THEN '30+ days'
            WHEN days_before_event >= 14 THEN '14-29 days'
            WHEN days_before_event >= 7 THEN '7-13 days'
            WHEN days_before_event >= 1 THEN '1-6 days'
            ELSE 'Day of event'
        END as registration_window
    FROM registration_timeline
    WHERE days_before_event >= 0
)
SELECT 
    registration_window,
    COUNT(*) as registration_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM windowed
GROUP BY registration_window
ORDER BY 
    CASE registration_window
        WHEN '30+ days' THEN 1
        WHEN '14-29 days' THEN 2
        WHEN '7-13 days' THEN 3
        WHEN '1-6 days' THEN 4
        WHEN 'Day of event' THEN 5
    END;