Changes between Version 6 and Version 7 of AnalyticalStatisticalQuerying

Timestamp:

09/27/25 13:25:45 (2 weeks ago)

Author:

221507

Comment:

--

AnalyticalStatisticalQuerying

@@ -43 +43 @@
 
 {{{ 
-WITH company_performance AS (
-    SELECT 
-        to_org.company_name,
-        COUNT(DISTINCT r.route_id) as routes_operated,
-        COUNT(DISTINCT t.trip_id) as trips_organized,
-        COUNT(tk.ticket_id) as total_tickets_sold,
-        SUM(COALESCE(tk.price, 0)) as total_revenue,
-        AVG(COALESCE(tk.price, 0)) as avg_ticket_price,
-        COUNT(DISTINCT tk.account_id) as unique_customers,
-        AVG(COALESCE(rev.rating, 0)) as avg_rating
-    FROM transport_organizer to_org
-    JOIN route r ON to_org.transport_organizer_id = r.transport_organizer_id
-    JOIN trip t ON r.route_id = t.route_id
-    LEFT JOIN ticket tk ON t.trip_id = tk.trip_id
-    LEFT JOIN review rev ON tk.ticket_id = rev.ticket_id
-    GROUP BY to_org.transport_organizer_id, to_org.company_name
-)
-SELECT 
-    company_name,
-    routes_operated,
-    trips_organized,
-    total_tickets_sold,
-    total_revenue,
-    avg_ticket_price,
-    unique_customers,
-    ROUND(avg_rating, 2) as avg_rating
-FROM company_performance
+
+CREATE VIEW company_performance_view AS
+SELECT
+    to_org.company_name,
+    COUNT(DISTINCT r.route_id) AS routes_operated,
+    COUNT(DISTINCT t.trip_id) AS trips_organized,
+    COUNT(tk.ticket_id) AS total_tickets_sold,
+    SUM(COALESCE(p.total_price, 0))::double precision AS total_revenue,
+    AVG(COALESCE(tk.price, 0))::double precision AS avg_ticket_price,
+    COUNT(DISTINCT tk.account_id) AS unique_customers,
+    ROUND(AVG(rev.rating)::numeric, 2)::double precision AS avg_rating
+FROM transport_organizer to_org
+         JOIN route r ON to_org.transport_organizer_id = r.transport_organizer_id
+         JOIN trip t ON r.route_id = t.route_id
+         LEFT JOIN ticket tk ON t.trip_id = tk.trip_id
+         LEFT JOIN review rev ON tk.ticket_id = rev.ticket_id
+         JOIN payment p ON tk.payment_id = p.payment_id
+GROUP BY to_org.transport_organizer_id, to_org.company_name
 ORDER BY total_revenue DESC;
 
@@ -183 +174 @@
 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.
 {{{
+
+
+CREATE VIEW top_selling_routes_view AS
 WITH route_stats AS (
-    SELECT 
+    SELECT
         r.route_id,
-        r.from_location_id,
-        r.to_location_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) AS total_revenue,
-        AVG(p.total_price) AS avg_ticket_price
+        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 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
-)
+             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
-WHERE rs.total_tickets_sold >= 0.8 * 
-        (SELECT 
-             MAX(total_tickets_sold) FROM route_stats) -- this is used to retrieve top selling tickets, the percentage is flexible
+FROM route_stats rs, max_tickets mt
 ORDER BY rs.total_tickets_sold DESC;
 
@@ -249 +250 @@
 
 
-
+Here's a short example:
+
+[[Image(trip_before.png)]]
+
+If we purchase 3 tickets:
+
+
+[[Image(purchase.png)]]
+
+
+We get the new seat availability:
+
+[[Image(trip_after.png)]]