= Synthesis: Multi-Dimensional Analysis Capabilities =

== 4.1 Integrated View Query ==

The three previous scenarios can be synthesized into a single comprehensive query that simultaneously analyzes financial, operational, and engagement dimensions.

=== SQL Code ===

{{{
#!sql
SELECT
    w.wedding_id,

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

    w.date,

    w.budget,

    -- Financial Dimension
    (
        SELECT COALESCE(SUM(vb.price), 0)
        FROM venue_booking vb
        WHERE vb.wedding_id = w.wedding_id
    ) AS venue_cost,

    (
        SELECT COALESCE(SUM(
            EXTRACT(EPOCH FROM (pb.end_time - pb.start_time))/3600
            * p.price_per_hour
        ), 0)
        FROM photographer_booking pb
        LEFT JOIN photographer p
            ON pb.photographer_id = p.photographer_id
        WHERE pb.wedding_id = w.wedding_id
    ) AS photographer_cost,

    (
        SELECT COALESCE(SUM(
            EXTRACT(EPOCH FROM (bb.end_time - bb.start_time))/3600
            * b.price_per_hour
        ), 0)
        FROM band_booking bb
        LEFT JOIN band b
            ON bb.band_id = b.band_id
        WHERE bb.wedding_id = w.wedding_id
    ) AS band_cost,

    -- Operational Dimension
    (
        SELECT COUNT(DISTINCT a.guest_id)
        FROM event e
        LEFT JOIN attendance a
            ON e.event_id = a.event_id
        WHERE e.wedding_id = w.wedding_id
          AND a.status IN ('ATTENDED', 'CONFIRMED')
    ) AS confirmed_attendees,

    (
        SELECT MAX(v.capacity)
        FROM venue_booking vb
        INNER JOIN venue v
            ON vb.venue_id = v.venue_id
        WHERE vb.wedding_id = w.wedding_id
    ) AS max_venue_capacity,

    -- Engagement Dimension
    (
        SELECT ROUND(
            AVG(
                CAST(response_count AS NUMERIC)
                / guest_count
            ) * 100,
            2
        )
        FROM (
            SELECT
                COUNT(DISTINCT r.response_id) AS response_count,
                COUNT(DISTINCT g.guest_id) AS guest_count
            FROM guest g
            LEFT JOIN event_rsvp r
                ON g.guest_id = r.guest_id
            WHERE g.wedding_id = w.wedding_id
            GROUP BY w.wedding_id
        ) t
    ) AS avg_rsvp_rate

FROM wedding w

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

ORDER BY w.wedding_id;
}}}

=== Interpretation ===

This integrated query demonstrates the capability to materialize multiple analytical dimensions simultaneously.

It combines:
* financial analysis
* venue and attendance utilization
* RSVP engagement metrics
* wedding-level operational overview

This enables holistic wedding performance assessment.

= Performance Considerations and Optimization Strategies =

== 5.1 Query Execution Plans ==

The complex queries presented in this phase employ several optimization strategies.

=== Index Utilization ===

* Foreign key indexes enable faster join operations on:
  * wedding_id
  * venue_id
  * photographer_id
  * band_id
  * event_id
  * guest_id

* Composite indexes on (wedding_id, status) can accelerate filtered aggregations.

=== Aggregate Optimization ===

* DISTINCT clauses reduce result set cardinality before aggregation.
* CASE WHEN statements inside COUNT() minimize unnecessary table scans.
* Subqueries with local WHERE clauses reduce data volume before joins.

=== Temporal Calculation Efficiency ===

* EXTRACT(EPOCH FROM ...) is used to convert time intervals into hours.
* Time arithmetic is constrained to booking tables where start_time and end_time are stored.
* Vendor cost calculations are separated by service type for clearer execution logic.

== 5.2 Scalability Assessment ==

=== Current Schema Limitations ===

* JOIN chains of six or more tables may become slower with large datasets.
* Repeated aggregation over booking and attendance tables may increase execution time.
* Financial and attendance reports may require recalculation if used frequently.

=== Recommended Enhancements ===

* Create materialized views for:
  * budget variance reports
  * capacity utilization reports
  * RSVP conversion reports

* Add indexes for analytical reporting on:
  * wedding_id
  * event_id
  * status
  * booking date columns

* Archive historical wedding data older than two years into a separate analytical database.

* Consider storing frequently used calculated values when reporting performance becomes critical.