Changes between Version 20 and Version 21 of DatabaseCreation

Timestamp:

04/26/26 13:48:02 (6 days ago)

Author:

231027

Comment:

--

DatabaseCreation

@@ -375 +375 @@
 }}}
 
-=== `Event_Ratings`
-
-{{{
-
-CREATE VIEW "Event_Ratings" AS
+=== `Event_User_Ratings`
+
+{{{
+
+CREATE VIEW "Event_User_Ratings" AS
 SELECT eh.event_happening_id,
        e.event_id,
@@ -394 +394 @@
 
 }}}
+
+=== `Event_Overall_Ratings`
+
+{{{
+
+CREATE VIEW "Event_Overall_Ratings" AS
+SELECT 
+    e.event_id,
+    e.name AS event_name,
+    eh.event_happening_id,
+    eh.event_time,
+    COUNT(ehr.rating_id) AS total_reviews,
+    ROUND(AVG(ehr.rating), 2) AS average_rating
+FROM "Event" e
+JOIN "Event_Happening" eh ON e.event_id = eh.event_id
+JOIN "Event_Happening_Rating" ehr ON eh.event_happening_id = ehr.event_happening_id
+GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;
+
+}}}
+
+== `Event_Financial_Summary`
+
+{{{
+
+CREATE VIEW "Event_Financial_Summary" AS
+SELECT 
+    e.event_id,
+    e.name AS event_name,
+    eh.event_happening_id,
+    eh.event_time,
+    COUNT(tp.purchase_id) AS total_tickets_sold,
+    SUM(tp.purchase_amount) AS total_revenue,
+    ROUND(AVG(tp.purchase_amount), 2) AS avg_ticket_price
+FROM "Event" e
+JOIN "Event_Happening" eh ON e.event_id = eh.event_id
+JOIN "Ticket" t ON eh.event_happening_id = t.event_happening_id
+JOIN "Ticket_Purchase" tp ON t.ticket_id = tp.ticket_id
+GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;
+
+}}}