Changes between Version 24 and Version 25 of DatabaseCreation

Timestamp:

04/26/26 13:57:27 (6 days ago)

Author:

231027

Comment:

--

DatabaseCreation

@@ -437 +437 @@
     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
+    
+    -- total revenue
+    SUM(tp.purchase_amount - tr.refund_amount) AS net_revenue,
+    
+    -- refund taxes
+    SUM(tp.purchase_amount - tr.refund_amount) AS refund_fee_profit
+
 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
+LEFT JOIN "Ticket_Refund" tr ON tp.purchase_id = tr.purchase_id
 GROUP BY e.event_id, e.name, eh.event_happening_id, eh.event_time;