Changes between Initial Version and Version 1 of RSVPConversion

Timestamp:

05/13/26 21:26:13 (13 days ago)

Author:

193284

Comment:

--

RSVPConversion

@@ +1 @@
+= 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 Nikoloski | 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