Changes between Version 3 and Version 4 of AnalyticalStatisticalQuerying

Timestamp:

08/27/25 16:02:00 (2 months ago)

Author:

221507

Comment:

--

AnalyticalStatisticalQuerying

@@ -118 +118 @@
 
 == 5. Most popular destinations in the past year ==
-This SQL query retrieves the top 5 most popular destinations from the last year, showing passenger volume, revenue, and breakdown of student and child ticket sales for each location.
+
+This SQL query aggregates ticket data by destination over the past year, calculating total passengers, unique customers, revenue, and specific ticket types, then returns destinations with passenger counts at or above the average, ordered by total passengers in descending order.
+
 {{{
+
+WITH destinations AS (
+    SELECT 
+        l.name AS destination,
+        COUNT(t.ticket_id) AS total_passengers,
+        COUNT(DISTINCT t.account_id) AS unique_customers,
+        SUM(t.price) AS total_revenue,
+        COUNT(st.student_ticket_id) AS student_tickets,
+        COUNT(ct.child_ticket_id) AS child_tickets,
+        l.latitude,
+        l.longitude
+    FROM location l
+    JOIN ticket t ON l.location_id = t.gets_off_location_id
+    JOIN trip tr ON t.trip_id = tr.trip_id
+    JOIN transport_organizer to_org ON tr.transport_organizer_id = to_org.transport_organizer_id
+    LEFT JOIN student_ticket st ON t.ticket_id = st.ticket_id
+    LEFT JOIN child_ticket ct ON t.ticket_id = ct.ticket_id
+    GROUP BY l.location_id, l.name, l.latitude, l.longitude
+),
+stats AS (
+    SELECT AVG(total_passengers) AS mean_passengers -- this is used to avoid hard limits 
+    FROM destinations
+)
 SELECT 
-    l.name AS destination,
-    COUNT(t.ticket_id) AS total_passengers,
-    COUNT(DISTINCT t.account_id) AS unique_customers,
-    SUM(t.price) AS total_revenue,
-    COUNT(st.student_ticket_id) AS student_tickets,
-    COUNT(ct.child_ticket_id) AS child_tickets
-FROM location l
-JOIN ticket t ON l.location_id = t.gets_off_location_id
-JOIN trip tr ON t.trip_id = tr.trip_id
-JOIN transport_organizer TO_id ON tr.transport_organizer_id = TO_id.transport_organizer_id
-LEFT JOIN student_ticket st ON t.ticket_id = st.ticket_id
-LEFT JOIN child_ticket ct ON t.ticket_id = ct.ticket_id
-WHERE t.date_purchased >= CURRENT_DATE - INTERVAL '1 year'
-GROUP BY l.location_id, l.name, l.latitude, l.longitude
-ORDER BY total_passengers DESC
-LIMIT 5;
+    d.destination,
+    d.total_passengers,
+    d.unique_customers,
+    d.total_revenue,
+    d.student_tickets,
+    d.child_tickets
+FROM destinations d, stats
+WHERE d.total_passengers >= stats.mean_passengers
+ORDER BY d.total_passengers DESC;
+
 }}}
 
@@ -144 +163 @@
 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;
+WITH route_stats AS (
+    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
+)
+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
+ORDER BY rs.total_tickets_sold DESC;
+
 }}}