Changes between Version 12 and Version 13 of AnalyticalStatisticalQuerying

Timestamp:

09/30/25 20:07:01 (3 months ago)

Author:

222077

Comment:

--

AnalyticalStatisticalQuerying

@@ -207 +207 @@
 
 }}}
+
+= Relational Algebra =
+
+
+== 6. Top selling routes for transport organizers ==
+==== Original Query
+{{{
+CREATE VIEW top_selling_routes_view AS
+WITH route_stats AS (
+    SELECT
+        r.route_id,
+        from_loc.name AS from_location_name,
+        to_loc.name   AS to_location_name,
+        to_org.company_name AS transport_organizer_name,
+        COUNT(tk.ticket_id) AS total_tickets_sold,
+        SUM(p.total_price)::numeric(38,2) AS total_revenue,
+        AVG(p.total_price)::numeric(38,2) AS avg_ticket_price
+    FROM route r
+             JOIN transport_organizer to_org
+                  ON r.transport_organizer_id = to_org.transport_organizer_id
+             JOIN location from_loc
+                  ON r.from_location_id = from_loc.location_id
+             JOIN location to_loc
+                  ON r.to_location_id = to_loc.location_id
+             JOIN trip tr ON r.route_id = tr.route_id
+             JOIN ticket tk ON tr.trip_id = tk.trip_id
+             JOIN payment p ON tk.payment_id = p.payment_id
+    GROUP BY r.route_id, from_loc.name, to_loc.name, to_org.company_name
+),
+     max_tickets AS (
+         SELECT MAX(total_tickets_sold) AS max_sold
+         FROM route_stats
+     )
+SELECT rs.*
+FROM route_stats rs, max_tickets mt
+ORDER BY rs.total_tickets_sold DESC;
+}}}
+
+== Results (Relational Algebra) ==
+{{{
+J1 = route ⨝ transport_organizer
+J2 = J1 ⨝ from_loc
+J3 = J2 ⨝ to_loc
+J4 = J3 ⨝ trip
+J5 = J4 ⨝ ticket
+J6 = J5 ⨝ payment
+
+G1 = γ(route_id, from_loc.name, to_loc.name, to_org.company_name;
+        COUNT(ticket_id) → total_tickets_sold,
+        SUM(total_price) → total_revenue,
+        AVG(total_price) → avg_ticket_price)(J6)
+
+M1 = γ(; MAX(total_tickets_sold) → max_sold)(G1)
+
+Result = τ_desc(total_tickets_sold)(G1 × M1)
+}}}
+
+== Analysis (Optimizations) ==
+ * Push projections early to reduce carried attributes before aggregation.
+ * Join order: start with fact tables (`ticket`, `payment`) then join dimension tables (`trip`, `route`, `location`, `transport_organizer`).
+ * The `max_tickets` CTE is redundant unless we need to filter only top seller(s).
+ * Indexing opportunities:
+   - `ticket.trip_id`, `trip.route_id`, `ticket.payment_id`
+   - Foreign keys: `route.from_location_id`, `route.to_location_id`, `route.transport_organizer_id`
+
+== Conclusion (Rewritten SQL) ==
+{{{
+CREATE VIEW top_selling_routes_view AS
+SELECT
+    r.route_id,
+    from_loc.name AS from_location_name,
+    to_loc.name   AS to_location_name,
+    to_org.company_name AS transport_organizer_name,
+    COUNT(tk.ticket_id) AS total_tickets_sold,
+    SUM(p.total_price)::numeric(38,2) AS total_revenue,
+    AVG(p.total_price)::numeric(38,2) AS avg_ticket_price
+FROM ticket tk
+JOIN payment p ON tk.payment_id = p.payment_id
+JOIN trip tr   ON tk.trip_id = tr.trip_id
+JOIN route r   ON tr.route_id = r.route_id
+JOIN transport_organizer to_org ON r.transport_organizer_id = to_org.transport_organizer_id
+JOIN location from_loc ON r.from_location_id = from_loc.location_id
+JOIN location to_loc   ON r.to_location_id = to_loc.location_id
+GROUP BY r.route_id, from_loc.name, to_loc.name, to_org.company_name
+ORDER BY COUNT(tk.ticket_id) DESC;
+}}}
+