Changes between Version 2 and Version 3 of AnalyticalStatisticalQuerying

Timestamp:

08/21/25 10:16:54 (4 months ago)

Author:

221507

Comment:

--

AnalyticalStatisticalQuerying

@@ -139 +139 @@
 }}}
 
+
+
+== 6. Top selling routes for transport organizers ==
+This query retrieves the most popular routes for a specific transport organizer, ranking them by the number of tickets sold while also showing total revenue and average ticket price.
+{{{
+SELECT 
+    r.route_id,
+    r.from_location_id,
+    r.to_location_id,
+    COUNT(tk.ticket_id) AS total_tickets_sold,
+    SUM(p.total_price) AS total_revenue,
+    AVG(p.total_price) AS avg_ticket_price
+FROM route r
+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
+WHERE r.transport_organizer_id = 100 -- just for reference
+GROUP BY r.route_id, r.from_location_id, r.to_location_id
+ORDER BY total_tickets_sold DESC
+LIMIT 5;
+}}}
+
+