Changes between Version 1 and Version 2 of AnalyticalStatisticalQuerying

Timestamp:

08/21/25 00:10:39 (4 months ago)

Author:

221507

Comment:

--

AnalyticalStatisticalQuerying

@@ -116 +116 @@
 ORDER BY total_tickets DESC;
 }}}
+
+== 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.
+{{{
+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;
+}}}
+