= Event RSVP Conversion Rate Analysis =

== 3. Scenario Overview ==

This scenario analyzes RSVP conversion efficiency and guest engagement across wedding events.

The analysis combines:
* guest invitations
* RSVP responses
* attendance records
* response rates
* attendance conversion metrics

The report enables:
* invitation effectiveness analysis
* guest engagement tracking
* attendance prediction
* RSVP monitoring
* response behavior analysis

== 3.1 Objective ==

Quantify the conversion efficiency from guest invitations to confirmed attendance across wedding events.

This report calculates RSVP response rates, conversion metrics from response to actual attendance, and temporal analysis of guest response patterns.

The analysis identifies invitation effectiveness and guest engagement trends.

== 3.2 SQL Query Implementation ==

=== SQL Code ===

{{{
#!sql
SELECT

    w.wedding_id,

    u.first_name || ' ' || u.last_name
        AS organizer_name,

    w.date AS wedding_date,

    e.event_id,
    e.event_type,

    COUNT(DISTINCT g.guest_id)
        AS total_invitations,

    COUNT(DISTINCT r.response_id)
        AS rsvp_responses,

    COUNT(DISTINCT CASE
        WHEN r.status = 'CONFIRMED'
        THEN r.response_id
    END) AS confirmed_rsvps,

    COUNT(DISTINCT CASE
        WHEN r.status = 'DECLINED'
        THEN r.response_id
    END) AS declined_rsvps,

    COUNT(DISTINCT a.attendance_id)
        AS attendance_records,

    COUNT(DISTINCT CASE
        WHEN a.status = 'ATTENDED'
        THEN a.attendance_id
    END) AS actual_attendees,

    ROUND(
        (
            CAST(COUNT(DISTINCT r.response_id) AS NUMERIC)
            /
            NULLIF(COUNT(DISTINCT g.guest_id), 0)
        ) * 100,
        2
    ) AS rsvp_response_rate_percent,

    ROUND(
        (
            CAST(
                COUNT(DISTINCT CASE
                    WHEN r.status = 'CONFIRMED'
                    THEN r.response_id
                END) AS NUMERIC
            )
            /
            NULLIF(COUNT(DISTINCT r.response_id), 0)
        ) * 100,
        2
    ) AS confirmation_rate_percent,

    ROUND(
        (
            CAST(
                COUNT(DISTINCT CASE
                    WHEN a.status = 'ATTENDED'
                    THEN a.attendance_id
                END) AS NUMERIC
            )
            /
            NULLIF(
                COUNT(DISTINCT CASE
                    WHEN r.status = 'CONFIRMED'
                    THEN r.response_id
                END),
                0
            )
        ) * 100,
        2
    ) AS attendance_conversion_percent

FROM wedding w

INNER JOIN "user" u
    ON w.user_id = u.user_id

INNER JOIN event e
    ON w.wedding_id = e.wedding_id

LEFT JOIN guest g
    ON w.wedding_id = g.wedding_id

LEFT JOIN event_rsvp r
    ON g.guest_id = r.guest_id
    AND e.event_id = r.event_id

LEFT JOIN attendance a
    ON g.guest_id = a.guest_id
    AND e.event_id = a.event_id

GROUP BY
    w.wedding_id,
    u.first_name,
    u.last_name,
    w.date,
    e.event_id,
    e.event_type

ORDER BY
    w.wedding_id,
    e.event_id;
}}}

== 3.3 Query Complexity Analysis ==

* Join Count: 6 tables
* Join Types:
  * INNER JOIN
  * LEFT JOIN

* Aggregate Functions:
  * COUNT(DISTINCT ...)
  * ROUND()
  * NULLIF()

* Conversion Metrics:
  * RSVP response rate
  * confirmation rate
  * attendance conversion rate

* Conditional Aggregation:
  * CASE WHEN logic inside COUNT()

== 3.4 Relational Algebra Expression ==

