= Event RSVP Conversion Rate Analysis =

== 3. Scenario Overview ==

This scenario analyzes RSVP response efficiency and attendance conversion for wedding events.

The analysis measures:
* invitation response rates
* RSVP confirmation rates
* attendance conversion percentages
* guest engagement metrics
* response timing statistics

The generated analytical reports provide insights into invitation effectiveness and guest participation behavior.

== 3.1 Objective ==

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

The analysis calculates:
* RSVP response rates
* confirmation percentages
* attendance conversion metrics
* average response timing before events

The scenario demonstrates how analytical SQL queries can be used to evaluate guest engagement patterns.

== 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()

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

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

== 3.4 Relational Algebra Expression ==

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

    COUNT(g.guest_id),

    COUNT(r.response_id),

    COUNT(CONFIRMED),

    COUNT(DECLINED),

    COUNT(a.attendance_id),

    COUNT(ATTENDED)
)

(
    γ(
        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 aggregation
* CASE = Conditional aggregation

=== Interpretation ===

The expression combines weddings, guests, RSVP responses, and attendance records in order to calculate RSVP conversion metrics.

Grouping operations aggregate invitation, RSVP, and attendance statistics for each event.

== 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);

            v_attendance_records :=
                COALESCE(v_attendance_records, 0);

            v_actual_attendees :=
                COALESCE(v_actual_attendees, 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:
  * iteration through weddings and events

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

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

* Filtering:
  * response rate interval filtering

* Dynamic Parameters:
  * optional wedding filtering
  * optional event type filtering

== 3.7 Proof of Execution with Sample Data ==

=== Sample Data Insertion ===

{{{
#!sql
INSERT INTO "user" (
    first_name,
    last_name,
    email,
    phone_number
)
VALUES (
    'Александар',
    'Николовски',
    'aleksandar.n@email.com',
    '071-222-333'
);

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

=== Query Execution Result ===

{{{
 wedding_id | organizer_name        | total_invitations
------------+-----------------------+-------------------
 3          | Aleksandar Nikolovski | 15
}}}

=== Calculation Verification ===

* Total Invitations:
  * total invited guests

* RSVP Responses:
  * total RSVP replies

* Confirmed RSVPs:
  * guests who accepted invitations

* Declined RSVPs:
  * guests who rejected invitations

* Attendance Conversion:
  * ratio between confirmed RSVPs and actual attendance

== Summary ==

This scenario demonstrates advanced RSVP analytics using:
* analytical SQL queries
* conditional aggregation
* conversion metrics
* PostgreSQL stored procedures
* attendance analysis
* guest engagement reporting