Changes between Version 6 and Version 7 of izvestai

Timestamp:

01/29/25 20:47:40 (27 hours ago)

Author:

213209

Comment:

--

izvestai

@@ -14 +14 @@
 
     COUNT(CASE WHEN rh.cancellation_reason = '/' THEN 1 END) AS succesfull_by_user_per_restaurant,
-    
-    COUNT(CASE WHEN rh.cancellation_reason = 'Canceled by restaurant' THEN 1 END) AS canceled_by_restaurant,
 
     COUNT(rh.cancellation_reason) AS total_cancellations_per_restaurant
@@ -37 +35 @@
 
 {{{
-SELECT r.restaurantId, r."name", COUNT(res.reservationId) AS totalReservations
+SELECT 
+        r.restaurantId, 
+        r."name", 
+        COUNT(res.reservationId) AS totalReservations
 FROM restaurants r
 JOIN reservations res ON r.restaurantId = res.restaurantId
@@ -43 +44 @@
 ORDER BY totalReservations desc
 }}}
+
+== Извештај за просечна големина на група, максимална и број на резервации за во последниот месец
+{{{
+SELECT 
+    r."name" AS restaurant_name,
+    ROUND(AVG(rh.party_size), 1) AS average_party_size,
+    MAX(rh.party_size) AS max_party_size,
+    COUNT(*) AS number_of_reservations
+FROM reservation_history rh 
+JOIN "tables" t ON t.tableid = rh.table_id  
+JOIN restaurants r ON r.restaurantid = rh.restaurant_id 
+WHERE rh.reservation_datetime  >= (CURRENT_DATE - INTERVAL '1 month') 
+GROUP BY r."name"
+ORDER BY average_party_size DESC;
+}}}
+