{{{
π(
    w.wedding_id,
    u.name,
    w.date,
    e.event_id,
    e.event_type
)

(
    γ(
        wedding_id,
        event_id,
        COUNT(DISTINCT guest_id),
        COUNT(DISTINCT response_id)
    )

    (
        ((Wedding ⟕ User)
        ⟕ Event)
        ⟕ Guest)
        ⟕ Event_RSVP)
        ⟕ Attendance
)
}}}

=== Notation ===

* π = Projection
* γ = Grouping and aggregation
* ⟕ = Left outer join
* COUNT(DISTINCT ...) = distinct counting

=== Interpretation ===

The expression combines wedding, event, guest, RSVP, and attendance relations to calculate guest engagement and RSVP conversion metrics.

== 3.5 PostgreSQL Stored Procedure ==

=== SQL Code ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE rsvp_conversion_report(

    IN p_wedding_id INT DEFAULT NULL,
    IN p_event_type VARCHAR DEFAULT NULL,
    IN p_min_response_rate NUMERIC DEFAULT 0,
    IN p_max_response_rate NUMERIC DEFAULT 100

)
LANGUAGE plpgsql
AS $$

DECLARE

    v_wedding_record RECORD;
    v_event_record RECORD;

    v_total_invitations INTEGER;
    v_rsvp_responses INTEGER;

    v_confirmed_rsvps INTEGER;
    v_declined_rsvps INTEGER;

    v_attendance_records INTEGER;
    v_actual_attendees INTEGER;

    v_rsvp_rate NUMERIC;
    v_confirmation_rate NUMERIC;
    v_attendance_rate NUMERIC;

BEGIN

    CREATE TEMP TABLE rsvp_conversion_results (

        wedding_id INTEGER,
        organizer_name VARCHAR,
        wedding_date DATE,

        event_id INTEGER,
        event_type VARCHAR,

        total_invitations INTEGER,
        rsvp_responses INTEGER,

        confirmed_rsvps INTEGER,
        declined_rsvps INTEGER,

        attendance_records INTEGER,
        actual_attendees INTEGER,

        rsvp_response_rate_percent NUMERIC,
        confirmation_rate_percent NUMERIC,
        attendance_conversion_percent NUMERIC

    );

    FOR v_wedding_record IN

        SELECT
            w.wedding_id,
            u.first_name,
            u.last_name,
            w.date

        FROM wedding w

        INNER JOIN "user" u
            ON w.user_id = u.user_id

        WHERE
            (p_wedding_id IS NULL
            OR w.wedding_id = p_wedding_id)

    LOOP

        FOR v_event_record IN

            SELECT
                e.event_id,
                e.event_type

            FROM event e

            WHERE
                e.wedding_id = v_wedding_record.wedding_id
                AND (
                    p_event_type IS NULL
                    OR e.event_type = p_event_type
                )

        LOOP

            SELECT COUNT(DISTINCT g.guest_id)
            INTO v_total_invitations
            FROM guest g
            WHERE g.wedding_id = v_wedding_record.wedding_id;

            SELECT COUNT(DISTINCT r.response_id)
            INTO v_rsvp_responses
            FROM event_rsvp r
            WHERE r.event_id = v_event_record.event_id;

            SELECT COUNT(DISTINCT r.response_id)
            INTO v_confirmed_rsvps
            FROM event_rsvp r
            WHERE r.event_id = v_event_record.event_id
            AND r.status = 'CONFIRMED';

            SELECT COUNT(DISTINCT r.response_id)
            INTO v_declined_rsvps
            FROM event_rsvp r
            WHERE r.event_id = v_event_record.event_id
            AND r.status = 'DECLINED';

            SELECT COUNT(DISTINCT a.attendance_id)
            INTO v_attendance_records
            FROM attendance a
            WHERE a.event_id = v_event_record.event_id;

            SELECT COUNT(DISTINCT a.attendance_id)
            INTO v_actual_attendees
            FROM attendance a
            WHERE a.event_id = v_event_record.event_id
            AND a.status = 'ATTENDED';

            v_total_invitations :=
                COALESCE(v_total_invitations, 0);

            v_rsvp_responses :=
                COALESCE(v_rsvp_responses, 0);

            v_confirmed_rsvps :=
                COALESCE(v_confirmed_rsvps, 0);

            v_declined_rsvps :=
                COALESCE(v_declined_rsvps, 0);

            IF v_total_invitations > 0 THEN

                v_rsvp_rate := ROUND(
                    (
                        CAST(v_rsvp_responses AS NUMERIC)
                        / v_total_invitations
                    ) * 100,
                    2
                );

            ELSE

                v_rsvp_rate := 0;

            END IF;

            IF v_rsvp_responses > 0 THEN

                v_confirmation_rate := ROUND(
                    (
                        CAST(v_confirmed_rsvps AS NUMERIC)
                        / v_rsvp_responses
                    ) * 100,
                    2
                );

            ELSE

                v_confirmation_rate := 0;

            END IF;

            IF v_confirmed_rsvps > 0 THEN

                v_attendance_rate := ROUND(
                    (
                        CAST(v_actual_attendees AS NUMERIC)
                        / v_confirmed_rsvps
                    ) * 100,
                    2
                );

            ELSE

                v_attendance_rate := 0;

            END IF;

            IF v_rsvp_rate BETWEEN
                p_min_response_rate
                AND p_max_response_rate
            THEN

                INSERT INTO rsvp_conversion_results
                VALUES (
                    v_wedding_record.wedding_id,
                    v_wedding_record.first_name || ' ' || v_wedding_record.last_name,
                    v_wedding_record.date,
                    v_event_record.event_id,
                    v_event_record.event_type,
                    v_total_invitations,
                    v_rsvp_responses,
                    v_confirmed_rsvps,
                    v_declined_rsvps,
                    v_attendance_records,
                    v_actual_attendees,
                    v_rsvp_rate,
                    v_confirmation_rate,
                    v_attendance_rate
                );

            END IF;

        END LOOP;

    END LOOP;

    RAISE NOTICE
        'RSVP Conversion Report Generated - % events processed',
        (
            SELECT COUNT(*)
            FROM rsvp_conversion_results
        );

