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

Извештај за број на откжани, успешни резервации по ресторан

SELECT 
    au.first_name, 
    au.last_name, 
    au.membership_level, 
    au."role", 
    rh.restaurant_id,
    
    COUNT(CASE WHEN rh.cancellation_reason = 'Canceled by user' THEN 1 END) AS canceled_by_user_per_restaurant,

    COUNT(CASE WHEN rh.cancellation_reason = '/' THEN 1 END) AS succesfull_by_user_per_restaurant,

    COUNT(rh.cancellation_reason) AS total_cancellations_per_restaurant

FROM reservation_history rh  
INNER JOIN app_user au ON rh.customer_id = au.id 

WHERE rh.cancellation_reason IS NOT NULL  

GROUP BY au.first_name, 
         au.last_name, 
         au.membership_level, 
         au."role", 
         rh.restaurant_id 

ORDER BY canceled_by_user_per_restaurant DESC;

Извештај за најмногу резервации по ресторан

SELECT 
	r.restaurantId, 
	r."name", 
	COUNT(res.reservationId) AS totalReservations
FROM restaurants r
JOIN reservations res ON r.restaurantId = res.restaurantId
GROUP BY r.restaurantId, r."name" 
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;