Changes between Version 1 and Version 2 of Напредни извештаи од базата (SQL и складирани процедури)

Timestamp:

02/06/25 14:07:32 (9 months ago)

Author:

173067

Comment:

--

Напредни извештаи од базата (SQL и складирани процедури)

@@ -218 +218 @@
 
 }}}
+
+* Извештај за топ 3 најпопуларни дестинации, аеродроми и летови за секој месец во годината
+
+{{{
+WITH MonthlyBookings AS (
+    -- Count bookings for each destination per month
+    SELECT 
+        DATE_TRUNC('month', b.bookingDate) AS month,
+        d.Name AS destination_name,
+        COUNT(b.BookingID) AS bookings_count,
+        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.bookingDate) ORDER BY COUNT(b.BookingID) DESC) AS rank
+    FROM Booking b
+    JOIN Flight f ON b.FlightID = f.FlightID
+    JOIN Airport a ON f.ArrivalAirport = a.AirportID
+    JOIN Destination d ON a.City = d.DestinationID
+    GROUP BY month, destination_name
+), 
+
+MonthlyFlights AS (
+    -- Count bookings for each flight per month
+    SELECT 
+        DATE_TRUNC('month', b.bookingDate) AS month,
+        f.FlightNumber,
+        COUNT(b.BookingID) AS bookings_count,
+        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.bookingDate) ORDER BY COUNT(b.BookingID) DESC) AS rank
+    FROM Booking b
+    JOIN Flight f ON b.FlightID = f.FlightID
+    GROUP BY month, f.FlightNumber
+), 
+
+MonthlyAirports AS (
+    -- Count departures from each airport per month
+    SELECT 
+        DATE_TRUNC('month', b.bookingDate) AS month,
+        a.Name AS airport_name,
+        COUNT(b.BookingID) AS departures_count,
+        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.bookingDate) ORDER BY COUNT(b.BookingID) DESC) AS rank
+    FROM Booking b
+    JOIN Flight f ON b.FlightID = f.FlightID
+    JOIN Airport a ON f.DepartureAirport = a.AirportID
+    GROUP BY month, a.Name
+)
+
+-- Retrieve the top 3 destinations, flights, and airports for each month
+SELECT month, 'Destination' AS category, destination_name AS name, bookings_count AS count FROM MonthlyBookings WHERE rank <= 3
+UNION ALL
+SELECT month, 'Flight' AS category, FlightNumber AS name, bookings_count AS count FROM MonthlyFlights WHERE rank <= 3
+UNION ALL
+SELECT month, 'Airport' AS category, airport_name AS name, departures_count AS count FROM MonthlyAirports WHERE rank <= 3
+ORDER BY month, category, count DESC;
+}}}