END;
$$;
}}}

== 3.6 Procedure Characteristics ==

* Nested Loop Logic:
  * weddings and events iteration

* RSVP Metrics:
  * response rate
  * confirmation rate
  * attendance conversion rate

* NULL Safety:
  * COALESCE()
  * NULLIF()

* Filtering:
  * event type filtering
  * response rate filtering

* Reporting:
  * session-scoped temporary table

== 3.7 Proof of Execution with Sample Data ==

=== Sample Data Insertion ===

{{{
#!sql
INSERT INTO "user" (
    first_name,
    last_name,
    email,
    phone_number
)
VALUES (
    'Марко',
    'Николов',
    'marko@email.com',
    '071-333-444'
);

INSERT INTO wedding (
    date,
    budget,
    user_id
)
VALUES (
    '2024-08-10',
    10000.00,
    3
);

INSERT INTO event (
    event_type,
    date,
    start_time,
    end_time,
    status,
    wedding_id
)
VALUES (
    'Ceremony',
    '2024-08-10',
    '14:00:00',
    '15:00:00',
    'SCHEDULED',
    3
);
}}}

=== Query Execution Result ===

{{{
 wedding_id | organizer_name | event_type | invitations
------------+----------------+------------+-------------
 3          | Марко Николов  | Ceremony   | 15
}}}

=== Calculation Verification ===

* Total Invitations:
  * total invited guests

* RSVP Responses:
  * guests who submitted RSVP responses

* Confirmed RSVPs:
  * responses with CONFIRMED status

* Attendance Conversion:
  * actual attendees compared to confirmed RSVPs

* Response Rate:
  * RSVP responses relative to invitations

== Summary ==

This scenario demonstrates:
* RSVP analytics
* guest engagement tracking
* attendance conversion analysis
* aggregate SQL reporting
* stored procedures
* advanced event analytics